MS SQL Server - HELP! Linked Server Problem

Author

HELP! Linked Server Problem

Howard Carr

2006-03-08, 7:23 am

I have a SQL 2000 instance on a 32 bit Win2000 server and a SQL 2005 instance
on a 64bit Win 2003 Server. 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
(I am in the admin group on both machines and I have sysadmin roles on both)
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'.

I dont understand this as I'm using Windows Autherntication on everything.

Immy

2006-03-08, 7:23 am

Have you tried to use Named Pipes on your client network configuration?

"Howard Carr" < HowardCarr@discussio
ns.microsoft.com> wrote in message
news:BB430B53-5AA8-47FA-B8EB- 215ABF91BB49@microso
ft.com...
>I have a SQL 2000 instance on a 32 bit Win2000 server and a SQL 2005
>instance
> on a 64bit Win 2003 Server. 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
> (I am in the admin group on both machines and I have sysadmin roles on
> both)
> 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'.
>
> I dont understand this as I'm using Windows Autherntication on everything.

Howard Carr

2006-03-08, 9:23 am

Tried Pipes, but not the issue.
For some reason, the windows authentication mode is not working properly.

"Immy" wrote:

> Have you tried to use Named Pipes on your client network configuration?
>
> "Howard Carr" < HowardCarr@discussio
ns.microsoft.com> wrote in message
> news:BB430B53-5AA8-47FA-B8EB- 215ABF91BB49@microso
ft.com...
>
>
>

Steen Persson (DK)

2006-03-08, 9:23 am

Howard Carr wrote:[color=darkred
]
> Tried Pipes, but not the issue.
> For some reason, the windows authentication mode is not working properly.
>
> "Immy" wrote:
>

You might take a look at Security Account Delegation in Books On Line.
In order to use Windows authentication you must enable the servers for
Account Delegation, and also the SQL server usr account must be enabled
for delegation. I think the problem is that when you try from your own
machine, you use an account and computer that isn't trusted.

Regards
Steen

Jeffrey K. Ericson

2006-03-08, 8:23 pm

I believe you are experincing the two-hop problem? You can't pass Windows
authentication credentials across a link in SQL Server. If you define a
static SQL ID for the link and use that, you should be fine

"Howard Carr" wrote:

> I have a SQL 2000 instance on a 32 bit Win2000 server and a SQL 2005 instance
> on a 64bit Win 2003 Server. 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
> (I am in the admin group on both machines and I have sysadmin roles on both)
> 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'.
>
> I dont understand this as I'm using Windows Autherntication on everything.

Sponsored Links