locked
access network share via openrowset RRS feed

  • Question

  • I am trying to access a blob on a network share but keep getting 

    Msg 4861, Level 16, State 1, Line 3
    Cannot bulk load because the file "\\server\share\1.jpg" could not be opened. Operating system error code 5(failed to retrieve text for this error. Reason: 15105).

    the command is: 

    select * from openrowset ( 
               bulk '\\server\share\1.jpg' 
               ,SINGLE_BLOB) x 

    I can access the file from windows. I have given permissions to the share for the domain user running sqlserver 

    obviously a delegation error. can a username/password be specified in the sp or openrowset? I just want access through sqlserver. I dont care how. would an assembly bypass this ? 

    thanx


    ???

    Thursday, October 10, 2019 1:04 PM

Answers

  • powershell worked but the copy and the running of powershell from an sp was slow. I had it rewritten as an assembly and all worked fine

    ???

    • Marked as answer by berli Thursday, October 24, 2019 12:14 PM
    Thursday, October 24, 2019 12:14 PM

All replies

  • Operating system error code 5

    OS error 5 = Access denied. You also have to grant NTFS permissions, not only on the share.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, October 10, 2019 1:31 PM
  • the user running sql server is a network users and can access the files not via the openrowset command

    as I said, it seems to be a delegation error


    ???


    • Edited by berli Thursday, October 10, 2019 3:34 PM
    Thursday, October 10, 2019 3:26 PM
  • as I said, it seems to be a delefation error

    Yes, this sounds like a double-hop issue. That is the Kerberos ticket is  not popular on the fileshare.

    Workarounds in SQL Server are:
    1) Run from SSMS on the same machine as SQL Server.
    2) Log in with an SQL login and make sure that the service account for SQL Server has access to the file share.

    But a better solution is to talk to your Windows admin about the Kerberos configuration.

    When you look at your session in sys.dm_exec_connections, what do you see in the column auth_scheme?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, October 10, 2019 9:36 PM
  • Hi berli,

    >> I can access the file from windows. I have given permissions to the share for the domain user running sqlserver  and the user running sql server is a network users and can access the files not via the openrowset command

    I want to know that your SQL server service is running under which account? For the security considerations using OPENROWSET(BULK...), please refer to this MS official document to get more information. If I misunderstood your description, please let me know.

    Best regards,
    Cathy  

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, October 11, 2019 8:54 AM
  • windows admin came to me for help.  the share is not on a windows device but rather a NAS storage device. as I said, sql runs under a windows account. that windows account can access the network share and read the files. via an sql login, I get the error

    I enabled xp_cmdshell and ran a powershell script to copy the file to c:\temp from within sql and then sql can open the file from c:\temp using the same openrowset command. it will not work via a network url \\server\share\...



    ???

    Friday, October 11, 2019 11:16 AM
  • powershell worked but the copy and the running of powershell from an sp was slow. I had it rewritten as an assembly and all worked fine

    ???

    • Marked as answer by berli Thursday, October 24, 2019 12:14 PM
    Thursday, October 24, 2019 12:14 PM