Linked Server Issue

  • I am having an issue with some linked servers. Here is the scenario:

    ProdA, ProdB, and DevC are all identical servers except DevC has half the memory of the Prod servers. I create linked server objects on DevC to ProdA and ProdB. I am using the "be made using the login's current security context" option. When I test the connections, for ProdA it tests fine and I can get to tables on the remote server. For ProdB I get the "login failed for NT AUTHORITY\ANONYMOUS" error message. Here's the kicker: If I use Remote Desktop to log on to the DevC server and test the linked server objects, it works fine, no error message, and I can query data on both Prod servers. My guess is that it is related to the Kerberos double-hop issue. But why would that the be case on one server and not the other? I am pulling out what little hair I have left so any insight would be appreciated.

  • Sounds like double hop issue. What accounts are the SQL Server services on the prod servers running as? Depending on the answer, either check for an SPN registered to the service account(s) the computer name(s) Chances are you need to register a new SPN to get things working.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • The double hop issue is the direction I am leaning as well. But, all instances are using the same domain account to run their services, and all three servers are registered as SPNs with the domain account. We are using CNAMEs in DNS to point to the instances, and both of the CNAMEs are registered as SPNs with the domain account as well. The most frustrating thing is why would I be having a double hop issue with one and not with the other?

  • When I have issues like this I run setspn /L and scrutinize the results. All SPNs have to be perfect for things to work.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Did that. Even ran setspn with the -X option and no duplicates exist for any of the affected servers or accounts. I'm not quite sure what you mean by "perfect". My situation seems "perfect", but it ain't working.

  • Perfect meaning all accounts or servers are registered, port numbers are spot-on, and even fully-qualified names are registered in case you used that, e.g. you could have registered servername.domainname.extension but if you used servername as the host when you created your Linked Server you may not be getting the SPN resolution you expect.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks to @opc.three for all the input. If this is the case, it seems rather ridiculous that all the ducks must be in a perfect row for this to work. At best it is a pain, at worst it could be an unworkable situation. However, this still doesn't seem to add up. I have a server registered as SPN ProdA.my.dom, but only added as linked server ProdA and it seems to work fine. I have registered ProdB as ProdB, ProdB.my.dom, and ProdB:1433 and my linked server using the current user's credentials do not work on that one.

  • ApologetixFan (7/9/2013)


    Thanks to @opc.three for all the input. If this is the case, it seems rather ridiculous that all the ducks must be in a perfect row for this to work. At best it is a pain, at worst it could be an unworkable situation. However, this still doesn't seem to add up. I have a server registered as SPN ProdA.my.dom, but only added as linked server ProdA and it seems to work fine. I have registered ProdB as ProdB, ProdB.my.dom, and ProdB:1433 and my linked server using the current user's credentials do not work on that one.

    Kerberos tickets are a pain and it takes a lot to get them right and keep them right, but if it's not right, it's not right. Try being explicit in your Linked Server config and specifying the fully-qualified domain with the port number, like this with a comma:

    ProdB.my.dom,1433

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Tried all of the following:

    ProdB

    ProdB,1433

    ProdB.my.dom

    ProdB.my.dom,1433

    AliasToProdB

    AliasToProdB,1433

    AliasToProdB.my.dom

    AliasToProdB.my.dom,1433

    Nothing works. Keep in mind, as I stated in my OP, all this works fine if I am logged into the DevC server (RDS or physically), which confirms to me that the target servers are configured identically. And, since users are not allowed to log in to servers, and they will be running queries from their desktops (via SSMS), it needs to work that way.

  • If you need this to work, you're in for a deep-dive.

    Troubleshooting Kerberos Authentication problems – Name resolution issues

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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