Solved

Saving the SQL Login Credentials for Excel 2010 Microsoft Queries

Posted on 2016-07-31
1
183 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

740 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