SQL Linked Server and SQL Server Agent

When using SQL Server Agent to do a job that has a view or other that connects to a remote server then you may get the following issues:

Error “Linked servers cannot be used under impersonation without a mapping for the impersonated login”

Solution 1:

  1. Create a login on the end server as:
    USE [master]
    GO
    CREATE LOGIN [NT SERVICE\SQLSERVERAGENT] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[British]
    GO
  2. Then give this login the permissions on the objects required.
  3. Back on the host right-click on the remote server > Properties > Security >
  4. Add “Local server login to remote server login mappings:”
    Add “NT SERVICE\SQLSERVERAGENT” Impersonate = ticked on – No remote user or password
    Suggest use “Be made using the login’s current security context”

Solution 2:

  1. Create a new SQL server login on the end server and give it the required permissions
  2. Back on the host right-click on the remote server > Properties > Security >
  3. Add “Local server login to remote server login mappings:”
    Add “NT SERVICE\SQLSERVERAGENT” Impersonate = ticked off – Remote user = your new user, Password = new password
    Suggest use “Be made using the login’s current security context”

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: