none
Permissions Issue Question mgmt studio > Server > file share

    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 05, 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 05, 2011 3:03 PM
  • gave service permission to folder .. still getting error ... when trying above query
    David Ulrich
    Wednesday, July 06, 2011 5:40 PM
  • are you still getting Operating system error code 5? What is the exact error message?

    Wednesday, July 06, 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 06, 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 06, 2011 7:21 PM
  • run this to get user that need perms

    xp_cmdshell 'whoami'

     

    Wednesday, July 06, 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 08, 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 08, 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 09, 2011 12:49 AM