SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked Servers - Link drops out intermittently..Grrrr


Linked Servers - Link drops out intermittently..Grrrr

Author
Message
Ness
Ness
SSC Eights!
SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)

Group: General Forum Members
Points: 951 Visits: 974
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
Evil Kraig F
Evil Kraig F
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19927 Visits: 7660
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
Ness
Ness
SSC Eights!
SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)

Group: General Forum Members
Points: 951 Visits: 974
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
Evil Kraig F
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19927 Visits: 7660
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
Ness
Ness
SSC Eights!
SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)

Group: General Forum Members
Points: 951 Visits: 974
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
jasona.work
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9890 Visits: 12526
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 farSmile
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
Greg Edwards-268690
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4108 Visits: 8592
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.
Greg Edwards-268690
Greg Edwards-268690
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4108 Visits: 8592
http://support.microsoft.com/kb/244474
http://support.microsoft.com/kb/327825

Read these articles.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92143 Visits: 38954
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

Cool
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)
Ness
Ness
SSC Eights!
SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)

Group: General Forum Members
Points: 951 Visits: 974
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search