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

sql sql-server-2008-r2 excel-2016
gbeaven picture gbeaven · Apr 12, 2018 · Viewed 9.7k times · Source

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:

enter image description here 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:

enter image description here

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

lmk picture lmk · May 13, 2019

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.

Related questions

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several …

Read More
CREATE TABLE IF NOT EXISTS equivalent in SQL Server

CREATE TABLE IF NOT EXISTS works on mysql but fails with SQL Server 2008 R2. What is the equivalent syntax?

Read More