Parmissions issue running agent and bulk insert from network location

Question

Parmissions issue running agent and bulk insert from network location

bswhipp

Ten Centuries

Points: 1117
More actions
June 26, 2017 at 6:14 am

#338405

I have changed the service account to a domain account that has privileges to the network location. If I log on to SSMS as sa and run the stored procedure it works fine. If I try to run it in an agent it fails with Access denied error code 5.
Been racking my brain to figure this out.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Bulk Insert uses the privs on the remote system as whomever or whatever is running it. You need for the SQL Agent service login to be able to see the remote folder.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bswhipp Ten Centuries Points: 1117 More actions · Answer 2

Duh, I didn't even think of that. So I need to change the SQL Agent Service logon also to the Domain logon. Wow, I feel so stupid.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

bswhipp - Monday, June 26, 2017 6:37 AM
Duh, I didn't even think of that. So I need to change the SQL Agent Service logon also to the Domain logon. Wow, I feel so stupid.

Actually and quite ironically, you can reduce the footprint by using xp_CmdShell to call BCP or call SQLCmd to call Bulk Insert so that the agent doesn't need such high privs. Used properly, either method has some serious benefits and, used properly, xp_CmdShell does NOT increase any security risks.

If you need more details on any of that, let me know.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63428 More actions · Answer 4

That sounds like an article in the making...
How people do it wrong.... and then how to fix it/do it correctly.