locked
Linked Server in SQL Server Agent Job gives "Access to the remote server is denied because the current security context is not trusted" RRS feed

  • Question

  • I have a MS CRM database where I want to execute a stored procedure every night that joins out to a remote SQL server in the same domain.  Because of the way security works for Dynamics CRM I HAVE to be logged on as a real user, i.e. I can't run this as NT AUTHORITY\SYSTEM or any other built in user.  So my agent job is configured with a user defined in the "Run As User" dialog.

    My Linked Server is configured with no local server logins to remote server logins mapped; the connections are all forced to connect as SQL Login "sa" which has sysadmin rights on the remote server.

    During the day I can execute the stored procedure and it works fine.  The agent job will always fail with the error mentioned in the title of this post.

    Firstly, I am aware that there probably isn't any answer to this and it is just something that doesn't work properly in SQL Server so I will have to live with it...  If anyone has a workaround or suggestions for another approach then this would be gratefully received.

    Everything I have read about Linked Servers being used in SQL Agent Jobs can be paraprashed as: "they don't work properly - the workaround is to set NT AUTHORITY\SYSTEM to be impersonated.".  Well I don't have this option; although this does work, it will always return no results as the MS CRM security sees the user has no priviliges and will filter the built in Views so they can't see any data.  I could try going to the base tables, but this is highly unsatisfactory and would quickly lead to unmaintainable code.

    To my mind this "should" be quite simple:

    - I am running a job as SQL Agent - it says to run the job as a specific user so I run it as that user;
    - the user has rights to get the data on the local server so that part works;
    - on the remote server I use a SQL login (say use the "sa" account) and so it connects and that part also works;
    - end result my stored procedure works when run interactively and also when run in batch mode as a SQL Job.

    Is this really broken or is there some solution out there that nobody has ever posted?

    Wednesday, June 15, 2011 4:42 PM

All replies

  • Hi Diksta,

    According to the error message, you may need to enable the trustworthy property for the database to control access to resources outside the scope of the source database. The syntax is below:

    USE <databasename>;
    ALTER DATABASE <databasename> SET TRUSTWORTHY ON;
    GO
    
    

    Additionally, the database owner of the local database which the local procedure storage belonged to should be granted the AUTHENTICATE SERVER if it is not a member of  sysadmin role. The syntax is below:

    USE master;
    GRANT AUTHENTICATE SERVER TO <Loginname> WITH GRANT OPTION;
    GO
    

    I find a thread addressing the similar issue in detail: http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/512991fd-af88-4a87-8b3e-39e0e346040e/.

    Hope this helps. 


    Best Regards,
    Stephanie Lv


    Thursday, June 16, 2011 2:41 AM
  • Hello and thanks for the reply.  I have TRUSTWORTHY set, this is a SYSADMIN account, etc.  But I found something that I think is causing the problem from the link you attached...

    When I pick the "Run As User" I see a list of what looks like SQL Server accounts mixed with Windows Logins, i.e. "<domain>\<userid>" and the latter is what is currently selected.   I have a nasty feeling this has something to do with Windows Principals versus SQL Server Principals.

    Anyway, I have pretty much given up on getting this to work through a linked sevrer so I am going to pull the remote table across to the same database in an earlier step and hope this works!

    Thursday, June 16, 2011 4:53 PM