Unable to link server to itself

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Unable to link server to itself

Author

Topic

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2009-03-13 : 01:27:50

I am getting this error:

"The linked server has been created but failed a connection test. Do you want to keep the linked server?"

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI" for linked server "SRV1" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "SRV1" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 53)

I have set the remote connections to both named pipes and tcp/ip.

Regards,
Ahmad Osama

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-13 : 07:28:20

Can you give some backgrounds and what you are doing?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-03-13 : 09:04:42

I have created a self referencing linked server several times and it's been working great (usually in a dev environment to emulate two distributed servers) . You do however need to set the server to allow remote connections in Surface Area Manager and of course specify the correct username/password/portnumber/etc. There might actually be a firewall issue as well since a distributed query like this might go through the router...but I'm not sure about this last part.

- Lumbago

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2009-03-13 : 09:48:32

quote:
Originally posted by sodeep

Can you give some backgrounds and what you are doing?

here's the script


/****** Object:  LinkedServer [SRV1]    Script Date: 03/13/2009 19:15:56 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SRV1', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
--EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SRV1',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'

GO
EXEC master.dbo.sp_serveroption @server=N'SRV1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SRV1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SRV1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SRV1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SRV1', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SRV1', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SRV1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SRV1', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SRV1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SRV1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SRV1', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SRV1', @optname=N'use remote collation', @optvalue=N'true'

this works fine, however when I add the linked server login i get the error msg as posted earlier.

Regards,
Ahmad Osama

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources