SQL SERVER – Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server

SQL
6 Comments

SQL SERVER - Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server errorexit As you might have seen, along with performance tuning related work, I also help community in getting answers to questions and troubleshoot issue with them. A few days ago, one of community leader and user group champion contacted me for assistance. He informed me that they are using Microsoft Access Database Engine 2010 provider to read data from an excel file. But they are seeing below error while doing test connection. Let us learn about error related to OLE DB provider.

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

This error appears only from the client machine, but test connection works fine when done on the server. The same error message is visible via SSMS.

Solarwinds

EXEC master.dbo.sp_addlinkedserver @server = N'EXCEL-DAILY'
	,@srvproduct = N'Excel'
	,@provider = N'Microsoft.ACE.OLEDB.12.0'
	,@datasrc = N'D:\EMPLOYEE_ATTEN_DATA.xlsx'
	,@provstr = N'Excel 12.0; HDR=Yes'

After a lot of checking, we found that the account which was used from client and the server was different. So, I have captured Process Monitor for both working and non-working test connection. It didn’t take much time to see below in non-working situation.

sqlservr.exe QueryOpen C:\Users\svc_app\AppData\Local\Temp ACCESS DENIED

WORKAROUND/SOLUTION

So, we went to the SQL Server machine and gave full permission to the file path which was listed in process monitor as access denied.  C:\Users\svc_app\AppData\Local\Temp.

After permission was given test connection worked and client machine could read data via excel using linked server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Creating Azure VM for SQL Server Using Portal
Next Post
dbForge Studio for SQL Server – Ultimate SQL Server Manager Tool from Devart

Related Posts

6 Comments. Leave new

  • Having the same problem. I’m getting “NAME NOT FOUND” HKUS-1-5-80-3880718306-3832830129-1677859214-2598158968-1052248003_CLASSESMicrosoft.ACE.OLEDB.12.0CLSID

    Does this mean anything?

    Reply
  • technocrat millenium
    June 11, 2018 12:05 pm

    Thank you. That solved my problem. Mine was the same problem . AccessDenied on SQL Temp folder.

    Reply
  • I am able to create linked server with the above mentioned script, but cannot read excel file with the below query..
    SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
    ‘Excel 12.0 Xml;HDR=YES; IMEX=1; Database=D:\Desktop\task.xls’,
    ‘Select * from [Sheet1$]’)

    Reply
    • I get the below error:
      OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error”.
      Msg 7303, Level 16, State 1, Line 1
      Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

      Reply
  • angel dario rodriguez
    October 9, 2019 9:41 pm

    I am having the same problem..

    Reply

Leave a Reply

Menu