Solved

Saving the SQL Login Credentials for Excel 2010 Microsoft Queries

Posted on 2016-07-31
1
308 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 66

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

617 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