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

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?
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

souquetbressandConnect With a Mentor Commented:
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.
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.

seadogonlineAuthor Commented:
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.
All Courses

From novice to tech pro — start learning today.