?
Solved

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

Posted on 2009-05-07
4
Medium Priority
?
506 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
[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
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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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 …

770 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