Recently we had to create a linked server between a SQL Server 2008 (64 Bit) and SQL Server 7.0 (32 Bit). To Create Linked Server we used below command:
EXEC sp_addlinkedserver @server=’ABC’,@srvproduct=”,
When this query was executed SQL Raised an exception as below:
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 26, Level 16, State 1, Line 0
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.
SQL Server 2008 can use different Providers:
* SQLNCLI (SQL Native Client 9.0 OLEDB Provider released with SQL Server 2005)
* SQLNCLI10 (SQL Native Client 10.0 OLEDB Provider Released with SQL Server 2008)
* SQLOLEDB Provider for SQL Server.
* MSDASQL (OLEDB Provider for ODBC)
We tried all the options and the only option that works is MSDASQL and none of other work.
So to create a linked Server between SQL Server 2008 and SQL Server 7.0 is
* Use ODBC Administrator to create DSN that connects to SQL Server 7.0.
* Create a Linked Server and Choose MSDASQL and then connect to DSN that was created.
* This way we can connect between SQL 2k8 and SQL 7.0 (if needed).