Prompt for SQL connection password per session in Excel PPIV 2012 SP1

Symptoms

After the release of Microsoft SQL Server Analysis Service 2012 Service Pack 1 (SP1), the PowerPivot add-in for Microsoft Excel 2010 and for Microsoft Excel 2013 has a design change that prompts for the password per session for a non-windows integrated connection such as SQL Connection. For example, if a user saves a password for SQL Connection for a PowerPivot data model and then closes the workbook in Excel 2010 or Excel 2013 together with SQL Server 2012 SP1 PowerPivot add-in, the user will be prompted to enter the password upon reopening the closed workbook, and to perform data refresh of the PowerPivot data model because it involves a new session. 

Note This design change behavior is different from pre-SP1 versions such as SQL Server Analysis 2012 PowerPivot add-in or SQL Server 2008 R2 PowerPivot add-in. The design change was originally intended to address situations when a connection string rebuild was initialized on a session where the password had not been entered, and thus subsequent data refresh operations will fail for the PowerPivot data model access. 

Resolution

In SQL Server Analysis Service 2012 SP1 PowerPivot add-in for Excel 2010 and for Excel 2013, it is possible to revert the behavior so that users will not be prompted to enter a password upon reopening the saved workbook and performing a data refresh of the PowerPivot data model. To opt in to the pre-SP1 behavior, follow these steps:

1. Obtain and apply the required hotfix or cumulative update:

   • For SQL Server Analysis Service 2012 SP1 PowerPivot add-in for Excel 2010, it is first fixed in SQL Server 2012 SP1 CU8 .

   • For SQL Server Analysis Service 2012 SP1 PowerPivot add-in for Excel 2013, it is first fixed in Office 2013 cumulative update for May 2014.

2. In Registry Editor, click the following registry key:

   • For Excel 2010: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Gemini\Settings

   • For Excel 2013: HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\PowerPivot\Settings

   
   

3. Right-click Settings, point to New, and then click DWORD value.

4. Type PasswordPromptBehavior and then press ENTER.

5. Right-click PasswordPromptBehavior and then click Modify.

6. In the Value data box, type 1 and then click OK.

Note PasswordPromptBehavior = 1 is not to prompt password for non-windows integrated connection, 0 is to prompt password for non-windows integrated connection.