Solved

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

Posted on 2009-05-07
4
439 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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

705 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

17 Experts available now in Live!

Get 1:1 Help Now