Saving the SQL Login Credentials for Excel 2010 Microsoft Queries

Hello:

I'm using Microsoft Query in Excel 2010 to pull in data from SQL Server 2008.

For security purposes, I have a read-only SQL login that I need to use.  When I first began using Microsoft Query in a spreadsheet, I chose "New Data Source", entered my credentials, chose my database, and used the Query Wizard to pull in the data that I wanted.

I called the Data Source "Accounts".

Afterward, I went into the Connections window and chose the data source's properties and checked "Save my password".  Also, I clicked "OK" to the message warning me about encryption.  

Then, when I would open a new worksheet and create new queries, I would choose "Accounts" as the data source.

But, doing so did not save my SQL login credentials.  True, I was still "allowed" to conduct queries in Excel.  But, when I looked at the Connections properties, after creating new queries in a new worksheet, Excel was using my SQL Windows Authenticated user ID--not my read-only SQL Authenticated user ID.

I would have thought that, since I had saved my password and entered my credentials earlier for the "Accounts" data source, that Excel would have remembered to use my SQL login when I kept using "Accounts".  But, again, it didn't.

Does this mean that, every time I open a new worksheet to create new queries or even create new queries in the first worksheet after creating my "Accounts" data source, I have to create a new data source so that my SQL login will be used?

Is there not a way of saving my SQL Authenticated credentials, for Excel?  If not, then it's going to be very burdensome, every time I have to create a new data source and enter my credentials.

Thank you!

John
John EllisAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
For starters let me know if this works for you ... Microsoft Excel & SQL Server:  Self service BI to give users the data they want, mostly Step 3 'Excel connects to and executes the SQL Server Stored Procedure', as I'm having a memory that after creating the connection you have to go back in it and edit the connection string in order for it to completely save.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.