Embed SQL Server Credentials in Excel 2016 (O365) to Refresh Data On-Demand

Question

I am trying to embed credentials into an Excel 2016 workbook for an account on my SQL Server 2008R2 database that has execute permissions on certain stored procedures to provide end users read-only data. User accounts don't have access to the database itself, the idea is to embed the credentials of this read-only account into an excel spreadsheet and the user will just click "Refresh All" to get the most recent data returned from the stored procedure.

To clarify, I don't care if the user knows the password to the account - that's not an issue. I just don't want them to ever have to type the password to refresh the data.

Here's what I've tried so far in Excel:

• Data (tab) > Get Data > From Database > From SQL Server Database > I enter the Server, database and my 'exec sp' command and this returns data no problem for me. However, if I hand the file over to another user that doesn't have access to the database the connection fails because Excel is trying to use Windows credentials. So I tried editing the Connection String (Data (tab) > Queries & Connections > right-click query > Properties > Definition (tab) > Connection String) but the fields are grayed out so I can't add the User ID and Password portion of the connection string.
• Data (tab) > Get Data > From Other Sources > From OLEDB > Build > Provider (tab) I choose 'SQL Server Native Client 10.0' > Connection (tab) I enter server name, 'Use a specific user name and password' radio checked and I enter the account username/password, select the database, 'Test Connection' returns valid > All (tab) > set 'Integrated Security' to "False" > OK > OK > Excel then prompts for credentials from Database/Windows/Default or Custom:

I wouldn't think I would enter anything here as I already entered credentials when building the query string but I entered anyways as it seems it's required to enter something. I hand the Excel file over to another user and sure enough access is denied because it's defaulting to their current Windows login to access the database.

• I added the From Data Connection Wizard (Legacy) to my Ribbon as an alternative method to build out the connection string using the same settings as the previous attempt and I'm prompted to type the password every time I want to refresh:

What gives? I have a colleague that has an Excel 2010 file where they can go in and edit the connection string properties without issue and their files work. How can I get this to work in Excel 2016?

I would really appreciate any help you guys can provide!

Edit: This question doesn't seem that far off..does anyone know how to do this? I feel like it should be pretty simple.

Answer 1

Better late than never? You need to use the legacy "Microsoft Query" (rather than Power Query) to create the data connection. MS have explicitly removed the ability to store username and password in the connection string (in ODC files - ignored) with Power Query data sources.

To Create:
Get Data > From Other sources > From Microsoft Query

You can access (and edit) the connection string (connection properties -> definition) - and store the password persistently (non-securely) this way.

Also Via VBA (for existing connection)

With ActiveWorkbook.Connections("yourconnection").ODBCConnection

    .Connection = _
    "ODBC;DRIVER=SQL Server;SERVER=sql2012.servername.com,99999;UID=username;PWD=password"
    .SavePassword = True

End With

Would love a method for O365.