Linked Server Error: Login failed for user NT AUTHORITY\ANONYMOUS LOGON

microsoft.public.sqlserver.programming

14 Sep 2006 3:24 PM

Jay

I have a SQL 2000 instance and a SQL 2005 instance

on a 32bit Win 2003 Servers. Both systems are using the same user id &

password.

Both instances use windows authentication mode.

In the SQL Server Enterprise Manager on the SQL 2005 instance I created
a

linked server to the SQL 2000 instance. I used the login as the current
login

context.

>From the server itself, I can execute queries from the linked server.

My problems lies when I use the SQL Server Enterprise Manager in my
computer

when executing the same query I get:

OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message

"Communication link failure".

Msg 10054, Level 16, State 1, Line 0

TCP Provider: An existing connection was forcibly closed by the remote
host.

Msg 18456, Level 14, State 1, Line 0

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Is this have something to do with a "double hop". Any help is greatly
appreciated.

14 Sep 2006 4:43 PM

Jay

I have yet to get any replies...maybe I'm in the wrong group section.
Any suggestions a group that may know more about programming and
security with between linked servers?

Jay

Jay wrote:
Show quoteHide quote

> I have a SQL 2000 instance and a SQL 2005 instance
>
> on a 32bit Win 2003 Servers. Both systems are using the same user id &
>
>
> password.
>
> Both instances use windows authentication mode.
>
> In the SQL Server Enterprise Manager on the SQL 2005 instance I created
> a
>
> linked server to the SQL 2000 instance. I used the login as the current
> login
>
> context.
>
> >From the server itself, I can execute queries from the linked server.
>
> My problems lies when I use the SQL Server Enterprise Manager in my
> computer
>
> when executing the same query I get:
>
> OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message
>
>
> "Communication link failure".
>
> Msg 10054, Level 16, State 1, Line 0
>
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
>
> Msg 18456, Level 14, State 1, Line 0
>
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>
> Is this have something to do with a "double hop". Any help is greatly
> appreciated.

14 Sep 2006 5:46 PM

Joshua Andrews

Hi Jay --

First, give it a few minutes -- it's only been a couple or few hours.

Second, the microsoft.public.sqlserver.server group might have more
discussion of linked servers then this group, particularly regarding
authentication issues.

Josh

Jay wrote:
Show quoteHide quote

> I have yet to get any replies...maybe I'm in the wrong group section.
> Any suggestions a group that may know more about programming and
> security with between linked servers?
>
> Jay
>
> Jay wrote:
>
>>I have a SQL 2000 instance and a SQL 2005 instance
>>
>>on a 32bit Win 2003 Servers. Both systems are using the same user id &
>>
>>
>>password.
>>
>>Both instances use windows authentication mode.
>>
>>In the SQL Server Enterprise Manager on the SQL 2005 instance I created
>>a
>>
>>linked server to the SQL 2000 instance. I used the login as the current
>>login
>>
>>context.
>>
>>>From the server itself, I can execute queries from the linked server.
>>
>>My problems lies when I use the SQL Server Enterprise Manager in my
>>computer
>>
>>when executing the same query I get:
>>
>>OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message
>>
>>
>>"Communication link failure".
>>
>>Msg 10054, Level 16, State 1, Line 0
>>
>>TCP Provider: An existing connection was forcibly closed by the remote
>>host.
>>
>>Msg 18456, Level 14, State 1, Line 0
>>
>>Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>>
>>Is this have something to do with a "double hop". Any help is greatly
>>appreciated.
>
>

14 Sep 2006 9:41 PM

Jay

Hey Josh,

Sorry, I needed an answer fast and I noticed other questions (more sql
based) being answered fairly quick.

However my inpatience wasn't a total waste. I had a pretty good idea
of what was causing this, but I had trouble finding a clear explanation
of how to fix it.

It did have to do with double hop scenario, where windows can't pass
along the windows based authentication. I found the following link
very helpful in understanding this issue (nice diagrams with detailed
explanations).

http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

But the quickest fix I found that doesn't involve modifying windows
domain authentication for domains and user accounts was very simple. I
created a SQL Login on both servers with the appropriate permissions.
Then I registered the linked server using the SQL Server login and
password option and specifying the login i created for both servers.

But if you need to keep windows authentication for the server that
points to the linked server you can do the following. First, setup a
SQL Login with the appropriate permissions on the "linked server".
Then use the following extended proc on the server that connects to the
linked server via query:

EXEC sp_addlinkedsrvlogin 'linkedservername', 'false', 'Domain\Jay',
'sqllogin', 'sqlpassword'

This will configure the server to use the sql login and password to
connect to the linked server when user Domain\Jay is logged on. Hope
this helps someone who needs it!

Jay

Joshua Andrews wrote:
Show quoteHide quote

> Hi Jay --
>
> First, give it a few minutes -- it's only been a couple or few hours.
>
> Second, the microsoft.public.sqlserver.server group might have more
> discussion of linked servers then this group, particularly regarding
> authentication issues.
>
> Josh
>
> Jay wrote:
> > I have yet to get any replies...maybe I'm in the wrong group section.
> > Any suggestions a group that may know more about programming and
> > security with between linked servers?
> >
> > Jay
> >
> > Jay wrote:
> >
> >>I have a SQL 2000 instance and a SQL 2005 instance
> >>
> >>on a 32bit Win 2003 Servers. Both systems are using the same user id &
> >>
> >>
> >>password.
> >>
> >>Both instances use windows authentication mode.
> >>
> >>In the SQL Server Enterprise Manager on the SQL 2005 instance I created
> >>a
> >>
> >>linked server to the SQL 2000 instance. I used the login as the current
> >>login
> >>
> >>context.
> >>
> >>>From the server itself, I can execute queries from the linked server.
> >>
> >>My problems lies when I use the SQL Server Enterprise Manager in my
> >>computer
> >>
> >>when executing the same query I get:
> >>
> >>OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message
> >>
> >>
> >>"Communication link failure".
> >>
> >>Msg 10054, Level 16, State 1, Line 0
> >>
> >>TCP Provider: An existing connection was forcibly closed by the remote
> >>host.
> >>
> >>Msg 18456, Level 14, State 1, Line 0
> >>
> >>Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> >>
> >>Is this have something to do with a "double hop". Any help is greatly
> >>appreciated.
> >
> >