Search code examples
sql-servermergesql-server-agent

Merge from 2 different servers: SQL Server Agent Job - Authentication Error

I have a SQL statement that merges 2 databases together, from 2 different servers (SQL Server 2012 for both).
The two servers are linked, so when executing the statement in a query window from Management Studio with the SA account it works perfectly.

However, I need to automatize this. I created a Job in the SQL Server Agent, and I keep getting an authentication error. This is because the user that is executing the SQL Server Agent service doesn't have the SA rights on both servers.

This is the error:

The OLE DB provider 'providerName' for the connection server 'serverName' reported an error. Authentication failed.

I tried setting a superadmin user from the Active Directory with all rights on both servers to execute the SQL Server Agent, but I still get the error.

Is there any way to fix this?

Thanks

Solution

  • You can store your linked server credentials with your linked server definition (forbid my picture editing skills ;-)):

    enter image description here

    Alternatively - and much more preferabele from security point of view - setup same account on both servers (you can then limit the account privileges as you need) and use the third option ('Be made using the login's current security context')