Enable remote connections for SQL Server Express 2

Question

I just installed SQL Server Express 2012 on my home server. I'm trying to connect to it from Visual Studio 2012 from my desktop PC, and repeatedly getting the well-known 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

What I've done to try to fix this:

• Run SQL Server Configuration Manager on the server and enable SQL Server Browser
• Add a Windows Firewall exception on the server for TCP, ports 1433 and 1434 on the local subnet.
• Verify that I have a login on the SQL Server instance for the user I'm logged in as on the desktop.
• Verify that I'm using Windows Authentication on the SQL Server instance.
• Repeatedly restart SQL Server and the whole dang server.
• Pull all my hair out.

How can I get SQL Server 2012 Express to allow remote connections!?

Answer 1

All you need to do is open the relevant port on the server's firewall.

Answer 2

I prefer way of "Rosdi Kasim" as that's doesn't require detail configuration on the IP.

I will definitely forget it again when I try to up another server again.

Keep It Simple Stupid (KISS) by simply enable the Sql Server Browser service, then add the \SQLEXPRESS behind the IP when you connect the server.

Direct using IP without "\SQLEXPRESS" was my point of failure as it doesn't use the default port.

Thanks.

Answer 3

I had to add port via Configuration Manager and add the port number in my sql connection [host]\[db instance name],1433

Note the , (comma) between instancename and port

Answer 4

One More Thing...

Kyralessa provides great information but I have one other thing to add where I was stumped even after this article.

Under SQL Server Network Configuration > Protocols for Server > TCP/IP Enabled. Right Click TCP/IP and choose properties. Under the IP Addresses you need to set Enabled to Yes for each connection type that you are using.

Answer 5

You can also set

Listen All to NO

in the protocol dialog then in the IP address IP1 (say)

set enabled to Yes,

define yr IP address,

set TCP Dynamic to Blank and

TCP port to 1433 (or whatever)

Answer 6

In my case the database was running on non standard port. Check that the port you are connecting is the same as the port the database is running on. If there are more instances of SQL server, check the correct one.