Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an MS Access database on a shared network location which I've added as a linked server to my SQL Server instance. I'm authenticated on the network location via the server's main Windows login.

I can run the stored procedure normally via SSMS on the server, which works perfectly. However, when I try and run the job through SQL Job Agent, it fails, I believe due to the fact that Job Agent runs under the Local Service account.

How can I allow only this one job to run under the Windows account without having to change this for all the other jobs? And should I even be doing it this way from a security standpoint?

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

Note: SQL Server Agent itself does not access the share

It depends on how you have mapped linked server security between local and remote credentials. SQL Server Agent running as local system will connect to SQL Server, which then accesses the share based on linked server credentials.

SUSER_SNAME will be different of course in this case to when you run it.

Options:

  • add a linked server login mapping for "local system"
  • configure all local credentials to use one remote login
  • look at SQL Server Agent proxy accounts for the job step (changes SUSER_SNAME)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.