Cannot connect to SQL Server, error: 0 - No Connection could be made because the target machine actively refused it

Question

I'm new to SQL related matters so please bare with my lack of knowledge and asking a question which has been asked countless times before.

I have to connect to a SQL database server which is located at a remote location at xxx.xx.xxx.xx:3306, but every time I try to connect I get the same error:

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

I get this same error whether I try to connect using SQL Server Management Studio or in a C# program via:

 SqlConnection myConnection = new SqlConnection(...)
 myConnection.Open();

I've tried it on two different computers, both on the same home network. I've had a look at the SQL server configuration manager on the computer which I installed SQL Server on, enabling TCP/IP and fiddling with the port values settings, but I'm guessing this is just for configuring an SQL server on my computer and irrelevant to connecting to a remote one. I shouldn't even need to install SQL server to do queries on a remote server anyway, right?

I've turned off my Windows firewall and my router firewall, though ShieldsUP still says

Port: 3306 Status: Stealth

Your system has achieved a perfect "TruStealth" rating. Not a single packet — solicited or otherwise — was received from your system as a result of our security probing tests. Your system ignored and refused to reply to repeated Pings (ICMP Echo Requests). From the standpoint of the passing probes of any hacker, this machine does not exist on the Internet. Some questionable personal security systems expose their users by attempting to "counter-probe the prober", thus revealing themselves. But your system wisely remained silent in every way. Very nice.

I read that this is irrelevant as well, since I'm not the one hosting the server, but when I was given the address, I was given the port as well, so I must have to do something with it. I'm just not sure what. My understanding of ports really is quite shaky.

I've been trying this for over a day now, and I can't think of anything more I can do.

EDIT: I fixed the problem. I had to use MySQL, not MSSQL. Doh. Sorry guys.

In SSMS, did you enter the port as part of the server name? In other words did you type: xxx.xx.xxx.xx:3306 in the name of the server to connect to? — Stuart Ainsworth
Where in SSMS should I enter that? I was under the impression that I wouldn't need to install SQL server just to query a remote database. — Dominic
FYI: "SSMS" means "SQL Server Management Studio". Us old hands frquently forget that the acronym is not self-explanatory. — RBarryYoung
Sorry, I misread that as SSCM for some reason. Yeah, when I try entering xxx.xx.xxx.xx:3306 I get a "No such host is known" error. They gave me a code sample in python for the connection parameters, it's just: MySQLdb.connect('xxx.xx.xxx.xx', 'username', 'pw', 'database'); — Dominic

Aaron Bertrand Aaron Bertrand · Accepted Answer · 2012-07-10T12:56:22

If you are sure the remote SQL Server is running on port 3306 (otherwise I'm not sure why you talked in the question about that port specifically), try the following connection string:

user id=username; password=password;
data source=123.45.678.90,3306; 
initial catalog=dbname; 
Network Library=dbmssocn;

I think without the port you'll have trouble if the other end has disabled the SQL Browser service. You should also make sure it isn't a named instance, in which case you may need:

user id=username; password=password;
data source=123.45.678.90\InstaneName,3306; 
initial catalog=dbname; 
Network Library=dbmssocn;

Cannot connect to SQL Server, error: 0 - No Connection could be made because the target machine actively refused it

3 Answers