How am I blocking connections to SQL Server 2008

Question

This is a fairly specific issue, but I'm at a loss for reasons to what might be happening.

We have SQL Server 2008 running on what we'll call ServerA. We can RDP in ServerA, we can ping ServerA. If we RDP into ServerA, we can open up SQL Server Management Studio and connect to all of the database instances running on ServerA just fine.

However,

We can't connect to any of those instances from anywhere else on the network. I don't think its a network problem, because if I can RDP and ping ServerA, then the network is fine. And I don't think its a permission thing, because if I'm RDP'd in, I can connect to all the instances just fine. It's almost like I've got some secret safety setting turned on that's preventing connections from any IP other than localhost, or something like that.

Does such a safety setting exist? I wouldn't know how to get into this state even I wanted to, so I have no idea how to get out of it.

Answer 1

Make sure the SQL Browser Service is running.

Answer 2

Use the SQL Server Configuration Manager on ServerA to make sure the TCP/IP network protocol is enabled (it is disabled by default). It also needs to be enabled in the client network configuration but it is enabled there by default.

Answer 3

SQL Server allows connection via four different protocols:

• Shared Memory
• Named Pipes
• TCP/IP
• VIA

Of these, only Shared Memory is enabled if you've installed SQL Server with default settings.

See the protocols:

• RDP to your database server
• Open SQL Server Configuration Manager (SSCM): Start -> All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager
• In SSCM, expand the SQL Server 2008 Network Configuration node
• Select the Protocols for [instancename] node (instancename is the name of your database instance; mine is called SQLEXPRESS).

Shared Memory only works locally on the database server. This is likely the protocol being used when you RDP to the server and open SQL Server Management Studio (SSMS).

TCP/IP is normally used when connecting from a remote machine to the database server. TCP/IP uses ports, and the default port for SQL Server is 1433. If you have a firewall installed on your database server, you may need to open this port.

Enabling the TCP/IP protocol:

• Double-click the TCP/IP protocol in the right pane
• Change the Enabled option to Yes and click OK
• You will need to restart the SQL Server service

Restarting the SQL Server service:

• Select the SQL Server 2008 Services node
• Right-click the SQL Server([instancename]) service and choose Restart

---

You should now be able to connect SSMS from remote machines to SQL Server running on your database server.

Answer 4

Is the firewall blocking port 1433?