Post reply

Dynamic SQL referencing a linked server returns "Access to the remote server is denied because the current security context is not trusted."

  • October 10, 2012 at 9:37 am

    #263730

    I've got a peice of dynamic sql which passes in the Server and Database name to create a fully qualified name in the query. It's designed to sit behind a report but i'm testing it in a query window.

    I execute as myself (Sysadmin on the server I'm executing from and the one I'm referencing) and it works perfectly.

    When I run it under the context of "NT Authority\NetworkService" which is the account SSRS is running under then it returns

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

    I've tried to configure my linked server to use a specifc account to make connections (be made using a specifc security context) , I've even used my own login but I still get the same error.

    i've set TRUSTWORTHY ON on both databases but no change.

    The calling server is SQL 2008 the target is 2012, but as it works under my own account I've ruled that out.

    A point in the right direction would be very helpful thanks

    Giles

  • October 10, 2012 at 10:49 am

    #1547751

    Not sure this will help but I seem to remember something similar. Turned out the problem was the database owner, setting the owner to 'sa' using sp_changedbowner fixed it.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • October 12, 2012 at 6:47 am

    #1548601

    nah, sorry changing the DB owner didn't work for me.

  • October 12, 2012 at 2:33 pm

    #1548838

    Maybe "NT Authority\NetworkService" does not have windows privilege that allows impersonation? Privilege "Impersonate a client after authentication".

    Start->Run-> secpol.msc

    Find it under Local Policies -> User Rights Assignment.

    You can first try with another account that already has that privilege.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

Viewing 4 posts - 1 through 3 (of 3 total)

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