Linked Server now fails.. even for high rights

  • hi,

    I set up a linked server from a 2005 box to 2008r2.

    security is set to "Be made using the logins current security context"

    and every thing else blank/left to defaults.

    It now doesn't work

    The error for a test connection is:

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

    I thought with the settings that its on, it should use the current users (ie mine for the test) the account that the user uses (sql account) has been created on the second server with public server role and db_datareader , public on the db they need connection to.

    I tried setting up an identical linked server from my local 2008r2 to a 2008r2 and it worked fine any ideas?

    Im local admin and sysadmin on all boxes.

  • see this link:

    [font="Times New Roman"][/font]
    DBA - SQL Server 2008

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • thanks for the link!

    Would it still be applicable if i was originally able to query between them?

  • maybe not!!!!

    this hotfix is most specific to solve this issue!!!

    [font="Times New Roman"][/font]
    DBA - SQL Server 2008

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • This is most likely a Kerberos issue.

    You authenticate from your PC to Server1, and try to use integrated security through a linked server to Server2. This is the common "double hop" problem. My guess is that the account running SQL Server on Server1 does not have permission to delegate your Kerberos ticket to Server2. This is something that needs to be set up in Active Directory (on the account running SQL Server on Server1).

  • (7/5/2012)

    maybe not!!!!

    this hotfix is most specific to solve this issue!!!

    I don't think Microsoft fixed this issue with the hotfix. We had a SQL2005 with SP2 still having this issue.

    After we created a SPN for that box, the issue was gone.

  • Hi all thanks for replies..

    So a bit more digging. after the suggestions about kerberos and where im connecting. I decided to remote onto the machine (2005 box) When i tried to connect to the linked server then i didnt get errors and got in.. doing something like:

    select top 10 *

    from server.database.schema.table

    If i try using execute as and using the account the users use its failing

    EXECUTE AS LOGIN = 'SQLUserAccount'


    select top 10 *

    from server.database.schema.table

    I get:

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'SQLUserAccount'.

    The same user account on the linked server exsists and with db_dataread to the db that it needs access to.

    looking @

    Ive tried changing the security settings to By using this security context and added the user 'SQLUserAccount'

    That got the error:

    Msg 7416, Level 16, State 2, Line 1

    Access to the remote server is denied because no login-mapping exists

    I added the 'SQLUserAccount to the Security -> Local server Login to remote server login mappings. And put the same account in the Remote User with the password.

    And now it seems to work but of course only if i use execute as login = SQLUserAccount.

    Could anyone confirm that the EXECUTE AS example above is a valid test for me.. very much second guessing myself now.. I have a feeling i know why it didnt work for them now but....

  • You can't use "Be made using the login's current security context" if your are authenticating on Server1 with a SQL Server login. That can only be used if you're using an AD account.

    For SQL logins you need to set up mapping between the local and remote login.

    So, are you using SQL logins or AD logins?

    If you want to use AD logins, then both servers need to be registered with the correct SPN for the account running the SQL Servers. You also need to set up delegation for the account running SQL Server on Server1 to allow it to delegate to Server2.

    To check if the SPN's are registered correctly you run "setspn -L MyDomain\ServiceAccount" where Mydomain and ServiceAccount is the domain and account name for the accounts running SQL Server on your to servers (They do no need to be the same).

    Setspn will list all SPN's registered for an account. Make sure that MSSQLSvc/<ServerName FQDN> is in the list.

    If not, you need to register it with "setspn -A MSSQLSvc/<FQDN>:1433 Domain\Account" and "setspn -A MSSQLSvc/<FQDN> Domain\Account" for both servers.

    The next step is to allow delegation from Server1 to Server2.

    Open Active Directory Users And Computers. Open the account running Server1 and go to the Delegation tab.

    Select "Trust this user for delegation to specific services only" and "Use Kerberos only".

    Click Add, and search for the account running SQL Server on Server2. Select both MSSQLSvc/<Server1 FQDN> and MSSQLSvc/<Server1 FQDN>:1433.

    That should do it.

  • Hi Nils Gustav Stråbø,

    Thanks for the details.. really helpful and copy/pasting them into my notes shortly 🙂

    It was originally set up for AD accounts. I got an email saying it would be used with a SQL Account from someone who had sysadmin rights on both boxes while they set up the app.

    They said it was all working and signed off. So i removed the AD account and now they say its broke so it looks like cross wires somewhere.

    Thanks for the better understanding though. This is my first time using Linked Servers and after setting it up I clearly forgot about 90% of what i did (Another call to properly document all work!)

    Thanks again for the help.. It was much appreciated

Viewing 9 posts - 1 through 8 (of 8 total)

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