14

I've got a BI Dashboard in Excel 2010 using Powerpivot, which is connected to a number queried tables on one SQL Server. When the connections were setup, I checked the box to 'save password' on each one. However, whenever my users reopen the document and go to the Powerpivot window and select 'RefreshAll' then they have to type in the password multiple times (once for each table), which is not suitable.

I have looked here and here and seem to be experiencing the same issue as a number of other people. I have started again from scratch, ensuring that the 'save password' box is definitely checked on each connection string.

The only workaround I can think of is by user Windows Authentication, but this document is intended for widespread use, and as such this will require a lot of maintenance, and will really annoy my server admin :)

Does anyone have a workaround, or any way of resolving the problem?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Oliver Lockett
  • 474
  • 3
  • 22
  • I'm having the same issue when connecting to a MySQL database through an ODBC connection. Hence, I don't think it has anything to do with the type of database that is being connected to. I've also tried adding the password explicitly in the connection string, but it is removed after reopening the document. – Wouter Apr 29 '15 at 13:06

2 Answers2

0

This solution comes close, but doesn't seem to work in my case. Might work for others though...

Solution summary:
In the main Excel window in the Data tab, choose Connections, then select the workbook connection that corresponds to your PowerPivot connection. Click on Properties, switch to the definition tab, and mark “Save password” box.

Source:
http://cpa-it.com/password-not-saved-in-powerpivot-connection-when-using-sql-authentication/#comment-10654

Wouter
  • 1,829
  • 3
  • 28
  • 34
0

I've found a workaround that works for me, but might not be an ideal solution for others.

In the main Excel window, you can create a new connection using a connection file (.odc). If you create this file on a shared network drive that all users can access, and select to store the password in the connection file, the password is -actually- stored.

You can then go on and use this connection in the PowerPivot window. It will no longer ask for a password when refreshing your tables.

Of course this is only a useful workaround within a company LAN, and if there are no security implications for storing the password on a fileserver in an odc file.

Wouter
  • 1,829
  • 3
  • 28
  • 34
  • I've tested this again, a year later, and can't reproduce this. I think my Excel has been upgraded to a new version... – Wouter Apr 19 '16 at 15:09