locked
Exec SP is failing in SQL JOB RRS feed

  • Question

  • Hi,

     I am using SQL Server 2005, i am able to execute a Stored Procedure manually by running the query

    Exec R2D2.RunR2D2Engine

    This SP which is running under a specific account (RMS_AppUser). Stored Procedure has some codes to access remote servers, read / write on the same server and remote servers.

    but when i run the same Exec query in the SQL J0b, with RUN AS "RMS_AppUser", the following error is thrown.

    Executed as User: Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274)

    Kindly let me know what i have to do... Thanks in advance

    Regards,

    Fazlu

    Monday, February 11, 2013 2:41 PM

Answers

  • If you use RUN AS, this impersonation is good inside in SQL Server. But when you connect to the remote server, that SQL Server will look at the login token passed to it by Windows. And Windows does necessarily not trust any impersonation inside SQL Server. Well, it shouldn't.

    One option is to set up login-mapping for the linked server, but this requires that the remote server has SQL authentication enabled, and that the DBA agrees to create an SQL account for the purpose.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Maggie Luo Wednesday, February 20, 2013 5:19 PM
    Monday, February 11, 2013 10:38 PM

All replies

  • Hi,

    Have you checked the linked server configuration? May be the login used in linked server doesn't have enough permission. May be you can use the option "Be made using the Login's current security context" in the linked server. Also, try removing the "run as" option in the job and run it as default ( sql agent credentials).

    Hope that help.



    Cheers

    Mahesh
    Monday, February 11, 2013 3:42 PM
  • Hi,

    Yes, i have all the necessary permissions in the Link Server, when i run the same query manually the query completed successfully. 

    And to remove the RUN AS option in SQL Job, the job is created with Corporate\Administrator account. but if i take out the RUN AS option the query was failed to run due to the job is executing as Corporate\Z_DB04Admin account. i dont know why this Production environment using different account to run this SQL JOb,. this account doesnt have necesasry permissions. 

    thats why i used RUN AS option to run the query under RMS_AppUser.

    Let me know what else i have to do

    Regards,
    Fazlu

    Monday, February 11, 2013 4:33 PM
  • If you use RUN AS, this impersonation is good inside in SQL Server. But when you connect to the remote server, that SQL Server will look at the login token passed to it by Windows. And Windows does necessarily not trust any impersonation inside SQL Server. Well, it shouldn't.

    One option is to set up login-mapping for the linked server, but this requires that the remote server has SQL authentication enabled, and that the DBA agrees to create an SQL account for the purpose.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Maggie Luo Wednesday, February 20, 2013 5:19 PM
    Monday, February 11, 2013 10:38 PM
  • The easiest way to make it work is to execute job under proxy account that successfully executes SP.

    Does SQL server run under domain user? That user should have execute privilege on SP.

    Tuesday, February 12, 2013 12:12 AM