Troubleshooting general SQL connectivity issues

There are quite a few things that an administrator may need to check when it comes to making sure connectivity to SQL is happy and healthy in your respective environments.  As of late, I have put together a checklist of sorts that for me has been very helpful in troubleshooting general SQL connectivity issues.  I wanted to share this with you guys so that it can be helpful to others besides myself.  Hopefully this will save you some troubleshooting time if you are presented with various SQL connectivity issues including but not limited to issues with SPNs, MSDTC, and SQL connectivity.

SPN Setup

• Check SPNs – setspn -L DOMAINserviceaccount
• On service accounts that have SPNs attached to them: check the accounts in Active Directory and make sure they are allowed for delegation.
  • This is configured in Active Directory through the delegation tab
  • The tab is only present on user accounts that have SPNs attached to them
• We have determined that the entries need both an FQDN for the server with 1433 and one without 1433.
  • e., MSSQLSvc/yourserver.fqdn.com & MSSQLSvc/yourserver.fqdn.com:1433

Autotuning Settings that seem to affect SPN and general SQL connectivity:

If you are dealing with SQL connectivity between a new OS (for me this is anything 2008 and higher) or legacy OS (Windows 2003 and under).  There are various issues that can present because of the way the TCP/IP stack was tweaked in Windows 2008 and higher with the autotuning settings.

These steps are only recommended if you have interoperability between legacy and newer server OS’es.

• netsh int tcp show global (recieve window auto-tuning level should be disabled)
• netsh int tcp set global autotuninglevel=disabled
• The following may be needed also: netsh int tcp set global rss=disabled – However, the autotuninglevel seems to be the the magic value.

DNS

DNS settings are critical for SPNs to work correctly.  If hosts are not resolved exactly as the SPNs are registered you will have issues.  General DNS configuration steps:

• For IPv4 DNS, make sure you have all the DNS suffixes in the search order if you have an environment with multiple domains (parent, child domains, etc.)
• Hosts file: c:windowssystem32driversetchosts should ideally be blank. However, with earlier name resolution issues, you may find that on servers that have been in production for a while, host entries may exist. If troubleshooting SPN errors and such it is always good to check what is contained in the hosts file (hopefully nothing but you may be surprised.)

MSDTC

See the MSDTC troubleshooting post here.

• MSDTC settings control the security of whether or not you can perform distributed transactions both incoming and outgoing.
• Also, sets the security account on the DTC Logon Account properties.
• Use the dcomcnfg.exe utility to configure MSDTC settings
• The settings that must be enabled are:
  • Network DTC Access
  • Allow Remote Clients
  • Allow Inbound
  • Allow Outbound
  • No authentication required
  • DTC Logon Account

Troubleshooting MSDTC – The following SQL queries help troubleshoot MSDTC –  (1) Simulates a distributed transaction  (2) Helps determine users involved in authentication

BEGIN DISTRIBUTED TRANSACTION
SELECT top 1 ID FROM <linkedServer>.<DatabaseName>.dbo.TestTable
ROLLBACK TRANSACTION

• See this post for the query to see which users are authenticating with remote servers (linked servers, etc).
• Make sure you have firewall exceptions in place for the Distributed Transaction Coordinator.  If you look at firewall.cpl you will see the DTC listed in the available firewall exceptions.  Make sure the firewall profile that you think applies is the one that is actually applying.  See how you can tell the difference between what the various firewall profiles apply to.

Keep in mind also that if you are dealing with a SQL cluster setup, MSDTC is a clustered resource and needs to be administered as such.  The service will be stopped/started as any other resource.  Also, the MSDTC security settings will be set not on the Local DTC settings but on the clustered MSDTC instance.

SQL Connectivity itself

Make sure of the obvious things but this is a short list:

• Make sure the SQL service is running
• Make sure your SQL instance is set to a static port – typically TCP 1433, usually set in the “surface area config” or the configuration manager (older and newer terminology).
• Make sure you have firewall exceptions allowed for TCP 1433
  • An easy way to do this is with the netsh command in an elevated command prompt like so:

netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip=192.168.1.0/24,192.168.2.0/24,192.168.3.0/24 profile = DOMAIN

• Make sure remote connections are allowed on your SQL server

This is an easy one really – simply open the SQL management studio, right-click on the server and go to properties.  Make sure the Allow remote connections to this server is checked.

Final Thoughts

The above checklist are some of the points to check in troubleshooting general SQL connectivity issues.  There may be others in your environment depending on the network topology and layout of the servers, so as always tweak the list according to your architecture.