Delegation through Linked Server Stops working

I have a Linked Server from SQL 2005 to a SQL 2000 server. I have it
configured to use delegation. This will work fine for a while and then
suddenly stop working. Sometimes it works for an hour, sometimes for a day.
I have to restart the SQL 2005 server and it will begin to work again. The
error is:
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Any ideas?A few others have reported similar issues - with no
solutions. I worked at a place where we had delegation
sporadically failing and then working after reboots. A
ticket was opened with Microsoft but the issue was never
resolved. I would guess it's a Kerberos issue, not a SQL
issue. Make sure AD is clean and you don't have duplicate or
bad SPNs for all machines involved. Make sure all machines
involved have times sync working correctly, using the same
time server.
I'd suggest getting the Kerberos Delegation troubleshooting
doc available at:
http://www.microsoft.com/downloads/...&DisplayLang=en
We also installed a tool that would do verbose logging for
Kerberos errors - I just looked and couldn't find the tool.
Maybe if someone else knows they will jump in and provide a
link for that tool.
It can be a difficult issue to troubleshoot and you may want
to consider opening up a support ticket with Microsoft
Product Support.
-Sue
On Wed, 16 Aug 2006 12:43:01 -0700, Sheriff
<Sheriff@.discussions.microsoft.com> wrote:

>I have a Linked Server from SQL 2005 to a SQL 2000 server. I have it
>configured to use delegation. This will work fine for a while and then
>suddenly stop working. Sometimes it works for an hour, sometimes for a day
.
>I have to restart the SQL 2005 server and it will begin to work again. The
>error is:
>TCP Provider: An existing connection was forcibly closed by the remote host
.
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Any ideas?|||Is there a solution for this issue.
delegation on linked server fails in our network when we use
nt-authenticated logins. we have a sql server 2000 nodes (n1,n2) on win 2003
cluster.
Any thots,hints,links,pointers appreciated
thanks,
GA
"Sue Hoegemeier" wrote:

> A few others have reported similar issues - with no
> solutions. I worked at a place where we had delegation
> sporadically failing and then working after reboots. A
> ticket was opened with Microsoft but the issue was never
> resolved. I would guess it's a Kerberos issue, not a SQL
> issue. Make sure AD is clean and you don't have duplicate or
> bad SPNs for all machines involved. Make sure all machines
> involved have times sync working correctly, using the same
> time server.
> I'd suggest getting the Kerberos Delegation troubleshooting
> doc available at:
> http://www.microsoft.com/downloads/...&DisplayLang=en
> We also installed a tool that would do verbose logging for
> Kerberos errors - I just looked and couldn't find the tool.
> Maybe if someone else knows they will jump in and provide a
> link for that tool.
> It can be a difficult issue to troubleshoot and you may want
> to consider opening up a support ticket with Microsoft
> Product Support.
> -Sue
> On Wed, 16 Aug 2006 12:43:01 -0700, Sheriff
> <Sheriff@.discussions.microsoft.com> wrote:
>
>|||Are you having a completely different issue?
This post was about delegation working and then suddenly
failing until a reboot. Is this your issue?
-Sue
On Sun, 27 Aug 2006 12:23:01 -0700, DallasBlue
<DallasBlue@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Is there a solution for this issue.
>delegation on linked server fails in our network when we use
>nt-authenticated logins. we have a sql server 2000 nodes (n1,n2) on win 200
3
>cluster.
>Any thots,hints,links,pointers appreciated
>thanks,
>GA
>"Sue Hoegemeier" wrote:
>|||looks like it works for few minutes when restarted the nodes...
"Sue Hoegemeier" wrote:

> Are you having a completely different issue?
> This post was about delegation working and then suddenly
> failing until a reboot. Is this your issue?
> -Sue
> On Sun, 27 Aug 2006 12:23:01 -0700, DallasBlue
> <DallasBlue@.discussions.microsoft.com> wrote:
>
>|||yes, when we restat the nodes the kerberos delegation starts to work for few
minutes and then stops with the 'login failed reason (null)' error...
"Sue Hoegemeier" wrote:

> Are you having a completely different issue?
> This post was about delegation working and then suddenly
> failing until a reboot. Is this your issue?
> -Sue
> On Sun, 27 Aug 2006 12:23:01 -0700, DallasBlue
> <DallasBlue@.discussions.microsoft.com> wrote:
>
>|||So then you followed everything in the troubleshooting
delegation doc? No one has every really posted any
resolution. I had posted a lot of steps Microsoft will have
you do when/if you open a ticket. That's about all I know
about it. It's not really a SQL issue, it's a kerberos
issue. Whether it's AD problems or issues with tickets
expiring, it's hard to say.
-Sue
On Tue, 29 Aug 2006 14:38:02 -0700, DallasBlue
<DallasBlue@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>yes, when we restat the nodes the kerberos delegation starts to work for fe
w
>minutes and then stops with the 'login failed reason (null)' error...
>"Sue Hoegemeier" wrote:
>|||do have a ticket open with microsoft for more than a month now, but no
resolution yet. "Troubleshooting Kerberos delation" is nearly a 90 page doc.
tried a lot of things nothing seems to fix it...
"Sue Hoegemeier" wrote:

> So then you followed everything in the troubleshooting
> delegation doc? No one has every really posted any
> resolution. I had posted a lot of steps Microsoft will have
> you do when/if you open a ticket. That's about all I know
> about it. It's not really a SQL issue, it's a kerberos
> issue. Whether it's AD problems or issues with tickets
> expiring, it's hard to say.
> -Sue
> On Tue, 29 Aug 2006 14:38:02 -0700, DallasBlue
> <DallasBlue@.discussions.microsoft.com> wrote:
>
>|||Yup...a client site I was at had the issue and an open case
with PSS. At first I thought it was expiring tickets causing
the problem but then it looked more like it could be
duplicate/bad SPNs. We'd clean out AD and then find
duplicate SPNs errors after removing all dupes. Don't know
what else to tell you - I haven't seen anyone who has a
ticket open post a solution. And the place I was at never
got a resolution to the problem either.
-Sue
On Wed, 30 Aug 2006 14:50:03 -0700, DallasBlue
<DallasBlue@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>do have a ticket open with microsoft for more than a month now, but no
>resolution yet. "Troubleshooting Kerberos delation" is nearly a 90 page doc
.
>tried a lot of things nothing seems to fix it...
>"Sue Hoegemeier" wrote:
>|||Things started working with the double hop after forcing the kerberos to use
TCP instead of UDP as in the article 244474 at
support.microsoft.com/kb/244474/
"Sue Hoegemeier" wrote:

> Yup...a client site I was at had the issue and an open case
> with PSS. At first I thought it was expiring tickets causing
> the problem but then it looked more like it could be
> duplicate/bad SPNs. We'd clean out AD and then find
> duplicate SPNs errors after removing all dupes. Don't know
> what else to tell you - I haven't seen anyone who has a
> ticket open post a solution. And the place I was at never
> got a resolution to the problem either.
> -Sue
> On Wed, 30 Aug 2006 14:50:03 -0700, DallasBlue
> <DallasBlue@.discussions.microsoft.com> wrote:
>
>