0

I'm having problems connecting to my SQL Server 2008 database from my computer. I have enabled remote connections as described in this answer (How to enable remote connections for SQL Server 2008?). And I have added the ports listed on the microsoft support page to our Cisco Asa firewall and I'm still unable to connect.

The error I'm getting from the SQL Management Studio is:

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: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

Once again, I have double and triple checked that remote connections are enabled under the database properties and that TCP is enabled on the configuration page. I've added tcp ports 135, 1433, 1434, 2382, 2383, and 4022 as well as udp 1434 to the firewall. I've also checked to make sure that 1433 is the static port that is set in the tcp section of the database server configuration. The ports should be configured correctly in the firewall since http/https and rdp are all working and the sql server ports are setup the same way.

What am I missing here? Any help you could offer would be greatly appreciated.

Edit: I can connect to the server via TCP on the internal network. The servers are colocated in a datacenter and I can connect from my production box to my development box and vice versa. To me that indicates a firewall issue, but I've no idea what else to open. I've even tried allowing all tcp ports to that server without success.

Community
  • 1
Timothy Strimple
  • 125
  • 1
  • 6
  • Sounds like you've got it all covered as far as normal troubleshooting goes. Is it a named instance, or a default instance? Changing tyhe ports to static ports is different for named instances than default instances, if I remember correctly. – Mark Henderson Jul 26 '09 at 23:18
  • I've got the same behavior on two server. My dev server uses a named instance, and the prod server uses a default instance. I've a feeling this is something very simple that I'm missing. – Timothy Strimple Jul 27 '09 at 01:33
  • I've got to add these links to in the comment instead of the question because I can only have one link and I can't have images. -_- Here is a screenshot of my relevant sql server settings. http://tmp.stayhealthy.com/sqlsettings.png And my firewall rules http://tmp.stayhealthy.com/firewall.png – Timothy Strimple Jul 27 '09 at 01:40
  • By the looks of it the port setup for SQL looks correct for static ports. The only thing I can suggest from here is to load up Wireshark on the SQL server and see if you can spot the connection reaching the box. If you don't even see any SQL authentication traffic coming into the boxes, then it's a firewall issue. – Mark Henderson Jul 27 '09 at 02:06
  • Can you confirm that the SQL Browser service is running? Also is this Express edition or Standard? – Wayne Jul 26 '09 at 23:11
  • This is Standard, and the SQL Browser IS running. – Timothy Strimple Jul 26 '09 at 23:14

2 Answers2

1

Check with your CoLo provider, they may be blocking access to port 1433. Many CoLos started doing this after the SQL Slammer went around a while back.

It's also recommended that you not try and access SQL Server's directly over the internet. Instead setup a VPN between the CoLo and your office and access the servers over this secure connection instead of over the public internet.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • The CoLo must be blocking that port. I changed the server to listen on 1533 instead of 1433 and it worked right away. I have access restricted to the SQL server based on an IP list. So only our developers, with their ip address added to the firewall will be able to access the sql server. I'll leave whether that, or vpn access is better in another question. – Timothy Strimple Jul 27 '09 at 09:58
0

I found this MSDN tutorial helpful in solving the problem: http://msdn.microsoft.com/en-us/library/ms345343.aspx

It walks through all aspects of configuring SQL Server 2008 on Windows Server 2008, including details of which IP to set to be static (IPAll) and using the "tcp:" prefix when specifying the server name.