SQL Linked Server and SQL Server Agent
31-Jul-1313 Leave a comment
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:
- 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 - Then give this login the permissions on the objects required.
- Back on the host right-click on the remote server > Properties > Security >
- 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:
- Create a new SQL server login on the end server and give it the required permissions
- Back on the host right-click on the remote server > Properties > Security >
- 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”