Linked Server Problems from SQL2005

Question

Linked Server Problems from SQL2005

TDuffy

SSCarpal Tunnel

Points: 4170
More actions
July 13, 2006 at 9:43 am

#366779

When I run linked server queries from SQL2005 it fails.

Query Example :
SELECT * FROM [servername].master.dbo.sysobjects

Error:
OLE DB provider "SQLNCLI" for linked server " servername" 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 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Notes:
The linked server is configured to use the current logins security context. It does not matter whether I connect to SQL2000 or SQL2005.

I have a case with MS (6 hours on the phone yesterday), but no help yet. All spn settings are as recommended by Microsoft. The MS engineer states that SQL is not passing the login as indicated in a capture file from netcap.

Any help is appreciated.

Terry

Viewing 9 posts - 1 through 8 (of 8 total)

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

aureolin Hall of Fame Points: 3520 More actions · Answer 1

Are you using Windows logins or SQL (old style) logins? IF this was SQl 2000 I'd say "you must use SQL logins" to do linked queries - and it may be true of SQL 2005 as well.

TDuffy SSCarpal Tunnel Points: 4170 More actions · Answer 2

The servers accept windows and sql logins.

The linked servers need to use the secruity context of the logged in user, and therfore must be made with windows authentication. Audidting needs demand I keep this architecture, and it works fine in SQL2000 when spns are configured correctly.

Terry

malia SSC Enthusiast Points: 135 More actions · Answer 3

Hi Terry - have you progress further/had any success with this?

May already be in place but just in case if you've checked that:

- remote server accepts windows auth (ie allow mixed mode ) ?

- since you're using windows auth, does this current login have access to the remote database (on remote server)? ie is in the list of "Logins" on remote server ?

TDuffy SSCarpal Tunnel Points: 4170 More actions · Answer 4

Some progress but no solution.

A work around is to create an alias with the same name as the remote server, and force it to use named pipes (important that in a 64bit world, the alias is created in the non-32bit portion of configuration manager). Another 3 hour marathon with MS yesterday (now up to 14 hours) but this was fruitful.

FYI...All servers are set to mixed mode. Logins tested are all valid on all servers tested.

Terry

Bhushan Kalla-348741 SSC Veteran Points: 267 More actions · Answer 5

Hi Terry,

I got the same error yesterday when I was working on linked servers between SQL Server 2005 64 bit and SQL Server 2000 32 bit. The solution for this problem is, follow the below steps as is (This is given in the mircosoft website). Linked server name is your SQL Server 2000 instance name and this script should be executed on SQL Server 2000 box.

Check out the link before you follow the steps.

http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

Use Windows Authentication mode

1. Log on to the computer by using a Windows account that is a member of the SQL Server sysadmin fixed server role.

2. Click Run, type cmd.exe, and then click OK.

3.

At the command prompt, type one of the following commands, and then press ENTER:

For a default instance

osql -E -S <LinkedServerName> -i <Location>\instcat.sql

For a named instance

osql -E -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql

Note <LinkedServerName>, <InstanceName>, and <Location> represent the linked server name, the instance name, and the full path of the folder that contains the Instcat.sql script. By default, this folder is C:\Program Files\Microsoft SQL Server\MSSQL\Install.

Back to the top

Use SQL Server Authentication mode

1. Log on to the computer by using any Windows account.

2. Click Run, type cmd.exe, and then click OK.

3.

At the command prompt, type the following command, and then press ENTER:

For a default instance

osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName> -i <Location>\instcat.sql

For a named instance

 osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql

Bhushan

TDuffy SSCarpal Tunnel Points: 4170 More actions · Answer 6

Bhushan,

Thank you for the feedback

Using instcat solves schema binding issues only. This is not what I am experiencing and I have run instcat on the 2000 boxes.

Terry

JuncTheMayl Hall of Fame Points: 3912 More actions · Answer 7

Just out of curiosity, but are you executing your query while logged into the sql05 box, or are you using the management studio while logged into another box? I'm asking because I've seen something like this before related to Kerberos authentication... it can't pass the credentials "twice"... once from your box to the SQL05 box, then once again from the SQL05 box to the linked server.

TDuffy SSCarpal Tunnel Points: 4170 More actions · Answer 8

This is when logged into to sql05 box. It is not a "double hop" situation.

FYI...to get double hops to work, you need to set up servers and accounts running SQL to allow delegation, and you need to set up spns for the servers involved.

By way of update, I am still working with MS on this. It has not been fun and I have no resolution.

Terry