Debugging SQL Connectivity 101

  • Article

Note to self: Things to remember to check next time I can't connect to SQL...

1. Is SQL started? Yes. Is the instance I'm trying to connect to started? Yes.

2. Run netstat -aon on sql box, do I see something bound to 1433 or say my instance port 5555? For example do I see 0.0.0.0:1433 bound? Does the pid match with pid for SQL Server?

3. If I run > telnet servername 1433 <, does this connect? I usually try this one first it is easy and tells you if you have a firewall problem right away. Oh Windows Firewall, you have made my life in SQL Protocols such fun.

4. Try the osql connection test using hard coded protocol and port:

      osql -E -Stcp:mysql,5555

5. Hey, try the admin port, does this work? Why does admin port not work with osql? We call this progress my friend.

   sqlcmd -E -Sadmin:127.0.0.1

6. Maybe I have a machine with IPv6?

      sqlcmd -E -Sadmin:::1

Not even sure if this works, but ::1 is loopback for ipv6, good to know.

7. Did I check SQL SAC? The SQL Server Surface Area Configuration tool can totally block remote connectivity, go check it.

8. Is the SQL Browser service started? Run -> net start sqlbrowser. This service helps the client locate the proper port for named instances.

9. Did I change the port for my default instance from 1433 to something else? Client is not smart enough to figure this out sometimes. Check the ERRORLOG and see what port SQL is binding to.

10. Is IPSec services enabled? This can break connectivity within a domain. Run -> net start policyagent. 

11. Check SQL Server Configuration Manager and determine what ports you are really binding to. Please forgive us for the funky GUI here, I agree it is confusing. Read it carefully is my best advice. I find it easier to look at the ERRORLOG to be honest that this GUI.

12. Am I using a clustered instance of SQL? With clustered SQL, the only thing that might throw you off is that you can connect to the virtual ip as well as locally bound ips. It can get confusing. Avoid the local ips and use the clustered ips you see in Cluster Admistrator.

13. Did someone install a virus scanner on your SQL box recently? Maybe it decided to block SQL from talking to the internet by blocking all sockets. Oh yes I've seen this many times.

14. Running any unusual xprocs in SQL lately? I have seen xprocs close our socket handles in the past, CloseHandle is a dangerous API, it does not preclude you from putting in some random value that is somebody else's handle.

15. Rootkits. Did you know that a rootkit can piggyback the same ports you use for normal applications such as IIS and SQL and use these ports while you are also using these ports for normal activities? And everything works fine for both your app and the rootkit? This is exceedingly rare however, but does make a good case for regular network sniffing and intrusion checking.

I'm sure I can think of more of these, later!