Query Linked Access database on network share via SQL Job Agent

Question

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?

Answer 1

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)