Openrowset not working with network file path

Question

Openrowset not working with network file path

tommiwan

Mr or Mrs. 500

Points: 593
More actions
December 20, 2017 at 12:32 pm

#334165

Hi all,
I'm trying to get an openrowset query to work and I believe I'm running in the permission issues. I've looked around the internet extensively but haven't found a solution. The query works when referencing a file on the database server (C drive). When I change it to reference a file on a network location the statement fails. Both the caller (me) and the SQL server service account have permissions to the network location. I believe this is because I was running in to a double hop issue so I ran the statement from the SQL server itself (not my local managment studio) and suddenly it worked, and I was able to use openrowset to query a file on a network location. What I need is for the application to be able to call this statement through an SP. Can anyone help me figure out what I need to change in order to make this work? Any help is appreciated!
SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=NO;Database=\\<computername>\Share\11-16-17.xls', 'select * from [sheet1$]')

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

tommiwan - Wednesday, December 20, 2017 12:32 PM
Hi all,
I'm trying to get an openrowset query to work and I believe I'm running in the permission issues. I've looked around the internet extensively but haven't found a solution. The query works when referencing a file on the database server (C drive). When I change it to reference a file on a network location the statement fails. Both the caller (me) and the SQL server service account have permissions to the network location. I believe this is because I was running in to a double hop issue so I ran the statement from the SQL server itself (not my local managment studio) and suddenly it worked, and I was able to use openrowset to query a file on a network location. What I need is for the application to be able to call this statement through an SP. Can anyone help me figure out what I need to change in order to make this work? Any help is appreciated!
SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=NO;Database=\\<computername>\Share\11-16-17.xls', 'select * from [sheet1$]')

Try putting the statement in a stored procedure, have the stored procedure in a database owned by a sysadmin account and use Execute As Owner for the stored procedure.

Sue

Joe Torre SSChampion Points: 10248 More actions · Answer 2

It sounds like you're credentials aren't being forwarded correctly. Often tis can be resolved by creating an SPN for the server for the account SQL Server is running under.

tommiwan Mr or Mrs. 500 Points: 593 More actions · Answer 3

Thanks for the replies. Both of these are suggestions that I did not run in to when googling for an answer. I will try these and report back with what I find.

I talked to one of our sysadmins who mentioned that the SQL server service account was not set up for kerberos delegation so that might be playing a part in this.