Excel 2010 protected mode trashing my linked workbooks

After installing Excel 2010 on our test network prior to a rollout of Office 2010 across the whole organisation we noticed a problem with linked workbooks when they are opened in Protected Mode.

Protected mode blurb from Microsoft:

By default, Protected View is enabled in Excel 2010, PowerPoint 2010, and Word 2010. However, files open in Protected View only under certain conditions (see below). In some cases, files bypass Protected View and are opened for editing. For example, files that are opened from trusted locations and files that are trusted documents bypass several security checks and are not opened in Protected View.

In our case it became obvious that workbooks which had previously been downloaded from the internet or had been saved from an email attachment were opening in protected view. This seemed sensible to us and did not cause any problems.

However, we rely on a lot of workbooks sent by suppliers which are saved to our network and then have our own workbooks linking to the data within them. When a supplier workbook opened in protected mode and then we opened our own linking workbook all the links to the supplier workbook were trashed.

We setup a test workbook just to work out if there was something corrupt within our own workbooks but, no, we got the same problem. We put together a set of test workbooks and a summary and contacted Microsoft:

Summary:

Opening a workbook may result in it being opened in Protected Mode (Read only due to originally being an email attachment or internet download). If you then open a workbook that was setup to link to it all the links are trashed by inclusion of weird :#REF entry:

e.g.

=’U:JAMES[sub_wb2.xlsx]Sheet1′!$B3

becomes:

='[sub_wb2.xlsx]Sheet1:#REF’!$B3

Opening main workbook first and then double clicking on link works ok (although the initial double click does not go to the linked cell for some reason – subsequent links do). In this case for some reason the workbook is not picked up as needing to be in protected mode!

Response from Microsoft confirmed there was a bug:

I totally understand your concerns here and how it can cause confusion for users. After researching further and testing the issue I can see that all issues including the one you have just highlighted below were explored by the development team during the testing of Excel 2010. However, the development team could not make any changes without breaking other components of Excel and thus a decision was made to postpone a fix and attempt to rectify for future releases. Their statement was as follows:

“Our initial assessment of this request has identified that a correct fix would require redesign work outside the scope of a hotfix cycle. Please know we carefully review all hotfix requests because each code change that we implement must maintain or improve the quality and stability of the product. We strive for this to ensure the continuing integrity of the code base and to maintain a supportable product. While we recognize the impact this issue is having on this customer, we cannot compromise the stability of the product’s code base using the Hotfix process”

I can certainly highlight your case and pass on your comments to the dev team. However, as previous fixes were rejected it will be highly unlikely that it will be possible to resolve this for a future Service Pack release and more than likely will be looked at for the next release of Excel. When researching I could only see three reported issues with Protected View and Link behaviour and it is unfortunate that you have come across all three. As it stands the official guidance is to use the Trusted Locations settings. This can be controlled centrally using Group Policy settings and minimise any I.T overheads.

So Microsoft were not going to do anything about it anytime soon!

Our final response was:

I understand the need to maintain product integrity and that this might mean bug fixes which impact large parts of the product cannot be implemented with a mid-product release via Hot-Fix or Service Pack. However, the seriousness of this bug would, in my view, warrant the extra resources required to produce a fix. It also does not give me great confidence with the entire protected view code if they are of the opinion that any changes will have a major impact on other areas. 

Yes, we could use trusted locations but this does increase the burden on IT and I would rather just switch off Protected View via group policy rather than use something buggy and untrustworthy. Also, it seems to me that having trusted locations negates the whole point of protected view as anyone can then save documents from the internet into the trusted location and these files would then be loaded unprotected – or am I missing something? Also, it does not address the 3rd issue of double clicking a link and the sub document being opened without being put into protected mode when it should be – if trusted locations are being used but a workbook is linked to an untrusted location the sub workbook will open in unprotected view whilst the user will assume it is from a trusted source because that has been set up.

I have not received any further notification that this problem has been fixed and testing still produces the same error.

Solution: don’t use protected view. It can be turned off by going to Excel Options, Trust Center, Protected View and untick all the options within the Protected View section:

Alternatively use group policy to turn it off:

User Configuration, Admin Templates, Microsoft Excel 2010, Excel Options, Security, Trust Center, Protected View:

Note the use of ‘enabled’ to turn this option off – see notes:

 

Microsoft blurb on protected mode opening conditions:

By default, files open in Protected View if any one of the following conditions is true:

• A file skips or fails Office File Validation Office File Validation is a new security feature that scans files for file format exploits. If Office File Validation detects a possible exploit or some other unsafe file corruption, the file opens in Protected View.

• AES zone information determines that a file is not safe Attachment Execution Services (AES) adds zone information to files that are downloaded by Microsoft Outlook or Microsoft Internet Explorer. If a file’s zone information indicates that the file originated from an untrusted Web site or the Internet, the downloaded file opens in Protected View.

• A user opens a file in Protected View Users can open files in Protected View by selecting Open in Protected View in the Open dialog box, or by holding down the SHIFT key, right-clicking a file, and then selecting Open in Protected View.

• A file is opened from an unsafe location By default, unsafe locations include the user’s Temporary Internet Files folder and the downloaded program files folder. However, you can use Group Policy settings to designate other unsafe locations.

In some cases, Protected View is bypassed even if one or more of the previously listed conditions are met. Specifically, files do not open in Protected View if any one of the following is true:

• A file is opened from a trusted location.

• A file is considered a trusted document.

Relevant links:

http://blogs.technet.com/b/office2010/archive/2009/08/13/protected-view-in-office-2010.aspx

http://office.microsoft.com/en-us/excel-help/what-is-protected-view-HA010355931.aspx

 

 

 

Advertisements

Author: James

IT Manager - Network, Web coding, MS SQL and Online Mapping expert

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s