Working with the 2010 Office ACE provider

Updated: 18-Aug-2010

Microsoft does not recommend or support the use of Office applications in a server-side environment (see http://support.microsoft.com/kb/257757 ). In the case of PowerPivot, the data refresh facility is implemented within an NT Service and, as such, falls under this restriction. If you are using Office data within a PowerPivot workbook (e.g. importing the data from flat files, Excel workbooks, or MS Access databases) and you need to automate the refreshing of that data, then Microsoft recommends that you move the data to a supported server-based, multi-user environment, such as a relational database. That being said, users may still try to use the ACE provider and this information is designed to ease their pain in the process.

Ok. I’ve promised some best practices – here is my first one.

What is the 2010 Office ACE provider and why is this important to PowerPivot? The 2010 Office System Driver for Data Connectivity Components (aka the Office ACE provider) is a OLE DB provider that can be used to read data from and write data to Office 2010 system files such as Microsoft Access Beta (mdb and accdb) files and Microsoft Excel 2010 (xls, xlsx, and xlsb) files. You can also use it to access text files. The provider “Microsoft.ACE.OLEDB.14.0” which includes both 32-bit and 64-bit versions, is available for download here: ( http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en ). PowerPivot uses the ACE provider as part of a data refresh job to import data.

In this blog, I would like to talk about two important topics concerning ACE: (a) how to get it and use it; and (b) what are some of the best practices that we’ve recently encountered when using it internally for some dogfood servers.

First, how do you get and use the ACE provider:

• You must manually download and install the ACE provider on each PowerPivot app server where PowerPivot for SharePoint is installed. Since you’ve used this OLE DB provider to import data on the PowerPivot Excel add-on wizard, if you in intend to use it for data refresh on your SharePoint farm, then the same OLE DB provider must be on the PowerPivot app server.
• Since SharePoint runs only under 64-bit servers – this means that you must use the 64-bit version of the ACE provider. Your client can be 32-bit, but the SharePoint server forces the use of the 64-bit version on the PowerPivot app server.
• The actual target file must be located on a shared location such as a file share, or some other location that can be reached by both the client import data wizard, and the server-side, SharePoint PowerPivot app server. It cannot be located on the client’s local desktop, e.g. C:\folder\source.xlsx.

So, now you’ve installed it, what is it like when you use it in production? In general, it runs as advertised, but we’ve found two issues that those folks doing deployments should be aware of:

Issue #1 – The ACE provider creates temporary files (copies) from the source data files. The temp files are created after the data refresh job has impersonated the user specified in the job schedule. Since normally the temp folder is ACL’ed to only allow the PowerPivot System service account access, the file creation may fail. If using the NEW SERVER configuration (the single all-in-1 server installation), then the system uses the same service account for the PowerPivot System service account as it uses the PowerPivot unattended account. So if you click on “Connect using the credentials already stored inside this workbook” then things work OK. But if you specify custom credentials, then it is likely that the ACE provider cannot create a file in the TEMP folder, then an error is returned to the data refresh job:

“OLE DB or ODBC error: Unspecified error. A connection could not be made to the data source with the DataSourceID of <guid>, Name of <name>. An error occurred while processing the ‘<sheet name>’ table. The operation has been cancelled.”

To solve this problem, we need to add an ACL to the PowerPivot System service account’s TEMP folder that allows users read+write access. We don’t need all accounts, just those who are specified in the custom credentials section of the data refresh job that is using the ACE provider. The account is the one specified here:

To solve the issue: (on each PowerPivot app server) Use Windows Explorer, running in administrator mode, to modify the “C:\Users\<PowerPivot System service account>\AppData\Local\Temp” folder by adding an ACL that gives the accounts that will be using the ACE provider limited access, i.e. Read+Write.

As you would expect this is a common Windows management issue. There are several approaches to use: (in increasing better ‘best practices’ order)

• Give “Everyone” read+write permission – this is the easiest, but least secure. Typically the C:\Users subfolders are shared out over the network, so any penetrators would have to actually be running on the app server, so this isn’t too bad, but the Everyone group contains lots and lots of accounts.
• Give “<domain>\Authenticated Users” read+write permission – this is better, but provides access to accounts that are not using your SharePoint farm. Clearly this is a large population – maybe even still too large.
• You might already have domain groups that you are using to control access to your SharePoint farm. If those groups exist, re-use them here and give those groups read+write access. This approach limits access to just SharePoint users, but users still might not be using ACE and they have access.
• Create a new local group, called “ACE Users” – Initially this group is empty; as users encounter problems, have your machine administrators add users to this group on request; in the best of all worlds, you would have a provisioning system so user can request access and have the system auto-remove them periodically.

Anyway, somehow regardless of what technique you use, the TEMP folder ACLs must be changed to allow access.

Issue #2 – To actually access the remote files the ACE provider uses SMB via the UNC name (typically in the form: \\server\share\folder\workbook.xlsx)

If the target location is a true file share, then things are OK. If however the target location is a SharePoint farm acting as a file share (using WebDAV protocol), then additional OS components must be installed. Typically this is done because users are storing Access databases, Excel files, or text files that are stored back on the same SharePoint farm as the source workbook (a kind of a ‘loopback’ for data access). If wish to provide this capability to end-users, then you must install the “Desktop Experience” role to the PowerPivot app servers.

If you don’t have “Desktop Experience” added as a server role and a data refresh job attempts to use the ACE provider to access a remote SharePoint farm via its UNC name, then the job will fail with:

OLE DB or ODBC error: Failure creating file.; 3436. A connection could not be made to the data source with the DataSourceID……”

The Desktop Experience role is not by default installed on a server OS and thus your administrator will have to add it as a role to the server. Again, this is not needed if the target files are located on true file shares, or equivalent. It is only needed if the app server is going to be referencing a SharePoint farm (either itself or a different remote farm) using a UNC name.

Enjoy.