May 30, 2013 at 5:42 am
Hi
Got a linked server on SQL Server 2008R2 connecting to an SQL server 2005 that was working fine but now it stopped working.
Both instances are clustered.
Test of linked server gives this reply:
TITLE: Microsoft SQL Server Management Studio
------------------------------
The test connection to the linked server failed.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
TCP Provider: An existing connection was forcibly closed by the remote host.
Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.
OLE DB provider "SQLNCLI10" for linked server "Sxxxxx18\Sxxxxx18" returned message "Client unable to establish connection due to prelogin failure".
OLE DB provider "SQLNCLI10" for linked server "Sxxxxx18\Sxxxxx18" returned message "Client unable to establish connection". (Microsoft SQL Server, Error: 10054)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476
Linked server type is "sql server" and sql login for the linked server are verified ok.(connected SSMS)
We have tried different scenarios to locate the problem but have not manage to do that, other than problem seems to be located at Sxxxxx18\Sxxxxx18
Below test works fine
Connecting by SSMS from physical box Syyyyy32 (Windows 2008), (Sxxxxx32\Sxxxxx32) 10.50.2500 to Sxxxxx18\Sxxxxx18 works fine.
Create a linked server on Sxxxxx32\Sxxxxx32 to Sxxxxx17\Sxxxxx17 (9.0.5000) (physical node Syyyyy21 (Windows 2003), same physical node as for Sxxxxx18\Sxxxxx18) works fine.
Create a linked server on Sxxxxx18\Sxxxxx18 (9.0.5000) to Sxxxxx32\Sxxxxx32 (1050.2500) works fine (reversed way of failing linked server)
A Linked server on Sxxxxx90\Sxxxxx90 (9.0.5057) to Sxxxxx18\Sxxxxx18 (9.9.5000) works fine
Test below are throwing same error as well:
Trying to create new Linked server on Sxxxxx32\Sxxxxx32 (10.50.2500) to Sxxxxx18\Sxxxxx18 throw same errormessage as existing linked server when tested.
Trying to create a linked server from another server, with version 10.50.4000 (Sxxxxx23) to Sxxxxx18\Sxxxxx18 (9.0.5000) throws same error.
Physical node Syyyyy21 (Sxxxxx18\Sxxxxx18) has about 18 GB free memory and Sxxxxx18\Sxxxxx18 sqlsvr.exe using approx 300 MB
and memorysetting for allows up to 3GB
Cpu is on approx 20-30%
No newly applied patches that I can found.
Anyone that got a clue on what it could be?
May 31, 2013 at 3:03 am
Problem solved for the moment:
resolution:
Action performed by MS support personal on our server is:
Checked the connectivity through universal data link file:
1.by using Microsoft OLE DB Provider for SQL Server - But it was failing
2.by using SQL Server native client 10.0 - it was failing
3.Forced TCP protocol, it was again failing
4.Forced named pipe protocol, it was success for OLE DB Provider
5.Again forced TCP protocol, this time it was success to his surprise.
Then he created the linked server and this is success this time again for his surprise.
Actually he is also not sure how this is resolved and whether it will come in near future or not, but he said, he will discuss this issue with other and share their thought in detailed email. :crazy:
May 31, 2013 at 3:15 am
pnorlin (5/31/2013)
Problem solved for the moment:resolution:
Action performed by MS support personal on our server is:
Checked the connectivity through universal data link file:
1.by using Microsoft OLE DB Provider for SQL Server - But it was failing
2.by using SQL Server native client 10.0 - it was failing
3.Forced TCP protocol, it was again failing
4.Forced named pipe protocol, it was success for OLE DB Provider
5.Again forced TCP protocol, this time it was success to his surprise.
Then he created the linked server and this is success this time again for his surprise.
Actually he is also not sure how this is resolved and whether it will come in near future or not, but he said, he will discuss this issue with other and share their thought in detailed email. :crazy:
Doesn't sound so re-assuring on how/why it could have happened in the first place. Hopefully you will post back MSFT support info on it when you give you more details.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply