SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Openrowset not working with network file path


Openrowset not working with network file path

Author
Message
tommiwan
tommiwan
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 97
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$]')

Sue_H
Sue_H
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62739 Visits: 13325
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
Joe Torre
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6186 Visits: 1141
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
tommiwan
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 97
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search