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.

Answer 1

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

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