Stored Credentials in PowerPivot Models

Follow

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

  1. Open the workbook, then open the PowerPivot model. PowerPivot > Data Model > Manage. 
  2. Go to the Home tab in the Get External Data section, click Existing Connections. 
  3. Select the datasource then click the edit button.
  4. Select Use SQL Server Authentication, enter the credentials, and tick the box next to Save my Password. Save the changes. 

Excel 2010

  1. Open the workbook, then open the PowerPivot model. PowerPivot > Open Power Pivot Window. 
  2. Go to the Design tab and click the Existing Connections button.
  3. Select the datasource then click the edit button.
  4. Select Use SQL Server Authentication, and enter the credentials then save the changes. 


Powered by Zendesk