SQL Server 2008: No connection could be made because the target machine actively refused it

An old problem which still causes me some trouble now and again.

So I got the following error:

No connection could be made because the target machine actively refused it. Microsoft SQL Server Error 10061

So first thing to check is wether or not the server is set up to accept remote connections.

1. Open up SQL Server Management Studio (SSMS).
2. Right-click on the server instance, select Properties…
3. Click on the “Connections” option on the left side.
4. Verify that the “Allow remote connections to this server” option is checked.

If it was not enabled, you might try to restart the sql server service by running “NET STOP mssqlserver” and “NET START mssqlserver” in a command window. You need to restart the service for any configuration changes to take effect.

Second thing you might check is if the server has enabled TCP/IP.

1. Open SQL Server Configuration Manager.
2. Expand the SQL Server Network Configuration node.
3. Make sure that TCP/IP is enabled.

Remember again that you must restart Sql Server for changes to be effective.

Final thing is to go into the Surface Area configuration and enable the functionality you need. This used to be a separate configuration tool in SQL Server 2005, but now it is integrated into SSMS.

1. Open up SQL Server Management Studio (SSMS).
2. Right-click on the server instance, select Facets…
3. Select the “Surface Area Configuration” facet from the dropdown at the top.
4. You may at least want to set the “AdHocRemoteQueriesEnabled” to true, and maybe some of the other options as well.

Remember to restart SQL Server.