locked
Permissions Issue Question mgmt studio > Server > file share RRS feed

  • Question

  • getting this error when attempting to run a query ..

    I get the error below at this point. 

    Msg 4861, Level 16, State 1, Line 1

    Cannot bulk load because the file "\\x\x\x\x\x.txt" could not be opened. Operating system error code 5(Access is denied.).

    can figure out exactly what permissions are missing....

    Thanks for the help


    David Ulrich
    Thursday, June 30, 2011 1:16 PM

All replies

  • Normally Operating System Error 5 means insufficient permission.

    To fix the issue make sure

    Account under which **SQL Server service**  is running has Read/Write access to the folder where text files (.txt) are kept. If it is a network location you may have to run your SQL server service under a domain account (with enough permission to access that network share) instead of local account.



    Thursday, June 30, 2011 1:22 PM
  • Yes you are  right , database services should be configure with Administraor a/c  or Doamin a/c with full privileges.

    I agree with above  Chirag Shah answer.

     

    Tuesday, July 5, 2011 1:11 PM
  • David,

     

    You should give permission to the sql server service user on windows scope.


    Fabrizzio A. Caputo
    Certificações: Oracle OCA 11g, MCTS SQL Server 2008 Implementation and Maintenance
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com
    Tuesday, July 5, 2011 3:03 PM
  • gave service permission to folder .. still getting error ... when trying above query
    David Ulrich
    Wednesday, July 6, 2011 5:40 PM
  • are you still getting Operating system error code 5? What is the exact error message?

    Wednesday, July 6, 2011 6:14 PM
  • Msg 4861, Level 16, State 1, Procedure sp_Hourly, Line 8 Cannot bulk load because the file "\\xxxxx\xxxxxxx\xxxxxx\xxxxxx.txt" could not be opened. Operating system error code 5(Access is denied.).
    David Ulrich
    Wednesday, July 6, 2011 6:52 PM
  • David,

    Operating System Error 5 is a Windows Operating System error that means access is denied.

    Are you running this proc as SQL Server Agent Job? If yes then make sure credentials under which Job is running has Read/Write/Modify permission on that folder.

    in other words,

    Whatever security context your Stored procedure is running needs access to that folder. 



    Wednesday, July 6, 2011 7:21 PM
  • run this to get user that need perms

    xp_cmdshell 'whoami'

     

    Wednesday, July 6, 2011 7:32 PM
  • ran query from desktop came back with SQL service account as suspected ... going to give sql service account full permissions to folder and test

     

    the SQL service account should not need permission on the local desktop running the query correct?  just passing from the server to the file share ...


    David Ulrich
    Friday, July 8, 2011 12:30 PM
  • >>just passing from the server to the file share ...

    Yes, need permission on the file share (make sure  sql service runs under a domain account, and you can replicate it by login to windows using the service account credentials and able to access the file share and files)

    Friday, July 8, 2011 1:52 PM
  • If you are concerned with the granting of extra permissions to the service accounts, the other approach that you could do is create a domain user account [DomName]\[ExternalAccountName] and grant this account the full permissions to the operating system folder to be able to read the files, grant this account the appropriate minimum level of permissions inside of sql server to perform what the job requires.

    Then you could create a new credential (found under the security tree), then create a new proxy account.

    Once you have this setup you can then set the agent job to run under your newly created proxy account that has the appropriate set of minimum permissions.

    I hope this helps


    Warwick Rudd MCT MCITP SQL Server 2008 Admin www.jnritoptions.com/Blog.aspx
    Saturday, July 9, 2011 12:49 AM