SQL Server: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

Question

I am trying to run the following query:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Somefile.xlsx',
'SELECT * FROM [Sheet$]')

But I get this error:

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

I tried the following:

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

And:

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

When I go to 'Server Objects' -> 'Linked Servers' -> 'Providers', Microsoft.ACE.OLEDB.12.0 is listed.

Acess Database Engine x64 is installed, all Office products are 64-bit, and my SQL Server is also 64 bit.

If it matters, all users have access to the Temp folder in the Users folder.

(These are all suggestions I found in similar answers to this question)

Edit: Using SQL Server 2014.

user_522588 · Answer

We had this failure and nothing resolved until we noticed our service account was configured to use a domain account, not the default service account. Another server using Local System worked running the exact same job with the same impersonation settings and executing user.

We investigated and confirmed, adding the Act as part of the operating system right for the SQL Agent service account in the Local Security Policy under the Security Settings | Local Policies | User Rights Assignments resolved the issue.

SQL Server: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

4 Answers