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?