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.