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 12»»

Linked Servers - Link drops out intermittently..Grrrr Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 3:06 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:17 AM
Points: 179, Visits: 818
Hi All,

I am experiencing an issue where two servers with matching linked server configurations are connected. They are configured to connect under the current security context, which is a windows account and with RPC out and from set to true along with data access. This all works fine the majority of the time and there is a service account calling a process that relies on these connections.

However, from time to time the link drops and refuses conections with the "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error". If I log onto the server and try a cross db query this seems to resolve the issue for a while, even though this is not the service account that is running the process. I know that access tokens are created and may exist for a period of time after I log in to the server but I'm sure that should only be relevant if I was logging in with the relevant service account.

Is there a permament fix to this issue? Has anyone else experienced the problem and resolved it?

BTW - Both servers are run via a service account that has been configured for delegation and both servers have SPN's registered against the account that the SQL service is running against for both the server and the port it is using.

Any help or guidance much appreciated

Cheers

Elliot


SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Post #1451477
Posted Monday, May 13, 2013 2:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 6,172, Visits: 7,241
Kerberos Double Hop error. Pain in the arse the first time you run into it because the keywords don't make sense, but that's what you've got.

Google up those keywords, grab a pot of coffee, and get ready for a really long read.



- 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1452316
Posted Monday, May 13, 2013 2:44 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:17 AM
Points: 179, Visits: 818
Many thanks for your messages but I'm not sure that this is the case, this works from time to time - Surely it would be an all or nothing scenario for the double hop? The process is called the same way every time.

Both servers have SPN's registered and the sql account on both servers is registered for delegation. The only part that may not be configured is the machines themselves as they are not set to delegate in AD, but I am not sure that is necessary.



SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Post #1452321
Posted Monday, May 13, 2013 3:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 6,172, Visits: 7,241
Ness (5/13/2013)
Many thanks for your messages but I'm not sure that this is the case, this works from time to time - Surely it would be an all or nothing scenario for the double hop? The process is called the same way every time.


Yeah, the machines themselves unfortunately must delegate, but as you mentioned it's inconsistent. However, the error itself is still a Kerberos baseline.

You're most likely going to have to drag in one of your network guys on this, and he'll probably have to run Wireshark or something equivalent to try to bag one of the errors when it comes in. That'll give you a lot more information.

I wouldn't troubleshoot the linked server in this case though, unless a particular account or two are the usual culprits. I'd be troubleshooting the network. It could be anything from it using a switch that's across a domain (incredibly unlikely) for traffic routing to something goofyfoot in the accounts that it's using cached data on occassion. I don't know, I'm definately NOT a network sysadmin expert. I do recommend getting one involved though.



- 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1452328
Posted Monday, May 13, 2013 3:34 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:17 AM
Points: 179, Visits: 818
Many thanks for your time and thoughts

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Post #1452342
Posted Tuesday, May 14, 2013 7:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 961, Visits: 4,995
I've run into the same error where I work, and while this may not be the case for you, maybe it'll help.

The problem was, the users getting this error (not linked servers, but SQL logins) had been away for some time, and because of corporate policy, their AD accounts deleted. When they came back, new accounts were created, but the SPIDs (obviously) had changed, so they couldn't login.

Now, in your case, my thoughts are (based on what you've posted so far:)
1. Are the machines at different ends of a "slow" connection (VPN, etc)? If so, it could be a domain replication issue, which is why it works sometimes, or just a corrupted packet during the authentication.
2. If not 1, possibly a "flaky" connection or a slow response from a DC?

Just a couple thoughts. I'd lean towards it probably not being the SQL, though...

Jason

*****************************
I'm sorry, I missed where the account is NT Authority\Anonymous
I'm not sure my thoughts will be applicable, after all.
Post #1452570
Posted Tuesday, May 14, 2013 7:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:36 PM
Points: 1,220, Visits: 6,572
Make sure registry keys for MaxPacketSize is set, and you also have the value set to force Kerberos to use TCPIP.
UDP is the default, which can lead to time out issues (sporadic).
MaxPacketSize is more for users - tickets for groups can sometimes get truncated, so a user (especially when carrying history from an old domain) might experience issues, while other users work fine.
Post #1452591
Posted Tuesday, May 14, 2013 7:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:36 PM
Points: 1,220, Visits: 6,572
http://support.microsoft.com/kb/244474
http://support.microsoft.com/kb/327825

Read these articles.
Post #1452595
Posted Tuesday, May 14, 2013 12:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 23,081, Visits: 31,613
Greg Edwards-268690 (5/14/2013)
http://support.microsoft.com/kb/244474
http://support.microsoft.com/kb/327825

Read these articles.


Making it easier for others:

http://support.microsoft.com/kb/244474
http://support.microsoft.com/kb/327825




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1452754
Posted Tuesday, May 14, 2013 1:06 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:17 AM
Points: 179, Visits: 818
thanks for all your suggestions

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Post #1452791
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse