Solved

How to get Excel 2007 to not store userid and password for external data connection

Posted on 2009-05-07
4
481 Views
Last Modified: 2012-05-06
I am ultimately working on a report that will connect to a stored procedure and pass in parameters from cells in the workbook, but it seems that whether I have a report in Excel set up to do that or if I just set up a very basic report that selects all rows from a table, the user id and password gets stored with the *.xlsx file.  If I rename the xlsx file to a zip file and look in the xl folder and open connections.xml I see the userid and password listed there.  

In the testing I've done I'm choosing Data > From Other Sources > From Microsoft Query and connecting to a SQL Server 2005 database.  I can get all sorts of reports to work, including the one I want that uses a stored procedure and passes in values from the worksheet and refreshes the report when those values are changed in the workbook.  But what I don't get is that in the properties of the connection I have told it to NOT save the password and yet when I refresh the connection after the very first time I give it a userid and password it does not ever prompt me to login again.  I ultimately want to have multiple users accessing this report and have them be prompted for their login credentials.  But it seems like this information is being saved with the xlsx file and I can't change that.  Am I missing something in my setup that is causing this to happen?
0
Comment
Question by:seadogonline
4 Comments
 
LVL 19

Expert Comment

by:folderol
ID: 24340537
I'm not seeing the same on my workbook.  I severed the connection of the query from the datasource connection definition.  When I opened Data > From Other Sources, after it prompted for a Server and Login, I chose a table.  In the definitions however, I changed that to the SQL optionbutton.  I got a message stating that the query no longer matched the saved connection, did I want to continue.  After that, the password was no longer stored.  Excel does however, treat an entire workbook session as one data connection (even though you are getting a new connection everytime you refresh) so it will not prompt for the password again until the workbook is closed.  If you want to avoid this, you can't use the wizard, you have to write the connection in VBA.

0
 

Author Comment

by:seadogonline
ID: 24368159
Thank you for your reply.  Having the workbook prompt the user for login credentials the first time they open the workbook and use those for the entire session would be perfect but the problem that I'm seeing is that the userid and password is being stored with the workbook even though I'm telling it not to save the password.  Here is what I'm doing to reproduce this:
1.  Create a new workbook
2.  Go to Data > Get External Data > From Other Sources > From Microsoft Query
3.  In the Databases tab, choose AdventureWorksDW SQL Server 2005 ODBC connection and hit OK
4.  On the SQL Server Login prompt, enter the SQL Server Login ID and Password and hit OK
5.  Hit Close on the Add Tables window
6.  Go to View > SQL
7.  Enter a basic query like "Select * from DimProduct" and hit OK
8.  At this point I see the Query results so then I go to File > Return Data to Microsoft Excel
9.  On the Import Data window I leave the radio button on Existing Worksheet and default to Cell =$A$1 and hit OK
10.  Now I have the Query results in Excel.  If I go to Data > Properties and look at the properties for this new connection, on the Usage Tab I uncheck the "Enable background refresh" checkbox and mark the "Refresh data when opening the file" checkbox.  On the Definition tab I don't make any changes.  Here is the connection string:  "DSN=AdventureWorksDW;UID=sa;;APP=2007 Microsoft Office system;WSID=SERVER01;DATABASE=AdventureWorksDW;AutoTranslate=No;QuotedId=No;AnsiNPW=No"
11.  When I click ok to the Properties window I am prompted for SQL Server Login credentials again.  I enter these and click OK.
12.  I save the workbook and close Excel
13.  When I open Excel again the workbook refreshes automatically like I set it to do, but it does not prompt me to log in.  When I got to Data > Refresh All (or right-click and choose Refresh) it will again refresh the report without prompting me to log in.  At this point I cannot seem to get the workbook to prompt me for login info.
14.  If I rename my file from the "*.xlsx" extension to "*.zip" and look at the files in there, if I go into the "xl" folder and open the connections.xml file, I can see the userid and password stored in there.

I have tried this with the 'sa' user as well as other SQL users and I get the same results with different logins.
0
 
LVL 4

Accepted Solution

by:
souquetbressand earned 500 total points
ID: 26115545
You need to hide the connection string.  Please see the following article on securing the information.  Toward the middle to the bottom is the solution you're looking for.  See if that helps.  If you need further clarification, please feel free to post.

http://msdn.microsoft.com/en-us/magazine/cc164054.aspx
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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