Skip to content

SQLCHECK

Jump to bottom
Malcolm Stewart edited this page Mar 14, 2024 · 39 revisions

SQL Connectivity Settings 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. Just type the name of the executable into an Admin command console (cmd.exe) or double-click the executable in Windows Explorer (you will be prompted to run as Administrator).

Usage

     SQLCHECK

This will create a file in the current folder with the following format: SQLCheck.computername.datetimestamp.txt
e.g. SQLCheck.SQLPROD01.20221212150526.txt

Installation

This is a C# application that requires the full .NET 4x framework on Windows.
Download SQL_CHECK.ZIP and extract SQLCHECK.EXE to a folder of your choice, e.g. C:\TOOLS. It is ready to use.

Download version 1.0.1316.0

Disclaimers

Note: This tool reads local system settings; it does not make any changes.
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 saves the report in the same folder as the application.
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. If using a web-based app, please run on the web server, as well.

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 the Program Files or Common Files folders located on a drive other than C:
  • Is there any low disk condition
  • Filter drivers and network mini drivers.
  • 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.

  • Certificates in the certificate store, their attributes, and whether they are suitable for SQL Server to use.
  • 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, including the certificate thumbprint
  • Suggested Service Principal Names for each instance and whether there are duplicates or the SPN is on the wrong service account

Sample Report

The sample report is representative of what is collected. It may not show the latest fixes and features.

Sample Report

Clone this wiki locally