5

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.

Community
  • 1
  • 1
gbeaven
  • 886
  • 1
  • 9
  • 25
  • Are you really sure that you want to do that? – Hackerman Apr 12 '18 at 17:17
  • What would be the reason for not wanting to do it? The SPs just return data. I'm open to other suggestions of course, but this seems better than putting a SQL statement in the Excel sheet as it could be modified. The SP wouldn't be able to be modified as the user can't access the DB. – gbeaven Apr 12 '18 at 17:19
  • @Hackerman Forgot to tag you above – gbeaven Apr 12 '18 at 17:38
  • Can you export an ODC file externally and edit the connection string in there to include the correct authentication and password? The reason you don't want to have cleartext login/password is because then anyone can copy, distribute, and reuse it for their own purposes (even if it's just a SP) – Nick.McDermaid Apr 15 '18 at 04:37
  • @Nick.McDermaid I don't care if anyone knows the password. As stated in the post, it's not an issue. Most people already know the password anyways. That's the whole point of the read-only account. The account has execute access to SPs that only return data. They are not dynamic SQL statements or even parameter accepting SPs for that matter. I simply don't want the user to be prompted to enter a password every time, the password should be embedded in the connection string as if I was to use the 'User ID' and 'Password' properties. – gbeaven Apr 16 '18 at 01:41
  • Did you try using an ODC file? The way I do this is actually use VBA and ADO and explicitly connect through code. – Nick.McDermaid Apr 16 '18 at 02:22
  • This is what I’m talking about with ODC. https://support.office.com/en-us/article/create-edit-and-manage-connections-to-external-data-89d44137-f18d-49cf-953d-d22a2eea2d46 you should be able to put the connection string in an external ODC file, and edit that as required, including embedding the password. – Nick.McDermaid Apr 16 '18 at 02:28
  • @Nick.McDermaid I'll give the ODC file a try shortly and let you know. If that doesn't work, I'll go the VBA route like you mentioned. – gbeaven Apr 16 '18 at 14:07

1 Answers1

5

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.

barbsan
  • 3,238
  • 11
  • 18
  • 27
lmk
  • 66
  • 1
  • 3
  • Thanks for the answer. I actually ended up going with a different solution - I embed the SQL `exec` commands in the Excel spreadsheet but instead of embedding credentials as well I control who can execute stored procedures by database role on SQL Server using domain accounts. I did test your answer above and it worked - for anyone else looking at this in the future. – gbeaven May 13 '19 at 20:55
  • Using Microsoft Query is, in my opinion, rejecting change. It might be good for now, but Microsoft Query has suffered several drawbacks since Excel 2016 and 365. I'm working on a way to get the same results but using Power Query M's Odbc.Query. I'll post my findings here shortly. – Firefighter1017 Jan 23 '20 at 18:59
  • Nope, could not find anything that would record the credentials and use it for any users opening the file. And the funny thing is, whenever I'm using Microsoft Query, it uses the connection token for the AS/400 session but with Power Query M Odbc.Query, it does not. It's as if it was not using the same socket. – Firefighter1017 Jan 23 '20 at 19:40