(provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)
The scenario:
You try to connect to Sql Server but you are getting the following connection error (they vary depending on the provider used):
Using the .Net SqlClient provider:
------------------------------------------------------------------------------------------------------------------------------------------------------------
System.Data.SqlClient.SqlException:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.
Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
(provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)
Using the sqlcmd application from the command prompt:
------------------------------------------------------------------------------------------------------------------------------------------------------------
HResult 0x274D, Level 16, State 1
TCP Provider: No connection could be made because the target machine actively refused it.
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005,
this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
Using ODBC:
------------------------------------------------------------------------------------------------------------------------------------------------------------
[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).
Using DB-LIB:
------------------------------------------------------------------------------------------------------------------------------------------------------------
DB-Library: Unable to connect: SQL Server is unavailable or does not exist. Unable to connect: SQL Server does not exist or network access denied.
Net-Library error 10061: ConnectionOpen (Connect()).
So, what can we conclude from these errors?
Well, the key is in the ‘target machine actively refused it’/’access denied’ parts of the error messages.
What this tells us is that the machine (server) we are trying to connect to actually is found, but it is for some reason denying the client access.
If we would try to connect to a non existing machine we would get something along these lines (here is when using sqlcmd)
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
So, why is it denying us this access? If checking for example the Sql Server logs then we see nothing, no incorrect password etc. This tells us that the connection never reaches the server.
It is denied before it even tries to login to the server.
The most common reason for this is that the client is trying to connect to a TCP port on the machine that is not valid, ie, nothing is listening on that port, or the port is not a Sql Server port.
To verify this, download the Microsoft Network Monitor tool from here:
"Microsoft Network Monitor"
(this tool should really be used for any type of connection error since it gives out a lot of useful information)
Start a new capture and try to connect, one you have got the connection error, stop the capture and look for rows where the source IP is your client and the destination IP is your server.
This can be done by simply running “ipconfig” (no quotes) from a command prompt. In the example below, the client IP is: xxx.xxx.xx.78 and the server IP is: xxx.xxx.xx.187 1234
...
88 xxx.xxx.xx.78 50313 xxx.xxx.xx.187 1234 TCP TCP: Flags=.S......, SrcPort=50313, DstPort=1234, Len=0, Seq=3060051214, Ack=0, Win=8192 (scale factor not found)
89 xxx.xxx.xx.187 1234 xxx.xxx.xx.78 50313 TCP TCP: Flags=..R.A..., SrcPort=1234, DstPort=50313, Len=0, Seq=0, Ack=3060051215, Win=0 (scale factor not found)
...
100 xxx.xxx.xx.78 50313 xxx.xxx.xx.187 1234 TCP TCP: Flags=.S......, SrcPort=50313, DstPort=1234, Len=0, Seq=3060051214, Ack=0, Win=8192 (scale factor not found)
101 xxx.xxx.xx.187 1234 xxx.xxx.xx.78 50313 TCP TCP: Flags=..R.A..., SrcPort=1234, DstPort=50313, Len=0, Seq=0, Ack=3060051215, Win=0 (scale factor not found)
...
Here we can see that the client is trying to connect (Flags=.S......) from port 50313 (SrcPort) to port 1234 (DstPort) on the server.
We can also see that the server machine instantly is resetting the connection (Flags=..R.A... ).
Go to the server machine, start a command prompt and run “netstat -aon” (no quotes), this should give a list along the lines:
Active Connections
Proto Local Address Foreign Address State PID
TCP 0.0.0.0:1233 0.0.0.0:0 LISTENING 1864
TCP 0.0.0.0:1235 0.0.0.0:0 LISTENING 620
...
If the port (1234 in this example) is not listening on the machine, the machine will refuse the connection and that is the reason for your error.
If the machine is listening on the port (again 1234 in this case), verify that this is actually Sql Server and not some other application.
This can be done by taking notice of the PID, start Task Manager and check what application is running with that PID. If it is not Sql Server, that is the reason for your error.
So why does the client try to connect to an invalid port? Common reasons are:
.1 You have specified a port number in your connection string, for example:
string cs = @"Data Source=tcp:server\instance,1234;Integrated Security=SSPI";
and the server is listening on another port. Check the Sql Server log, here you will see in the start sequence what port the server is listening to, for example:
2008-12-11 03:11:12.50 Server Server is listening on [ 127.0.0.1 <ipv4> 1866].
Make sure that your connection string uses the correct port.
.2 You have an alias defined on the client machine and this alias has the same name as the server and a specified port.
This means that when you connect, the provider finds the alias with the same name as the server, and it then uses the port defined for that alias.
This means that you will in effect use a connection string with the port provided.
You can check for aliases in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo\
If you have an entry here with the same name as the server you are trying to connect to and there is a port number suffix, then this port number will be used. And fail if the server is not listening on it.
So to summarize, the reason you are getting the errors above are most likely cause by your clients trying to access a the server on a port that does not exist, is not listening or is not a Sql Server port.