Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 669
  • Last Modified:

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
0
John Ellis
Asked:
John Ellis
1 Solution
 
Jim HornMicrosoft 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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now