July 23, 2007 at 4:01 am
Hi,
I have some T-SQL that uses BCP to import data from a txt file into a table. When I run it from Query analyser all is fine, however, when I create a Job to run the same code I get the following error message:
Executed as user: NT AUTHORITY\SYSTEM. Cannot bulk load because the file "\\londfs01\Data\Data01.txt" could not be opened. Operating system error code 5(Access is denied.). [SQLSTATE 42000] (Error 4861). The step failed.
I've been told by the Windows admins that they can't give access to the newtowrk folder to the user "NT AUTHORITY\SYSTEM"
What do I need to do? All help much appreciated.
Cheers.
July 23, 2007 at 5:24 am
What account does your sql server agent service run. It shoule run under a network service or domain account to access network resources.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 23, 2007 at 7:03 am
Hi,
How do I find this info?
July 23, 2007 at 7:09 am
Just to ask the same question in a different (more leading) way, how can i create a windows user on SQL server and use that user to execute the Job? Obviously, I could then give that user read permissions on the folder in question. Also, my personal Windows login account has read access to this folder... is it possible for me use my Windows account to run this Job?
July 23, 2007 at 9:25 am
The SQL Server Agent Service is running under "Local System", however when I change this to "Network Service" I get the following in the Logs:
Message
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. [CLIENT: <local machine>]
Any suggestions?
Thanks again!
July 23, 2007 at 10:25 am
Okay, I've solved it. Here's what I did:
A1. Go to Services in Administrative tools.
A2. Open "SQL Server (MSSQLSERVER)"
A3. On the "Log On" tab, check the "This account" radio button.
A4. Enter the Windows account details of an account that has permissions to the folder you want to access.
B1. Goto SQL Server Agent (MSSQLSERVER) in SQL Configuration Manager
B2. On the "Log On" tab, check the "This account" radio button.
B3. Enter the Windows account details of an account that has permissions to the folder you want to access.
Re-run your job!
Thanks to those who replied. Much appreciated.
Some useful links that helped:
July 23, 2007 at 10:26 am
one more thing (yeah, like colombo)
whilst what i've done works (for me), i'm guessing it's not the best approach, so any recommendations/guidelines appreciated.
Ta
July 24, 2007 at 7:33 am
Do not run your sql services with local account unless there is nothing that you acccess on the network. mostly prepfer a domain account with needed permissions to run the sql service account. but make sure that you give necessary permissions
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply