SELECT * FROM OPENROWSET Excel file on network drive

1

Hi,
I have an issue with

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=S:\SFIservicing\DataProduction\Development\UNITMS file - Copy.xlsx', 'Select * from [CurrentMonthCXR$]')

, I get the following .
Msg 7399, Level 16, State 1, Line 1
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 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
If file is located on the C: drive, it works fine.

I ran the following query to see if the server can connect to the Excel file and it appears yes.

 exec master..xp_cmdshell 'dir "S:\SFIservicing\DataProduction\Development\UNITMS file - Copy.xlsx"'
2

A few things to check, 1st make sure you run these two commands

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

The other very important thing is make sure the path "S:\SFIservicing\DataProduction\Development\UNITMS file - Copy.xlsx" is accessible on the SQL SERVER, not the server you are running the query from

and last ensure the file is not open. YOu must close the xlsx file before you run the query.

3

The problem is likely the Kerberos Double-hop "feature". If you RDC into the server, you're query will work fine. The reason why it doesn't work the way it is because SQL Server loses authentication when you do something like this from a connection. You need to write the query so that it works and put it into a stored procedure with an EXECUTE AS OWNER and the owner of the database needs to be "SA" even if you have it disabled.

And, yes, it IS a "feature" that makes life more difficult for would be hackers. :wink:

1 Like
4

Vinnie881:
Thanks Vinnie881, I had already did everything you mentioned.

JeffModen:
I tried running it under the Network/Service account and it worked fine so what you are proposing makes sense.
I run this in SQL 2014 on Windows Server 2012 R2. I could realize there is more security in that version even with IIS 8.5 for web hosting....

Thanks all!