Sql-server – Connecting to MS SQL 2012 with TCP/IP

sql servertcpipwindows-server-2012

I'm trying to enable TCP/IP connections on a SQL Express 2012 database, and it seems as though I'm missing a step somewhere. To enable it, I:

  • Opened SQL Server Configuration Manager
  • Selected "Protocols for [instance name]"
  • Double-clicked on TCP/IP
  • Set Enabled to Yes
  • For testing purposes, set Listen All to Yes

Also, for good measure, connecting to the server in SQL Server Management Studio (using [default] protocol, not TCP/IP explicitly), made sure Allow remote connections to this server is checked.

Then to test it, I tried to create a new connection in SQL Server Management Studio. Same user, same everything. The only difference is that on the advanced settings I set the protocol to TCP/IP. However, it doesn't connect. Just results in the standard "server was not found or was not accessible" error.

My goal is to be able to connect via SQL Management Studio as well as Visual Studio from a workstation which uses Hamachi to provide a private connection to the server. Naturally, the workstation gets the same error, since it seems that it doesn't yet work locally on the server.

Is there something else I should check which might be blocking the connection? I'm not well versed at all in Windows server configuration and maintenance, so there could very well be basic or obvious steps which I've entirely overlooked.

Best Answer

The SQL Browser service is kind of like DNS for SQL Server. Specifically, it resolves instance names to port numbers. If yours isn't on and the service isn't running on the default port (1433), SQL won't know how to connect to your instance. So check that UDP 1434 (the port that the Browser communicates on) is open and that TCP traffic to whatever port your SQL Server is configured for is also open.

Related Topic