Error querying "Microsoft.ACE.OLEDB.12.0" provider from SQL Server

Question

This is not a question, but this post may be useful for community because I could not find the answer on the Internet.

Tested on

• Windows 2008 R2 x64 and Windows 2003 x64
• SQL Server 2008 SP3 x64
• Provider Microsoft.ACE.OLEDB.12.0 x64

SQL Server is running under domain account that is not admin of local machine. SQL Server connection authentication is Windows.

The issue is:

when creating and using a linked server or adhoc querying using this provider, you may receive errors like these:

OLE DB provider "MSDASQL" for linked server "xxx" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed"

OLE DB provider "MSDASQL" for linked server "xxx" returned message "[Microsoft][ODBC dBASE Driver] Your network access was interrupted. To continue, close the database, and then open it again.

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "xxx"

Could not find installable ISAM

OLE DB provider "MSDASQL" for linked server "xxx" returned message "[Microsoft][ODBC dBASE Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x16f4 Thread 0x2728 DBC 0x1ce64ae8 Xbase'

The reason is that the provider creates a temporary file in SQL Server's domain account's temporary folder, using user connect's credentials. Ordinary users does not have such permissions.

Answer 1

The solution is
grant "Modify" permission to sql server users on Sql Server's domain account's temporary folder. This folder ordinarily is

• Windows 2003: "c:\Documents and Settings\sql_server_account_name\Local Settings\Temp\"
• Windows 2008: "C:\Users\sql_server_account_name\AppData\Local\Temp\"