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