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