Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server

Question

This is going to be a long story I'm afraid ;-(

I have to read some text-files into a SQL database. This database is on a SQL 2008 R2 x64 database server. I want to use a linked server so after some research I found that I had to install the Microsoft.ACE.OLEDB.12.0 x64 provider. I did this on my local machine and after I created my linked server I had some trouble reading text files.

The provider for the linked server was set to use Microsoft.ACE.OLEDB.12.0, the datasource pointed to the correct folder and the providerstring was set to "TEXT", but I could not test the connection. It was throwing this error:

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

After some research, I found that the SQL Service was running under the NETWORKING SERVICE-account. When I changed this to Local Account, everything was working fine.

Note: I also had to change some settings for the provider, "Allow inprocess" is checked, otherwise it was not possible to browse the files in the configure datasource folder.

But now I try to implement this solution on my customers network and I cannot get it to work. It is also a SQL 2008 R2 x64 Db-server, Microsoft.ACE.OLEDB.12.0 provider is installed, but it still throwing the mentioned error. So I asked them which account is used to run the SQL Service. This is an AD-account belonging tot the local Administrator group of this server and therefore it had enough privileges as far as I can see. The user who is testing is connected to the database engine with a windows AD account, also belonging to the administrator group. But when he tries to test the connection, it throwing the same error.

An other thing I tried is using the OPENROWSET, this also give an unspecified error. And yes I have set the "Ad hoc distributed query" using the next statement:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

So here I got my development box, where everything is working fine. But when I deploy to the test-server it has these connection-problems. Actually I don't know what to do or check anymore. I have been working and searching for several days now. Does anyone have some other suggestions I can try?

Answer 1

When you set up a linked server, you have the choice to re-use the credentials of the user, use a specific account or be anonymous. When you tested it from the same machine, and SQL Server was running as a local account, it worked because it was able to re-use the admin privileges of you while you were logged-in.

To get it to work in a network scenario, things get tricky. If you are using AD, you need to use AD (principalName) to configure the Kerberos trust between the SQL Server machine and the machine hosting the MSAccess file. This usually takes some fiddling to get it right. This TechNet article is long-winded, but contains a lot of config steps and suggestions: http://support.microsoft.com/kb/319723.

Also, you will need to set the server back to use the NETWORKING SERVICE account again so you can grant it permissions. On the other machines, your SQL Server will be using a hidden account like [domain]\[MachineName]$. If the MS Access DB is on the same machine as your SQL Server, you still need to grant (NTFS) read/write permissions on the MSAccess DB (file) to the accounts for the SQL Server process and to the network users.

Answer 2

This is likely a case of the exact Provider being installed on your machine / SQL server. Some Providers are not available by default and so if you haven't installed it the it won't be able to be initialized.

You can verify this by opening SQL management studio in Server Objects -> Linked Servers -> Providers and see if it is listed.

If not, you need to install the Microsoft Access Database Engine (either x86 or x64 depending on your SQL server install) which will install the provider for you.

Answer 3

You should configure security for Class MSDAINITIALIZE in Component Services like it described on Microsoft MSDN

MSDAINITIALIZE is a COM class that is provided by OLE DB. This class can parse OLE DB connection strings and load/initialize the provider based on property values in the connection string.

MSDAINITILIAZE is initiated by users connected to SQL Server. If windows authentication is used to connect to SQL Server, then the provider is initialized under the logged in user account. If the logged in user is a SQL login, then provider is initialized under SQL Server service account. Based on the type of login used, permissions on MSDAINITIALIZE have to be provided accordingly.

It's was a reason for my same case.

Answer 4

Run Management Studio as Administrator.