Skip to content

0720 Advanced SSL Data Capture

Jump to bottom
Malcolm Stewart edited this page Mar 18, 2024 · 10 revisions

Advanced SSL Data Capture

Note: This article is for Windows only.

NOTE: Consistent Authentication errors are usually due to bad settings, while intermittent failures are usually due to a dropped connection or some performance/timeout issue.

Note: We recommend turning file extensions on in the Windows File Explorer.

Capturing Windows Settings using SQLCheck.exe

To be done on both the client and server machines and any other related systems, such as a web server or SQL Server linked server intermediate machine.

  1. Download SQLCheck from the installation link on the page below. Unzip into a folder, such as C:\MSDATA.
  2. Double-click the executable in Windows File Explorer. The report will be written to the folder where SQLCheck.exe is located.

SQLCheck

Initial Setup of SQLTrace.ps1

Download SQLTrace.zip from the installation link on the page below and extract into a folder, such as C:\MSDATA.

SQLTrace

There will be two files, SQLTrace.ps1 and SQLTrace.ini. The INI file is used to configure what will be captured.

Configure the Driver Built-In Diagnostic (BID) Trace

  1. Open SQLTrace.ini in Notepad and go to the BID Trace section.
  2. Make sure that BIDTrace=yes is set.
  3. Make sure you enable the BIDProviderList that conforms to the driver your application is using.
    3.1. The built-in System.Data.SqlClient .NET drivers are auto-enabled.
    3.2. If these aren't the drivers your application is using, then comment this line using the # character and uncomment one of the others, such as the ODBC section or the OLEDB section.
    3.3. If you aren't sure, ask the DBA or application developer, or use the 4th BIDProviderList, which encompasses all drivers currently in use.
  4. Save the file.

Configure the Network Trace

The networking section is auto-configured with Network=yes and NETSH=yes. These should not be changed without good reason.

If you are tracing a local connection, make sure the application is using TCP/IP and not Shared Memory or Named Pipes. Install and use WireShark for the network capture as it supports LoopBack captures.

WireShark also captures VPN traffic quite well.

Save any changes.

Configure the Authentication Traces

The Auth section is auto-configured with Auth=yes and a number of other settings.

You may also want to set FlushTickets=yes in the MISC section. This will flush Kerbreos tickets for all users and services on the machine.

Save any changes.

Enabling BID Traces

Once all the changes to the SQLTrace.ini file have been saved, BID Traces must be enabled before tracing can begin.

  1. Open an Admin PowerShell prompt.
  2. CD to the SQLTrace folder, e.g. CD \MSDATA
  3. Run: .\SQLTrace.ps1 -setup
  4. Restart the service or application you wish to trace or the application will not be traced.

Collect the Trace Data

Note: Make sure the previous steps have been completed on all machines before continuing.

  1. Open an Admin PowerShell prompt on all machines being traced. Complete the starting steps on all machines before reproducing the issue.

  2. CD to the SQLTrace folder, e.g. CD \MSDATA

  3. Run: .\SQLTrace.ps1 -start

  4. When the command prompt appears, reproduce the issue.

  5. To stop the trace, run: .\SQLTrace.ps1 -stop

The trace may take a minute or two to completely stop since downloading the event logs may take a while.

You can start and stop the trace any number of times without having to redo the configuration steps. Each time it is taken a new folder will be created with a timestamp as part of the folder name. The time corresponds with the time the trace was started.

NOTE: If engaging Microsoft support from this point, zip the output of the entire directory and upload to the share provided by your engineer for further review.

Clone this wiki locally