TCP Provider: No connection could be made because the target machine actively refused it

[SQL-Server-Team]

First published on MSDN on Jul 19, 2007

In this post, I am trying to list out potential cause and general solution to this specific error when you are making connection to SQL Server 2005.

First, It is due to TCP connection fail, since you can tell from the title, "TCP Provider: .xx", which indicates that either your connection string explicitly use 'tcp:" prefix, say "Server=tcp:<machinename> ...." or your client protocol setting ask for first try Named Pipe connection, if fails, then fallback to TCP connection. For eg, in "Client NetworkUtility", you see the protocol order is "np on top of tcp".

Secondly, this error is winsock error "WSAECONNREFUSED  - 10061". You'd better go through the following checklist to fix the "connection refused" problem.

1) Are you connecting to the right server? Check out whether your target machine name is valid.

2) Whether the <machinename> you specified in your connection string resolve to the correct ipaddress? do:

ping <machinename> (if your remote server has ipv6 enabled, it might return ipv6 address). Go to the remote machine to see whether it returned the correct ipaddress.

Then do ping -a <ipaddress>, can it be resolved to correct target machine name?

3) Are you connecting to default instance or named instance? Please go to the target machine, to check whether the instance started successfully, you can go to "Service Control Manager", find out "MSSQLSERVER" or "MSSQL$<instancename>" whether they are running?

4) Does your target SQL instance enabled TCP and listening on a valid TCP port?

a. If it is default instance, on your client machine, try : "telnet <ipaddress> 1433", remember, SQL Server 2005 default instance always use the reserved port 1433, namely, if you modify it to any other port number, the client app can not connect to the sql instance.

b. If it is named instance, on your target server machine, go to "SQL Server Configuration Manager", open "Protocols for MSSQL$<instancename>", check TCP enabled, and right click TCP, find out the tcp port number under 'IPAll". Then on your client machine, do " telnet <hostname> <portnumber>", see whether it succeeds.

5) Is your browser service running on the target server?

If you connect to named instance, then on your target server machine do "net start sqlbrowser".

6) Are your target server behind firewall?

If so, go to " SQL Server 2005 Surface Area configuration", click "Surface Area configuration for service and connection", then click the instance name, enable its remote connection.

You should be able to see the sql tcp port and udp port(1434 for browser service) is opened in Firewall, which enable you can telnet from client.

7) Does your client app set any alias that cause the connection redirect to an invalid port?

a. If you are using MDAC: on your client machine, click "cliconfig.exe", check out alias, whether it points to some other port that you can not telnet, if so, correct it or remove alias, since you should be able to connect through servername by sqlbrowser service automatcally respond to the correct port which server is listening on.

b. If you are using SNAC/SqlClient,  go to SQL Configuration Manager, check out client protocols, see alias setting.

8) Check out IPSec setting if you still face problem. See whether IPSec specification make dropping any packets.

9) If you still face problem, please use "Evenvwr" on the client machine, try to look up the remote server application log, see any info from SQL Server that indicates the sql instance reject client connection. Under this situation, it probably due to invalid client request. Open SQL Profile also can help you dig out which client data operation make server terminate the connection.

MING LU

SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

[evanchatter37]

This error is a network-related error occurred while establishing a connection to the Server. It means that the error is occurring because there is no server listening at the hostname and port you assigned. It literally means that the machine exists but that it has no services listening on the specified port . So, no connection can be established.

Try running netstat -anb from the command line to see if there’s anything listening on the port you were entered. If you get nothing, try changing your port number and see if that works for you. In Windows operating systems, you can use the netstat services via the command line (cmd.exe) . On Linux you may need to do netstat -anp instead.

The target machine actively refused it occasionally , it is likely because the server has a full ‘backlog’ . Regardless of whether you can increase the server backlog , you do need retry logic in your client code, sometimes it cope with this issue; as even with a long backlog the server might be receiving lots of other requests on that port at that time.

[FXLima_Tecnologia]

If you are trying to make a connection with PDO (php):
In some cases, changing the “IP” to the “instance name” may resolve the issue.
It worked for me.

change this:

"sqlsrv:database=your_db; Server=your_ip,1433; Encrypt=0 ; TrustServerCertificate=1"; 
by:

"sqlsrv:database=your_db; Server=your_instance_name,1433; Encrypt=0 ; TrustServerCertificate=1"; 

[pdeliot]

Hi, 

Using IP address to connect to an SQL Server using IP adress is strongly discouraged. 

It defeating, Kerberos and certificate validation. It also force a revert DNS request that can aim to connection failure.

It will also probably fail for Azure instance, preventing connection gateway from working. 

IP to connect is a very bad practice... 

[madrijes]

Team

What happened if I have this error when I access into a DataCube? I created a DataBase and then a DataCube in a Windows Server. 

I am trying access with a other PC using the server address but I have this error. How can I solve it?