Linked Servers - Link drops out intermittently..Grrrr

Question

Linked Servers - Link drops out intermittently..Grrrr

Ness

SSCarpal Tunnel

Points: 4282
More actions
May 10, 2013 at 3:06 am

#277371

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 :hehe: 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

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 2

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 4

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

jasona.work SSC Guru Points: 50118 More actions · Answer 5

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.

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 6

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.