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

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.

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)

SQL Connection, SQL Error Messages, SQL Scripts, SQL Server
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

16 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
      • It means that the file is residing in your system. Make sure it is in Server’s directory

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

    I am having the same problem..

    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
  • Make sure the file is available in Server’s directory

    Reply
  • Norbert Wörle
    April 5, 2020 3:56 pm

    What do you mean with Server’s directory? The data files are normally in an location where datafilee are stored. D drive as an example. But yes it’s local on the server.

    The Server is running with a domain service account. I’ve also read a lot about setting security on the temp folder or the file location. But what exactly?

    Allow the Domain service account (running the SQL Server) with full access on the XLSX file location?
    or
    Allow the actual logged on account running management Studio with full access to the temp folder of the domain service account( running the SQL Server)?

    Reply
    • The file should be available in the directory where SQL Server is installed. Otherwise you need to use the UNC path

      Reply
  • Ruchika Sharma
    January 29, 2021 2:48 pm

    I am getting cannot initialize data source, error: could not parse rfc1738 URL from string

    Reply
  • This also happens if you have a multihomed database server and the active directory dns resolves to the alternate IP address which is inaccessible.

    Reply
  • How do I find out ” C:\Users\svc_app\AppData\Local\Temp” this kind of path in my system?

    Reply
  • I’m getting the error from client connections to the server when the Excel file for the linked server is on a network share.
    The linked server works from on the server for the file on the network share.
    The linked server works for client connections if the Excel file is on the server.
    Doesn’t seem to be a permission problem because the client connection behaves the same when it is made by the same user that works on the server.
    The client connection is using Kerberos. The Engine Service user is authorised for Delegation of ldap to the Domain Controller.
    SPNs are defined.

    Reply
  • I’ve followed up with ProcessMon on the SQL Server
    When it works:
    Process: sqlsever.exe;
    Operation: CreateFile;
    Path:{the file}
    Detail: Desired Access: Generic Read/Write, Disposition: OpenIf, Options: Non-Directory File, Open No Recall, Attributes: NCI, ShareMode: Read, AllocationSize: 0, Impersonating: {the user}, OpenResult: Opened

    When it fails
    Result: ACCESS DENIED
    CreateFile makes me think it is trying to create a lock file and not being allowed.

    I cannot find anything about either the success or failure with Process Monitor on the file server.

    On the client I am running the query in SQLSMS started with a user who has full permission on both servers.
    The Engine Service user is enabled for “Impersonate a client after authentication on both servers.

    I even tried moving the file into the user’s AppData\Local folder on the file server.
    I’m beginning to think that the credentials cannot be passed on to file server when they have already been passed from the client to the SQL Server. I thought Kerberos was supposed to allow multiple passes?

    Delegation to the file server directly from the SQL server works because it is only one pass. Client passing credentials to the engine which reads the file locally is only one pass.

    Reply

Leave a Reply