Trust Center Part 5: Data Connection Security

Today we have the fifth and final guest post from Sam Radakovitz, Excel Program Manager.  Sam is writing about the Trust Centre, a new feature for Office 2007.

To properly secure Excel, the Data Connection security has been moved into the new Trust Center space.  This means that the prompts from data connections you got on open from previous versions of Excel will now be in the Trust Bar and their security options in the Trust Center.

To be specific, when I say ‘data connection’ I’m referring to:

  • Query Tables
  • Pivot Tables
  • Tables that connect to Share Point
  • Connections in the workbook
  • Sheet Data Functions
  • DSP Connections

Upon opening of a workbook with a connection, you’ll get the Trust Bar informing you that data connections are disabled.  This is similar to the way we block Macros.

(Click to enlarge)

At this point, no data connection will be allowed to query outside of Excel.  If you want to refresh a data connection, there are a few ways to do it:

Enable Content – choose the ‘Enable Content’ button from the Trust Bar and select to enable data connections.  This will enable all connections for the workbook.

Click Refresh – clicking refresh will trigger a prompt for you to enable connections.  Choosing to enable connections will then allow that workbook to refresh all connections it has without any further prompts.

Specific Operations – some operations require us to connect to data, choosing that operation will prompt you to enable connections for the workbook in order to complete the operation.  These operations vary on the type of connection and if there is a local cache available, example: displaying the filter dropdown for a pivot connected to Analysis Services requires Excel to connect to the Analysis server, in this situation you would be prompted to enable connections before seeing the filter dropdown.

In prior versions of Excel, you had to manually edit the registry to setup data connection security.  For Excel 2007, the Trust Center dialog hosts those options in the External Content tab.  By default we block data connections and show a Trust Bar notification, allowing you to enable connections.  This is similar to having macros in your document, by default you are secure, you can inspect the document, and then choose to enable blocked features if you need them.

VBA and Data Connections
Like previous versions of Excel, running code can always refresh connections regardless of the data connection Enabled/Disabled state.  With the new data connection security changes to Excel 2007, we’ve altered the data connection engine to be more secure with a side effect of possibly blocking VBA calls to refresh data connections in some cases.  To help combat that, we’ve added a couple of object model calls to control the blocking of data connections:

Workbook.ConnectionsEnabled – Read-only Boolean that let you know if connections for the workbook are enabled or disabled.

Workbook.EnableConnection – Method that enables all the connections for the workbook.

Workbook Links?
One notable exception from the data connections list is workbook links.  The only part of workbook links that has been changed is the Automatic Update dialog on open being moved into the Trust Bar.  Other than that, workbook links will continue to function as they did in previous versions of Excel.