Post reply

Access to the remote server is denied because the current security context is not trusted.

  • February 10, 2014 at 4:35 am

    #285335

    Hi,

    We've got 3 SQL Servers all SQL Server 2012.

    We have a Master server and 2 production servers (let's call them A and B). The Master has linked servers defined for both A and B in an identical way.

    A and B both have the same databases and each has a Stored Proc that gets called by the Master server.

    An SQL Agent job executes the stored proc on both A and B.

    It works fine against A but not B.

    If we execute the stored proc in a query window under our windows account (domain\owner) it works, if we use SetUser to pretend to be the SQL Server Agent account (let's call it domain\agent) A works and B doesn't.

    Executing against B returns the error "Access to the remote server is denied because the current security context is not trusted."

    domain\agent is a sysadmin on both Server A and B.

    Trustworthy is set to ON in both the Master Server Database it's executing from and the remote databases on both A and B.

    All 3 databases have the same owner (let's call it domain\owner).

    Now I'm stuck so any help or direction would be greatly appreciated.

    Regards

    Giles

  • February 10, 2014 at 6:55 am

    #1687518

    I still don't understand why when executing the stored proc under the guise of domain\agent isn't working against Server B but setting the owner of the Job to be domain\owner has allowed the job to execute successfully against both Server A and Server B.

  • February 11, 2014 at 1:59 pm

    #1688114

    For this can you first check the security context of the both the servers and check the test connection .

  • February 11, 2014 at 2:46 pm

    #1688125

    Giles,

    Are you the Network Sysadmin with access to the firewalls and the like? This is typically a domain/server level setting. I don't know the specifics offhand on fixing it but I'm just wondering if you're fighting the wrong fight and need to get someone else at your firm involved.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • February 13, 2014 at 10:31 am

    #1688853

    Thanks for your interest and ideas.

    sqldba725, We were connecting "using the logins current security context".

    The Master and Server A both use Windows Authentication and Server B is in Mixed Mode, do you think that would make a difference?

    out of interest we created an SQL Login on Server B and used that in the Security Context from Server A but it still wasn't Trusted.

    Evil Kraig, we disabled firewall on all machines concerned and it made no difference.

    Starting to go bald over this.

  • February 14, 2014 at 2:35 am

    #1689001

    Can you check the SQL Server Agent Proxy settings on both servers A and B?

    http://technet.microsoft.com/en-us/library/ms190926.aspx

    Another thought is to check the Windows local security policy, for "Log on as a batch job" and "Log on as a service". Is there a difference between servers A and B?

    Check the Activity Monitor on Server B, when you execute the Job, to see exactly which Login is being used.

    Andy

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply