Post reply

Openrowset not working with network file path

  • 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$]')

  • December 20, 2017 at 1:18 pm

    #1972984

    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

  • December 20, 2017 at 6:25 pm

    #1973026

    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.

  • December 21, 2017 at 10:59 am

    #1973133

    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.

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy