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


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


Comments (26)

  1. Dmitri says:

    Hello,

    could explain how correctly set SPNs for many instances, i.g.

    fot default instance of sqlserver Alpha.microsfot.com we have:

    setspn -A MSSQLSvc/Alpha.microsfot.com:2450 microsfot.comsql_admin

    and

    setspn -A MSSQLSvc/Alpha:2450 microsfot.comsql_admin

    Also we have named SQLServer instance on this server [AlphaBeta] listening TCP port 2451

    How correctly set SPN for [AlphaBeta]?

  2. Il-Sung Lee says:

    Hi Dmitri,

    For your Beta instance, you would use:

    setspn -a MSSQLSvc/Alpha.microsoft.com:2451 <service account>

    i.e, specify the port upon which the instance listens.

    Il-Sung.

  3. Dmitri says:

    Thanks a lot, Il-Sung.

    Dmitri.

  4. Jon Hancock says:

    …The service needed to be started from Surface config mgr.

  5. Leonard Williams says:

    I’m getting this error using 2005 Analysis Server, when it tries to connect to localhost. localhost is started and I’m using the MS OLE data provider for Analy Server 2005. Some assistance please.

  6. Teemusa says:

    I added port 1443 to the IP all in configuration manager and started working

  7. Andrew says:

    What are ways to trace why a connection is falling back to named pipes from TCP in SQL 2005, with a 3rd party app using MDAC connection?

  8. Yuliya says:

    Thank you very much for this article. Now everything works fine!

  9. Dan Moran says:

    Thanks for this.  You saved me a whole heap of pain.  Wish I’d read it yesterday.

  10. MicG says:

    ok i have went through all the step that you have here and read all the blogs that are up and My error is still occuring. I

    TCP Provider, error: 0 – No connection could be made because the target machine actively refused itf anyone could help please

    i read the blog that states that i should check this and that but the thing is that i have more apps going to through the firewall and remotey connectin. only thing  different with my app is that i am using a Sqldatasource connection and in the web.config it add another name than what what was there. Cause it is another connection to the database. please and sorry so long

  11. Albert says:

    In Step 4 it reads: "if you modify it to any other port number, the client app can not connect to the sql instance"

    Are you telling me that you simply cannot remotely connect to a default instance using a non standard port?

  12. Amila says:

    Thanks very much for this. This is a perfect article. You saved so much time.

  13. Drew says:

    Very helpful article – thank you for the gift!

  14. Monichan says:

    Hi

    If you wants to know how to enable remote connections

    in sql server 2005 pls visit this

    http://aspnetmembershipprovider.blogspot.com/2009/02/eanble-remote-connections-on-instance.html

    Regards

  15. Neeraj says:

    Hi !!!

    I am also facing the similar problem , actually i changed the port number in th Alias name for tcp which is different in the protocols for sqlexpress in  tcp.

    I think port number and name should be same and yours client must have calling same name which it wants to connect.

    Thanks

  16. gavrilien says:

    I have sql server 2005 install in my laptop with window vista, i can’t connect to database engine.

  17. jholt says:

    I’m getting this same error and we are not using a SQL 2005 server, but a SQL 2000 backend.  The error references "SQL Server 2005", but also "System lacked sufficient buffer space or because a queue was full".  It almost sounds like an IIS problem.

  18. hakim says:

    hi there,

    I have a static IP and my sql server machine is connected to router.Router doesn’t allow port forwarding for 1433 port.So i am using 27015( Half Life’s) port no.

    I have setup my sql server to listen to port 27015 also created alias for that.

    When I try to connect from outside my router network it shows me this error "Provide:TCP provide ,error:0,No connection could be made because the target machine actively refused it"

    Please guide me to solve the problem.

    Thanx in advance.

    Best Regards

    Hakim

  19. Diogo Moura says:

    The TCP Port All did the trick!

    Thank you,

    Diogo Moura

  20. weznagwama says:

    thank you very much for this, the IPALL port no. was the trick for me.

  21. wirecracker@live.com says:

    I was receiving 10061 error because I changed my domain password (required by IT department), and did not update the properties of the SQL server instance. Solution in my case was to open Computer Management -> Services -> SQL Server [name] -> Properties, go to Log On tab, and update the password. I hope this helps someone.

  22. edarpab says:

    revisar que si esten arriba los servicios con services.msc en ejecutar… y hay buuscar servicios sql y darle iniciar a los dos servicios.. aclaro para el error 10061… a mi me funciono

  23. Greg McFarlane says:

    Nice trouble shoot.

    I was struggling w/ a new machine and i was sure it was the firewall.

    It was also set up to have an instance name. It would have been a while before i checked that.

    Nice job. Thanks.

    greg

  24. Prof. Dr. Said El Noshokaty says:

    Hi,

    I have followed the 9-item check list. No problem when connecting locally, the problem occurs when connecting remotely. I have added 3 more items to the check list: a) Fixing port id of default and named instances, b) fixing machine local IP address, router IP address, and alt DNS IP address, c) Port forwarding of TCP and UDP ports. Still I receive the message: 'No connection could be made …'. The string I used to connect is: 'tcp: machine-public-IP-addressMSSQLSERVER, 1433' along with SQL Server authentication (user name and password). I'm using Win 7 and SQL Server 2014 evaluation edition. The ERRORLOG reported: 'Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required'. Please advise.

    Best regards.