Setup a Linked Server to MS Access from SQL Server – Beat the errors

Normally, it is easy enough to setup a Linked Server on SQL Server to other data sources. Problems are usually caused by one of the usual culprits that have to be addressed

• SQL Logins simply do not work well when trying to do this type of setup
• The Windows login has to have permissions to the file (on a drive or network share)
• The appropriate drivers have to be setup (64 bit / 32 bit)
• …will add more here based on user comments to this post

After creating my Linked Server to MS Access, using my admin account (Windows login is part of local administrators), everything was fine. However, when the user tried to access it, he ran into errors.

Let us begin with how I setup the Linked Server:

The first error was:

I searched and found this solution to fix the issue:

USE [master]
GO

EXEC master.dbo.sp_MSset_oledb_prop
    N'Microsoft.ACE.OLEDB.12.0',
    N'AllowInProcess',
    1
GO

EXEC master.dbo.sp_MSset_oledb_prop
    N'Microsoft.ACE.OLEDB.12.0',
    N'DynamicParameters',
    1
GO

It works on my machine moment!

At this point, as an Windows admin user, I was able to test the link and query it fine and run queries against my Linked Server without any issues.

SELECT *
FROM OPENQUERY([TESTDB], 'SELECT * FROM Table1')

However, non-Windows admin users were running into this error when they tried to use the same Linked Server.

TITLE: Microsoft SQL Server Management Studio
——————————

The test connection to the linked server failed.

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “TESTDB2”.
OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “TESTDB2” returned message “Unspecified error”. (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

——————————
BUTTONS:

OK
——————————

I figured that it had to do with permissions to the MS Access file/folder but that was not it. I tried the different security options for the user in the “security” tab of the Linked Server properties but that nothing helped.

So, I reached out to my fellow DBA’s to see if they had seen anything like it and one of them suggested this solution based on an error in the Event Log

https://blogs.msdn.microsoft.com/dataaccesstechnologies/2011/09/28/troubleshooting-cannot-create-an-instance-of-ole-db-provider/

Event log message was:

The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID

{2206CDB0-19C1-11D1-89E0-00C04FD7A829}

and APPID

{2206CDB0-19C1-11D1-89E0-00C04FD7A829}

to the user [******] SID (S-1-5-21-1935393997-861567501-725345543-90175) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.

However, this did not work either!

The Solution:

At my behest, another DBA fought through the issue and discovered that giving read/write privileges to the Temp folder of the SQL service account user is what fixes the issue. Keep reading for the details..

https://support.microsoft.com/en-us/kb/814398

Here it is in his own words:

We got this working…. It turns out the user running the query needs to have R/W access to the temp directory of the SQL Server service account.  This seems like a very silly and very insecure thing to me.

I came across this discussion below and gave my non-admin account RW access to the SQL Server Service account’s default temp folder and it worked.  I’ve granted RW access to non-admin user who was experiencing issues.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6f663ab8-c75d-419b-a4bf-bb6c0f5b84c4/ole-db-provider-microsoftaceoledb120-for-linked-server-null-returned-message-unspecified?forum=transactsql

I just added Read/Write Rights on the folder C:\Users\\AppData\Local\Temp and everything started to work. Please note this is different from what most articles recommend doing which is to give Read/Write rights to folder C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp folder

The link below also has the two solutions that I tried but I found it after I fixed the issue:

http://sqlish.com/msg-7302-level-16-state-1-line-1-cannot-create-an-instance-of-ole-db-provider-%E2%80%9Cmicrosoft-ace-oledb-12-0%E2%80%9D-for-linked-server-%E2%80%9Cnull%E2%80%9D/

At the least, this post will act as a reference for myself. At best, it will fix your MS Access Linked Server problem for you. In any case, please leave feedback that might help other readers. What should have been a simple 10 minute thing, turned into a day or two thing for me!