I am trying to use OPENDATASOURCE to read an Excel file in Sql Server management Studio.
SELECT top 2 * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:SSISExcel.xlsx;Extended Properties="EXCEL 12.0;IMEX=1;HDR=NO"')...[Sheet1$];
SELECT top 2 * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=\app1App_FilesSSISExcel.xlsx;Extended Properties="EXCEL 12.0;IMEX=1;HDR=NO"')...[Sheet1$];
If I run it in SSMS
- From my computer
- The Excel file is on a network location
- Using an Active Directory user (can access the file through explorer, the same user as the SQL Server Service Account)
I get the Error:
OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “The Microsoft Access database engine cannot open or write to the file ‘app1App_FilesSSISExcel.xlsx’. It is already opened exclusively by another user, or you need permission to view and write its data.”.
Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.
If I run it with any one (or more) of the following changes it works (see chart below):
- From the server instead of my machine
- Using an SQL User instead of an Active Directory User
- Using a local (to the server) file instead of a network one
Some articles I googled seem to indicate that it might be a Kerberos Double Hop issue. I tried working with the network team and now when I run select auth_scheme from sys.dm_exec_connections where session_id=@@spid from my local machine I get
| auth_scheme |
|---|
| KERBEROS |
but it still doesn’t work.
If I run it from the Server (where it does work) I get
| auth_scheme |
|---|
| NTLM |
It is possible that the network team might not have configured something correctly when trying to setup the fix from the articles about the Kerberos Double Hop issue, but I am not sure how to check for that or even if the issue is with Kerberos at all.
6