SQL SERVER – Linked Server Error: TCP Provider: No Connection Could be Made Because the Target Machine Actively Refused It

While trying to create linked server, sometimes there are errors. One of the error is below which is related to TCP Provider.

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

TCP Provider: No connection could be made because the target machine actively refused it.
OLE DB provider “SQLNCLI11” for linked server returned message “Login timeout expired”.
OLE DB provider “SQLNCLI11” for linked server “11.1.11.25” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”. (Microsoft SQL Server, Error: 10061)

Based on my search on internet there are multiple reasons for this error. Also, the error is not specific to linked server, it can also appear with normal SQL connectivity via application or SQL Server Management Studio (SSMS).

CHECKLIST

Here is the checklist which I made from the internet search.

1. Make sure the server name is correct.
2. Make sure that ping is resolving to IP of right destination server.
3. Check if we have default or named instance on the destination server. Make sure that we are connecting to right port or instance name.
4. Since error message talks about TCP, make sure we can telnet to the port. You can run below from command prompt.

Telnet <ServerName> <Port>

If telnet is not installed, then you need to use server manager to install feature called “Telnet Client”.

If Telnet is not able to connect to IP and port combination, then firewall needs to be checked. You need to punch a hole in the firewall for SQL port and UDP port for SQL Browser.

In my case, I checked almost everything, but none worked. After a clam mind and rewind of activity performed I realized that I changed the server name, did drop server and add server. SQL SERVER – How to Change Server Name?

BUT I didn’t restart SQL Service. So, one more item in above checklist is that, make sure @@Servername is matching with host name and one restart has been attempted.

Reference: Pinal Dave (https://blog.sqlauthority.com)