Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server Issue Expand / Collapse
Author
Message
Posted Friday, July 05, 2013 7:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 8:58 AM
Points: 39, Visits: 96
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.
Post #1470741
Posted Monday, July 08, 2013 5:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1471395
Posted Tuesday, July 09, 2013 7:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 8:58 AM
Points: 39, Visits: 96
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?
Post #1471616
Posted Tuesday, July 09, 2013 7:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1471627
Posted Tuesday, July 09, 2013 8:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 8:58 AM
Points: 39, Visits: 96
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.
Post #1471638
Posted Tuesday, July 09, 2013 8:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1471657
Posted Tuesday, July 09, 2013 9:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 8:58 AM
Points: 39, Visits: 96
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.
Post #1471714
Posted Tuesday, July 09, 2013 9:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1471717
Posted Tuesday, July 09, 2013 10:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 8:58 AM
Points: 39, Visits: 96
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.
Post #1471728
Posted Tuesday, July 09, 2013 10:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1471729
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse