SQL

Troubleshooting general SQL connectivity issues

Take a look at the work in progress checklist in troubleshooting general SQL connectivity issues. We look at SPN, MSDTC, SQL and more...

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 SPNssetspn -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

sqltrouble02

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

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.

sqltrouble01

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.

Subscribe to VirtualizationHowto via Email 🔔

Enter your email address to subscribe to this blog and receive notifications of new posts by email.



Brandon Lee

Brandon Lee is the Senior Writer, Engineer and owner at Virtualizationhowto.com, and a 7-time VMware vExpert, with over two decades of experience in Information Technology. Having worked for numerous Fortune 500 companies as well as in various industries, He has extensive experience in various IT segments and is a strong advocate for open source technologies. Brandon holds many industry certifications, loves the outdoors and spending time with family. Also, he goes through the effort of testing and troubleshooting issues, so you don't have to.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.