Excel SSAS Pivot Table warning "Security Warning Data connections have been disabled"

Q: When opening Excel 2007 file with Pivot table connected to SSAS 2005 I am getting message:
"Security Warning Data connections have been disabled" and there is an "Options..." button
that lets me choose "Enable this content". How to disable this warning and enable connection to SSAS database by default?

A:You have to add location where you excel file resides into trusted location list. You can do this by following these steps:

• Start Excel 2007, click on the "Office" button
• Click on the "Excel Options" button.
• Select "Trust Center" tab and click on the "Trust Center Settings..." button.
• Select "Trusted Locations" tab
• Make sure that checkox "Disable all Trusted Locations. Only files signed by Trusted Publishers will be trusted." is not checked (do this just if that complies with your company security policies).
• Click on "Add new location..." button.
• In the Path enter location that will be trusted. Example: "C:\Users\UserID.Domain\Documents". If required, select checkbox "Subfolders of this location are also trusted". Optionally enter description for this location
• Click "OK" button
• Click "OK" to close "Trust Center" dialog
• Click "OK" to close "Excel Options" dialog.

Now when you open Excel files with Pivot Table connection to SSAS 2005 server by default will be enabled.

If you are using External connections (for example Sharepoint), then instead you should enable all data connections for external content in the Excel Trust Center. Steps for that are:

• Start Excel 2007, click on the "Office" button
• Click on the "Excel Options" button
• Select "Trust Center" tab and click on the "Trust Center Settings..." button
• Select "External Content" tab
• Select "Enable all Data Connections". Other options are "Prompt user about Data Connections" and "Disable all Data Connections".
• Save changes.

Added May 19, 2009. Excel MVP Héctor Miguel suggested that if you are looking to script these option changes, you could script registry entries for [HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security] registry key (Learn how to export/import registry files.)

Here is a sample registry file for my machine:

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security]
"VBAWarnings"=dword:00000001
"DataConnectionWarnings"=dword:00000000
"WorkbookLinkWarnings"=dword:00000000

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security\Trusted Locations]

"AllLocationsDisabled"=dword:00000000
"AllowNetworkLocations"=dword:00000001

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security\Trusted Locations\Location0]
"AllowSubFolders"=dword:00000001
"Path"="C:\\Program Files\\Microsoft Office\\Office12\\XLSTART\\"
"Description"="3"

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security\Trusted Locations\Location1]
"Path"=hex(2):25,00,41,00,50,00,50,00,44,00,41,00,54,00,41,00,25,00,5c,00,4d,\
  00,69,00,63,00,72,00,6f,00,73,00,6f,00,66,00,74,00,5c,00,45,00,78,00,63,00,\
  65,00,6c,00,5c,00,58,00,4c,00,53,00,54,00,41,00,52,00,54,00,00,00
"Description"="4" 

Values for parameters:

• Security\DataConnectionWarnings: 0 - "Enable all Data connections", 1-"Prompt user about Data connections", 2- "Disable all data Connections"
• Security\WorkbookLinkWarnings: 0 - "Enable automatic update for all Workbook Links", 1-"Prompt usre on automatic update for Workbook links", 2 - "Disable automatic update of Workbok Links"
• Security\Trusted Locations\AllLocationsDisabled : 1 - Disable all Trusted Locations (checked), 0 - not disabled.
• Security\Trusted Locations\AllowNetworkLocations: 1 - Allow Trusted Locations on my network, 0 - not allowed.