locked
Linked Server Permissions running from SQL Agent RRS feed

  • Question

  • Hi

     

    Hoping someone can help.

     

    On ServerA I have a stored proc which makes a call via a linked server to ServerB. The linked server connection is "to be made using the logins current security context. A SQL login (LoginC) exists on both ServerA and ServerB with the same password with the correct permissions to be able to run the stored procedure. I have proven this by logging on to both servers with the same login and password.  I have also connected to SSMS as LoginC on ServerA, then run the stored procedure successfully.

     

    If I set up the stored proc to run as a SQL Agent job specifying the owner to be LoginC, the job fails. The reason upon inspection is a login failure (State 8) to ServerB - which is telling me that there is a password mismatch. Both SQL Agent accounts on ServerA and ServerB are running as NT domain logins.

     

    If I then edit the linked server properties and add a Local server login to remote server login mapping, specifying LoginC's details and then re-run the job it will work successfully.

     

    Why should it work when I do a direct mapping of the login to the same remote login and password? Surely this is what the "Be made using the logins current security context" is for?

     

    Other things I have checked are the collation settings are the same on both servers, in case there is a problem mapping case sensitivity, I've also ensured that the login has been refreshed in msdb.syscachedcredentials. Any ideas would be welcome.

     

    Thanks

    Martin

     

    Wednesday, November 5, 2008 12:01 PM

All replies

  • does SQL Agent account from server B has permision on server A?

    Did you map a "domain group" to a SQL login? you cannot do this.

    The user who execute a job is a SQL Agent account,so, make it a job owner.

    Wednesday, November 5, 2008 4:27 PM
  •  

    Yes it does have permission - as I said if I add the direct mapping permissions to the linked server then the job will run successfully.

     

    No - it is not possible to map a domain group to a SQL login.

     

    The job owner is the SQL login that has permissions. It does not seem to be a SQL Agent permissions issue, it is more that when the proc is run from the SQL Agent it does not seem to pass through the linked server credentials correctly, unless you explicitly map them.

    Thursday, November 6, 2008 11:37 AM
  • Did you try to login to SSMS with the same NT domain login that is used for the SQL Agent account and then run the SP ?

     

    This may help understanding what is going on.

     

    Thanks,

     

    Gil

    Friday, November 7, 2008 5:48 PM
  • Hi Gil

     

    Sorry for the delay in getting back to you. I did not have time to mess about with this for too long - so in the end just created the linked server and did the specific login mapping. Just seems like another of those SQL 2005 oddities.

     

    Thanks

    Martin

     

    Tuesday, December 9, 2008 4:01 PM
  • Please read article:

    http://support.microsoft.com/kb/811031

     

    When you run a SQL Server Agent job that is not owned by a system administrator account, SQL Server Agent runs SETUSER to impersonate the owner of the job.

    If the job uses linked servers, the job may fail because remote access is not allowed after the execution of SETUSER


    manish
    Wednesday, September 21, 2011 4:07 PM