OpenRowSet works locally but not over networked drives

Question

OpenRowSet works locally but not over networked drives

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

Brian Stover - Monday, July 17, 2017 5:33 PM
One additional step I tried:
I'm trying to access an Excel 2010 file from network share using a UNC path. This query actually works against the network share when I'm logged onto the SQL server (via RDP with my credentials), but fails from SSMS on my machine (both on the same domain). The SQL Server is 2012 Standard on Windows 2008 R2 (not sure if that matters)
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\<Fully qualified server name>\<path to file>\Book1.xlsx','select * from [Sheet1$]')
When ran from SSMS 2016 on my machine (Windows 10) I get:
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)".
I've tried logging in as the AD account that the SQL service uses and confirmed that it can see and read files in the target directory. I've tried creating a new dummy excel file. Same problem. TSQL works when ran on the server, fails when ran locally on my machine from SSMS.

The SQL Server login on your local server doesn't have the same privs that your SQL Server does... and it shouldn't.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 2

"linked server "(null)" - this looks like a double hop issue. From your desktop to the first server with the openrowset code and then to the target. Delegation in Kerberos needs to be looked at. Kerberos is pretty much a mystery to me so someone will need to help you with it.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 3

MG-148046 - Tuesday, July 18, 2017 6:40 AM
"linked server "(null)" - this looks like a double hop issue. From your desktop to the first server with the openrowset code and then to the target. Delegation in Kerberos needs to be looked at. Kerberos is pretty much a mystery to me so someone will need to help you with it.

Agreed. And I don't think of it as a fault. I treat it like a security feature. If you create a stored procedure and let the SQL Agent run it, it should do just fine. If you need humans to be able to run it, make sure the owner of the database (should normally be "sa") has sysadmin privs and add WITH EXECUTE AS OWNER in the proc. There's no need to overcome the Kerberos double hop feature.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brian Stover Mr or Mrs. 500 Points: 511 More actions · Answer 4

Jeff Moden - Tuesday, July 18, 2017 7:36 AM
Agreed. And I don't think of it as a fault. I treat it like a security feature. If you create a stored procedure and let the SQL Agent run it, it should do just fine. If you need humans to be able to run it, make sure the owner of the database (should normally be "sa") has sysadmin privs and add WITH EXECUTE AS OWNER in the proc. There's no need to overcome the Kerberos double hop feature.

Thanks Guys, Setting up a stored procedure and running the stored procedure from a Job worked. I would ultimately like this to be run by a users. I tried switching the DB owner to a sql login with sysadmin privileges and adding WITH EXECUTE AS OWNER to the proc. No luck. I still get this error:

Msg 7399, Level 16, State 1, Procedure OPENROWSET_TEST, Line 5 [Batch Start Line 0]
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, Procedure OPENROWSET_TEST, Line 5 [Batch Start Line 0]
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I spoke with my lead Network Engineer about this. He did a did of research and adding a bit more detail to the double hop issue. To make this work, I was told that delegation would need to be setup and enabled for the SQL server. This would allow the SQL Service service account to authenticate to the resources outside of the server itself (I'm probably butchering the explanation).

Thanks for the tip on the double hop. That was the hint I needed to figure this out.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 5

You're welcome. Glad you are on the right track now.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.