So here's my issue.
I'm executing the following queries. The first is the one I really need as I want to access files on a network location. The second is shown to hopefully help narrow the issue down.
The error I get is
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
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)".
Note: In examples below I have replaced real file, server and folder names with myXXXX. However there is nothing in the names that will cause any issues (no spaces, special characters etc)
1. Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\myFileServer\MyFolder\myFileName.xlsx;HDR=YES', 'SELECT * FROM [mySheetName$]')
2. Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\MyFolder\myFileName.xlsx;HDR=YES', 'SELECT * FROM [mySheetName$]')
First, to get some context. For query 2 I copied myFileName.xlsx to the C: drive of the Windows Server that hosts the SQL Server 2008 instance.
Query 2: Accessing the file that is stored locally on the SQL Server box:
a. Run from SSMS connected to MySQLServer on SQl Server Box directly: Works
b. Run from SSMS connected to MySQLServer on my Windows 10 PC: Works
Both above work whether SSMS is run as my domain user account of the domain admin account
Query 1:
c. Run from SSMS connected to MySQLServer on SQl Server Box directly: Works
d. Run from SSMS connected to MySQLServer on my Windows 10 PC: FAILS
Even logged into the Windows Server and running SSMS as my local account works.
Next step was to check events on the SQL host box (BTW: The SQL instance is running directly on the host OS, there is no VM involved anywhere) I ran procmon on the SQL host box and spotted 4 errors each time I run scenario d. above.
Process = sqlservr.exe
Operation = CreateFile
Path = \\myFileServer\MyFolder\myFileName.xlsx
Result = ACCESS DENIED
Details = Desired Access: Read Attributes, Disposition: Open, Options: Open For Backup, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, Impersonating: myDomainName\administrator
Checking the logs running scenario c. above gives
Process = sqlservr.exe
Operation = CreateFile
Path = \\myFileServer\MyFolder\myFileName.xlsx
Result = SUCCESS
Details = Desired Access: Read Attributes, Disposition: Open, Options: Open For Backup, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, Impersonating: myDomainName\administrator, OpenResult: Opened
I've checked that the folder and file have full access from the domain admin and domain user accounts and also the service account that the SQL instance runs on. I've also check that the same accounts have access to C:\temp on the SQL host box.
I'm not really sure what to try next. I've been importing data like this for years on the same server and I'm fairly sure I've managed to get this working OK in the past but all existing scripts I found pointed to the local drive.
One more thing: I also tried to map a drive to the folder but got the same errors.
I'm hoping somebody will be able to point me in th right direction.
Thanks for reading!
The remarks section, in the OPENROWSET documentation, highlights a few points on remote OLEDB:
OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behaviour does not allow for ad hoc access.
When accessing remote OLE DB data sources, the login identity of trusted connections is not automatically delegated from the server on which the client is connected to the server that is being queried. Authentication delegation must be configured.
Suspect that non-local files are falling foul of this restriction.