Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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 '13 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 '13 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 '13 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 '13 at 21:55
    
Great! I'm going to add this as an answer for those who come after. –  Ben Thul Feb 9 '13 at 21:57
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.