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

Answer 1

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.

Answer 2

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.

 

Answer 3

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: [e-mail]

Answer 4

gave service permission to folder .. still getting error ... when trying above query

---

David Ulrich

Answer 5

are you still getting Operating system error code 5? What is the exact error message?

Answer 6

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

Answer 7

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. 

Answer 8

run this to get user that need perms

xp_cmdshell 'whoami'

 

Answer 9

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

Answer 10

>>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)

Answer 11

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