SQL Driver Security API Trace (SSPICLIENT)

This is a C++ MFC GUI tool that can perform a number of functions related to driver security negotiations with the server:

1. Trace the SSPI APIs while making a connection to the SQL Server.
2. Check certificates. Traces an encrypted connection, which also performs certificate validation.
3. Flush the Kerberos ticket cache for the current user.

The application GUI consists of of a single form:

Installation

Download version 2022.10.07.03 and unzip the executable into a folder, such as c:\temp. No other installation is required.

Launch by double-clicking on SSPIClient.exe in Windows Explorer. The log file (SSPIClient.log) will be written into the same folder where the executable is located. If run several times, the log will be appended to the existing file.

The application is written in C++ and should run on Windows 7 and newer operating systems. It is an x86 application, so the 32-bit version of the driver must be present as well as the 64-bit driver.

Run SSPI Connection Test

This is the primary function of this tool, to trace the SSPI security APIs used by the SQL Server ODBC Driver when connecting to SQL Server.

Note: The ODBC Driver was chosen because the ODBC API was simpler than the OLE DB API to implement. However, the ODBC drivers and OLE DB Providers all share the same basic code under the covers when it comes to the security APIs. And the same holds true for the SqlClient .NET provider.

1. By default, it will trace the SQL Server 2000 ODBC Driver that ships with Windows (Driver={SQL Server}). But if you click on the "Use latest SQL ODBC Driver (If Available)" checkbox, then it will use the latest driver it can find in the following order:

• SQL Server Native Client
• SQL Server Native Client 10.0
• SQL Server Native Client 11.0
• ODBC Driver 11 for SQL Server
• ODBC Driver 13 for SQL Server
• ODBC Driver 17 for SQL Server
• ODBC Driver 18 for SQL Server

2. Enter the server name in the box provided. This can be in a number of formats including those below:

• SQLProd01
• SQLProd01.contoso.com
• SQLProd01\Accounting
• tcp:SQLProd01.contoso.com,1455

3. Modify the log file path if you want to. Repeated tests will Append to the log file, otherwise.

4. If using SQL Credentials, click the "Use Integrated Login (Un-check For Standard)" checkbox to uncheck it and enter the user name and password in the additional boxes that appear.

5. Click the "Run SSPI Connection Test" button to start the logging. A message box will be displayed on both success or error.

6. ENCRYPT=NO is used explicitly since the ODBC Driver 18 and later have encryption turned on if the setting is not specified.

Run Client Certificate Test

This test is the same as the one above but ENCRYPT=YES is added to the connection string, so the certificate is validated in addition to the other SSPI API logs.

The additional checks test whether the server name entered matches the Subject Name or one of the Subject Alternative Names on the certificate, and whether the certificate CA root is in the trusted root store.

Flush Kerberos Tickets

This performs the equivalent of the klist purge command. Do this before the other tests if you suspect the issue might be due to a stale Kerberos ticket.