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

SQL
5 Comments

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

SQL SERVER - Linked Server Error: TCP Provider: No Connection Could be Made Because the Target Machine Actively Refused It link-error-01-800x388

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).

Solarwinds

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – The SaveToSQLServer Method Has Encountered OLE DB Error Code 0x80004005
Next Post
SQL SERVER – xp_cmdshell and Net Use ERROR: The Local Device Name is Already in Use

Related Posts

5 Comments. Leave new

  • 5. make sure that the linked server isnt under pressure and unable to accept connections.

    Reply
  • And not to forget to check if the target machine is under preasure
    br
    gerald

    Reply
  • In my org all new servers are built with TLS1.0 turned off. If your system is not patched to the minimal SP/CU needed to support TLS1.2 you will get this error. If you check the Windows Event logs you will find where SQL thought the connection was fine but the OS doesn’t. This causes the “forcibly disconnected” action. This will actually cause failure of SQL Server installs in 2014 and lower. We enable TLS1.0, complete install and config, then turn it back off once the system is updated to support 1.2.

    Reply

Leave a Reply

Menu