TCP Provider: An existing connection was forcibly closed by the remote host
Scenario is like this:
Error generated when trying to register the Linked server between 2 SQL instances.
SQL Server setup information is as follows:
provider:SQL native Client
Product Name: SQLNCL1
Data Source: Somewhere IN this world (SQLServer 2000 default instance)
catalog=Northwind
1. Remote connections: using both TCP/IP and Named Pipes on both server.
2. Domain user on both sever.
3. Mixed Authentication Mode on remote server and Windows authentication Mode on Local source server.
4. I am using windows authenticationuser for both server.
The resolution is as follows:
Refer this KBA article as a first hand reference and also check relevant protocol & services have been enabled with proper privileges. Also check the connection string for the provider: Provider=SQLNCLI.1;Password=girish;Persist Security Info=True;User ID=sa;Initial Catalog=AdventureWorks;Data Source=DOmain\SQLInstance (you have to change Persist security Info=false if Windows authentication is used).
If the 2 providers are SQL Server then ensure the relevant SQL Server services account has relevant privileges between those instances to connect. Refer to other blog post about Domain account best practices between the SQL instances.