Problem: Having a SQL Agent Job, which executes a Stored prcedure to connect to a remote SQL server via linked server and extract/manupulate the data.
The SP runs successfully in SSMS but get failed, if run through an agent Job.
NOTE: A Windows a/c is used here to connect to remote server and the a/c is having dbo permission in both source and remote server databases. Both the servers are on same domain.
Resolution: Steps :
a. Make sure that the a/c used in linked server/Job having necessary permissions on both source and remote server databases.
b. Remove the run as "Login a/c" for the Job step
c. Remove the Login a/c, if it defined under "Local Server Login to Remote Server Login Mappings" to impersonate.
d. In Linked Server properties, Choose "Be made using the Login's current security context",if the a/c having permissions on remote server and is used to run the SQL Agent Service else choose "Be made using this security context" and define the Remote Login and password.
**If it did not work you can try to see the new TRUSTWORTHY property is enabled. By default this is set to false on all user databases. SQL Server tighten up the link server security.
Here is the syntax
ALTER DATABASE < DATABASENAME>
SET TRUSTWORTHY ON
The SP runs successfully in SSMS but get failed, if run through an agent Job.
NOTE: A Windows a/c is used here to connect to remote server and the a/c is having dbo permission in both source and remote server databases. Both the servers are on same domain.
Resolution: Steps :
a. Make sure that the a/c used in linked server/Job having necessary permissions on both source and remote server databases.
b. Remove the run as "Login a/c" for the Job step
c. Remove the Login a/c, if it defined under "Local Server Login to Remote Server Login Mappings" to impersonate.
d. In Linked Server properties, Choose "Be made using the Login's current security context",if the a/c having permissions on remote server and is used to run the SQL Agent Service else choose "Be made using this security context" and define the Remote Login and password.
**If it did not work you can try to see the new TRUSTWORTHY property is enabled. By default this is set to false on all user databases. SQL Server tighten up the link server security.
Here is the syntax
ALTER DATABASE < DATABASENAME>
SET TRUSTWORTHY ON
Comments