1

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.

Improve this question
5
  • Where are the server and the client (in this case SSMS) relative to each other? In your test, are they on the same machine or different? If different, is the port on the server open to traffic from the client (i.e. no firewall blocking that traffic)?
    – Ben Thul
    Feb 9, 2013 at 21:41
  • @BenThul: I'm testing both scenarios. SSMS is running on the server as well as on a client connected via Hamachi. To my knowledge there's no firewall blocking the connection, but I can investigate that variable once the connectivity is confirmed within the scope of the same machine.
    – David
    Feb 9, 2013 at 21:44
  • 1
    So it's failing when the client and the server are on the same machine. Hmm... is the SQL Browser service running? Can you connect with a connection string like "servername,port"?
    – Ben Thul
    Feb 9, 2013 at 21:46
  • @BenThul: Looks like the Browser Service was what I needed. It was disabled, and enabling/starting it allowed me to connect from SSMS. I then tested connecting from the remote client, which was still failing. Temporarily disabling the Windows Firewall for private networks on the server fixed that. So it looks like I have some firewall configuration to research. Thanks!
    – David
    Feb 9, 2013 at 21:55
  • Great! I'm going to add this as an answer for those who come after.
    – Ben Thul
    Feb 9, 2013 at 21:57

2 Answers 2

Reset to default
2

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.

Improve this answer
0

Make sure the browser service is started and make sure the firewall is allowing inbound access for TCP and UDP on ports 1433 and 1434. See this article for more details.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .