Skip to content

SQLCHECK

Jump to bottom
Malcolm Stewart edited this page Apr 13, 2021 · 51 revisions

SQL Connectivity Check (SQLCHECK)

This is a command-line tool that will examine the current computer settings and service account settings and produce a text report to the Console window that is useful in determining why a connection may be failing. Normal usage is to redirect the output to a file, e.g.

Usage

     SQLCHECK > c:\temp\SQLCHECK.LOG

Installation

This is a C# application that requires the full .NET 4x framework on Windows.
Download SQLCHECK.EXE to a folder of your choice. It is ready to use.
Note: It requires an Administrative command-line (to read the SQL ERRORLOG file).

Disclaimers

Note: This tool reads local system settings; it does not make any changes.
Note: This tool does not save the report anywhere. You must redirect the output to a file.
Note: This tool does not communicate with any external systems.
Note: This tool does not make a connection to SQL Server, IIS, or other application services.
Note: This tool DOES make some LDAP queries regarding service account properties and to determine related domains that may be used in a cross-domain database connection.

For best results, run this tool on the SQL Server and on the client machine.

Reports

The report is intended mainly to illuminate issues that can result in consistent connection failures.
It can also be useful in identifying client and server properties for other issues, as well.

The report is divided into three main sections:

  • Computer Report
  • Client Security and Driver Report
  • SQL Server Report

The Computer Report

This report lists information about the local computer system, such as:

  • Computer name
  • Windows edition
  • Windows version
  • .NET Framework versions (not .NET Core)
  • Computer role and whether it is part of a domain
  • Information on the current domain
  • Related domains and trusts and whether Selective Authentication is in use
  • Are there any DNS aliases for this computer
  • Are the Program Files or Common Files folders located on a drive other than C:
  • Is there any low disk condition
  • General network settings, such as TCP Chimney, RSS, and the Ephemeral Port range
  • Network adapters and their properties and offloading settings

The Client Security and Driver Report

This report lists information regarding the local computer general security settings, such as DisableLoopbackCheck and the allowed Kerberos encryption algorithms, TLS settings, and SQL Server and non-SQL Server drivers are installed, SQL Aliases and client protocols that are set on the local computer.

  • Is CrashOnAuditFail being used
  • DisableLoopBackCheck and BackConnectionHostNames
  • Max kerberos token size
  • Kerberos local encryption algorithms in use
  • What versions of TLS are enabled and disabled in the registry
  • What is the cipher suite protocol order (Policy overrides Registry, if present)
  • Are Diffie-Hellman cipher suites enabled and what algorithm version is being used
  • SQL Server OLE DB Providers and ODBC drivers and their capabilities
  • Other OLE DB Providers and ODBC Drivers
  • SQL Aliases and where they point
  • SQL client driver SNI settings

The SQL Server Report

This report lists SQL Services installed on the computer and other services of interest, such as MSDTC, and IIS. It also lists service account properties and what Service Principal Names are defined and suggested SPNs for SQL Server.

  • Services of interest
  • Service account properties including whether it is trusted for delegation or used for constrained delegation
  • Constrained delegation SPNs for each, if any
  • SQL Server instances (SQL 2005 and later; SQL 2000 instances aren't shown)
  • Details for each SQL Server database engine
  • Suggested Service Principal Names for each instance and whether there are duplicates or the SPN is on the wrong service account
Clone this wiki locally