I'm trying to create a Powerpivot workbook with data that users can refresh themselves before analysis.  We don't have Powerpivot data refresh in Sharepoint active, so I can't make use of that service.

When I create a Powerpivot data connection to SQL server using SQL Server Authentication (unchanging read-only username and password), and checking the box to save the password, the connection works fine for refreshing the data as long as I leave the workbook open.

If I close the file and re-open it, the password is gone from the data connection and the user cannot refresh the data.

I've tried configuring the data connection with the SQL native client provider and the SQL OLEDB provider and I see exactly the same results.

The connection string gets built with Persist Security Info=True and the Password shows up as hidden text (dots).  It stays this way through refreshes as long as the workbook is open.  However, once I close the file and re-open it, the connection string no longer has the Password parameter listed at all, not even as hidden text.  Persist Security Info is still there and set to True.  As might be expected, the connection string does not have the information needed to access the SQL Server instance.

This type of connection string using SQL Server Authentication still works fine with the password saved when used directly as an Excel data connection, or when used as a data source in Reporting Services.  It's only Powerpivot where the password gets blown away.

Is there a workaround for this issue?

It appears that this problem was introduced in PowerPivor 2012. To the best of my knowledge, it has not been resolved.

Hi,

Based on my research, the Allow saving password check box was not selected on the Connection tab of the Data Link Properties dialog box, when the connection to the data source was made.

Check the following link:

http://support.microsoft.com/kb/277600

Thanks Jaynet.

Unfortunately, the procedure described in the link you provided does not work for PowerPivot, at least in Excel 2010.

If I create the PowerPivot data connection using "From Database/From SQL Server" there is no option to "Allow saving password".  There is only the checkbox to "Save my password".  There is an "Advanced" button that allows you to set many additional properties of the data connection, but there is nothing like allow saving password.

If I create the PowerPivot data connection using "From Other Sources/Others (OLEDB/ODBC)" and then click the "Build" button, I get a dialog that is pretty close to the one in your link.  For the Provider tab, I can choose either the Microsoft OLE DB Provider for SQL Server or the SQL Server Native Client 10.0.  In the connection tab I do have the "Allow saving password" checkbox.  I check this and complete the rest of the data connection configuration.  After I save the file with this data connection and then re-open it, the data will not refresh.  If I edit the existing connection from the PowerPivot design tab, the Password parameter is completely missing from the connection string.  If I go back into the connection string builder, there is nothing in the password field (not even dots) and allow saving password is no longer checked.

I've even tried creating my own connection string in Notepad and pasting it into the connection string box (definitely including Persist Security Info=True).  This does not work either and PowerPivot erases the entire Password parameter just the same as if I had used the builder and checked Allow saving password.

I'm beginning to think that the previous poster, David Hager, has it right.  This may be a bug introduced with the latest version of PowerPivot.  If there is no workaround, it is a total pain for me.

I'm having the same issue.  I created a SQL user with access to one stored procedure so it isn't a big deal that I save the password in the PowerPivot connection.  I can close Excel, go back in, and it refreshes correctly.  However, it doesn't refresh on another person's computer.

I had a simular problem, PowerPivot / Excel didn't save the changes I did in the data connection, I was trying to change the data provider, but I think have found a workaround.

• Open File Explorer and navigate to your Excel file
• Change the file type from .xlsx to .zip
• Extract all files into a new folder, e.g. called "Test"
• Look for a file called connections.xml in the folder you extracted the files.
  I did find it under the folder xl
• Open the file in Notepad
• Now you will be able to change the properties of the connection string and other properties as well.
  In my case I changed the Provider from SQLNCLI11 to SQLOLEDB.1
• Save the xml file, make sure that you not save it as .txt
• Select all files and folders in the folder "Test" and zip them together again.
• Change the file type from .zip to .xlsx and the file name
• Open the filen in Excel

Not "user friendly", but in my case this did the work...

• Edited by jre1960 Thursday, January 31, 2013 8:55 AM

I had a simular problem, PowerPivot / Excel didn't save the changes I did in the data connection, I was trying to change the data provider, but I think have found a workaround.

• Open File Explorer and navigate to your Excel file
• Change the file type from .xlsx to .zip
• Extract all files into a new folder, e.g. called "Test"
• Look for a file called connections.xml in the folder you extracted the files.
  I did find it under the folder xl
• Open the file in Notepad
• Now you will be able to change the properties of the connection string and other properties as well.
  In my case I changed the Provider from SQLNCLI11 to SQLOLEDB.1
• Save the xml file, make sure that you not save it as .txt
• Select all files and folders in the folder "Test" and zip them together again.
• Change the file type from .zip to .xlsx and the file name
• Open the filen in Excel

Not "user friendly", but in my case this did the work...

• Edited by jre1960 Thursday, January 31, 2013 8:55 AM

I had a simular problem, PowerPivot / Excel didn't save the changes I did in the data connection, I was trying to change the data provider, but I think have found a workaround.

• Open File Explorer and navigate to your Excel file
• Change the file type from .xlsx to .zip
• Extract all files into a new folder, e.g. called "Test"
• Look for a file called connections.xml in the folder you extracted the files.
  I did find it under the folder xl
• Open the file in Notepad
• Now you will be able to change the properties of the connection string and other properties as well.
  In my case I changed the Provider from SQLNCLI11 to SQLOLEDB.1
• Save the xml file, make sure that you not save it as .txt
• Select all files and folders in the folder "Test" and zip them together again.
• Change the file type from .zip to .xlsx and the file name
• Open the filen in Excel

Not "user friendly", but in my case this did the work...

Thanks for your reply jre1960!

That's a pretty interesting way to edit the Excel file.

I tried it for my problem.  The connections.xml file in my case did not contain the connections to my data sources, but rather contained only the connection between PowerPivot and the workbook.

I was able to find my connection strings in one of the itemxx.xml files in the customXml folder.

As expected, the password part of the connection string was not there.  I added it and zipped the files back up.  I was concerned that the unpacking and packing might not result in a valid Excel file, but it opened without a problem!

I am sad to report that the password manually inserted in the connection string through the xml file is not there and the user is prompted for the password just as before.  I was really expecting this to work so I double-checked to be sure that the xml file had actually been updated with the password and that I had built the Excel file out of all the zip components, and it all checked out.

Someone really, really thinks they know what's best for us here and has been very effective at making sure a password cannot be saved in a connection string.

Any update on this?

Nothing new on my end.   I have no expectation that it will ever be resolved, but I haven't tried anything since Februrary.  I've gone with a completely different approach for the project that generated this question.

No, it still has not been resolved in Excel 2013.  I wish the developers would fix this issue.

I was able to get this working in 2013.  

On the Excel ribbon, select the 'Data' tab.  Select 'Connections' to open the 'Workbook Connections' dialog. Select the connection used in the PowerPivot model and select 'Properties'.  Check the 'Save Password' check box.  When you refresh the PivotTable you should get prompted to enter the password. Enter the password and save the workbook. 

I did not check the 'Save Password' box within the PowerPivot environment.  

Hope this helps some folks.

• Proposed as answer by Tak541 Tuesday, October 22, 2013 7:12 PM

I was able to get this working in 2013.  

On the Excel ribbon, select the 'Data' tab.  Select 'Connections' to open the 'Workbook Connections' dialog. Select the connection used in the PowerPivot model and select 'Properties'.  Check the 'Save Password' check box.  When you refresh the PivotTable you should get prompted to enter the password. Enter the password and save the workbook. 

I did not check the 'Save Password' box within the PowerPivot environment.  

Hope this helps some folks.

• Proposed as answer by Tak541 Tuesday, October 22, 2013 7:12 PM

This works.  Thanks Imil10!

After two days of searching to no avail I am incredibly frustrated by this problem-- it is a deal breaker for us.

Imil10, sadly your solution did not work for me. Is it possible you could post your precise steps- from creating the connection to the refresh?

I would be eternally grateful to anyone who could fix this. I am having further issues getting scheduled refreshes done in PowerBI and I suspect it is related to this problem???

btw- I have also tried using the secure store-- I couldn't get that to work either. Any other ideas?

This worked for me.
Thanks lmil10

• Edited by Robert_unexpected Wednesday, July 09, 2014 9:51 PM

This worked for me.
Thanks lmil10

• Edited by Robert_unexpected Wednesday, July 09, 2014 9:51 PM

I think this is an ongoing issue.  When we first implemented SharePoint 2013 a year ago, I finally figured out this workaround:

http://surviving-sharepoint.blogspot.com/2013/10/how-to-refresh-powerpivot-data.html

I hope this helps you all out.

-Brandon Showers

This worked for me too, but I had to open the workbook, enter the password, check "Save Password", save the workbook and close it.

Fantastic! Well done, this is a huge help!