Solved

Saving the SQL Login Credentials for Excel 2010 Microsoft Queries

Posted on 2016-07-31
1
78 Views
Last Modified: 2016-08-02
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
Comment
Question by:John Ellis
1 Comment
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41736866
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now