Credentials for a PowerPivot connection can be saved within a PowerPivot model. These credentials allow the workbook to be refreshed using credentials local to your environment. Typically we use stored credentials when a site-to-site VPN has been setup. The VPN connection allows credentials to be passed to the database over a secured connection during data refresh.
NOTE: Each time the workbook is edited, the credentials need to be re-saved. The workbook does not retain the password, no matter how minor the edit.
The directions below show how to save credentials in existing connections using a SQL database. These directions also assume that there is a SQL user available for the connection.
Excel 2013
- Open the workbook, then open the PowerPivot model. PowerPivot > Data Model > Manage.
- Go to the Home tab in the Get External Data section, click Existing Connections.
- Select the datasource then click the edit button.
- Select Use SQL Server Authentication, enter the credentials, and tick the box next to Save my Password. Save the changes.
Excel 2010
- Open the workbook, then open the PowerPivot model. PowerPivot > Open Power Pivot Window.
- Go to the Design tab and click the Existing Connections button.
- Select the datasource then click the edit button.
- Select Use SQL Server Authentication, and enter the credentials then save the changes.