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.

Alsatian

I'm not familiar enough with this stuff to remember the error messages off the top of my head, so, sorry if this is irrelevant: Is it because the workbook is open when you run it? – Peerage

Workbook is not open. – Alsatian

Does it work when you run it as sysadmin? – Motor

No, still doesn't work. – Alsatian

Is it correct that the sheet is named "Sheet", not "Sheet1"? – Fishgig

might be similar answer here: #16987256 – Castroprauxel

Answer 1

Make sure you close the excel spreadsheet and run SSMS as admin.

Lifeboat

This fixed it for me – Imre

Running SSMS as admin did indeed work - but I feel this should not be necessary. Is there a reason why it needs to be run as admin? – Trephine

Answer 2

I found from this blog the two missing steps needed to get it working for me.

1) Check the permissions on the Temp folder

This is needed because the provider uses the temp folder while retrieving the data. The folder can be one of the below based on whether you use a local system account or network domain account.

For network accounts, folder is
:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp

and for local system account its
:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp

Right click on this folder and give it read write access to the account executing the code.

2) Check the MemToLeave memory area allocated

• Open SQL Server Configuration Manager -> Services -> SQLServer service.
• Right click and choose properties.
• Go to advanced tab and append -g512; to startup parameters property and it will resolve the issue.

You can also get it working without needing AllowInProcess if you follow the instructions in this MSDN article. The core instructions being:

To be able to execute linked server queries, also set RPC OUT to true on the linked server properties.

Permissions needed to set up linked server with out-of-process provider:

Verify below settings in DCOMCNFG: Start --> Run –> Dcomcnfg

1. Component services -->My Computer ---> Properties
   Verify that below options are set in the 'Default Properties' tab:

   • 'Enable Distributed COM on this computer' is checked.
   • Default Authentication = Connect.
   • Default Impersonation Level = Identify or Impersonate.

2. Component services --> My computer --> DCOM Config --> MSDAINITIALIZE

   • Right click on MSDAINITIALIZE --> Properties -->Security
   • Add the SQL Server service account (if connected to SQL server using SQL login) or windows user account under "Launch and Activation Permissions", "Access permissions" and "Configuration Permissions".
   • Give full rights to these accounts.

3. Restart the server

Paleface

Thank you very much for this advice, specifically permissions part. This is not common, it is an actually explanation, not just a solution. That's valuable. – Elsworth

Answer 3

I also met this issue and did the steps as yours,finally I met the error as yours. In the end, I use a SuperUser account and use this script as below and the issue has been resolved.

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

Mayhap

Answer 4

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.

Merimerida