Skip to content

SQLTRACE

Jump to bottom
Malcolm Stewart edited this page Aug 17, 2022 · 51 revisions

SQL Trace (SQLTRACE)

This is a command-line PowerShell script that will collect Network traces, BID traces, and Auth traces on the current computer to capure low-level logs while a problem is occurring to provide insight into underlying causes. It is a complementary tool to SQLCheck, which collects static settings. While SQLCHECK can be run at any time, SQLTRACE needs to be run prior to issues occurring and left running until the issue occurs to collect log files. Progress entries are written to the PowerShell window and also to SQLTrace.log in the output folder, into which all the other logs are also written.

Installation

This is a PowerShell script that drives built-in Windows data collectors (LOGMAN and NETSH) and must be run on Windows.
Download SQL_TRACE.ZIP and extract SQLTrace.ps1 and SQLTrace.ini to a folder of your choice, e.g. C:\MSDATA.
Note: It requires an Administrative PowerShell command-prompt in order to run. It will not run in CMD.EXE.

Download version TBD

What is Collected

SQLTrace is driven by settings in the SQLTrace.ini file. It collects the following items by default. Changing the INI file can enable or disable specific items for capture.

  • NETSTAT at the start and end of the trace.
  • TASKLIST at the start and end of the trace.
  • NETSH / LOGMAN network capture.
  • Driver traces for 64-bit .NET apps using System.Data.SqlClient.
  • Auth traces, including Kerberos, LSA, SSL, NTLM.
  • Windows Event logs for the last 24 hours, including Application, Security, System, CAPI2, and Kerberos.

INI Configuration

The default SQLTrace.ini file is given below. It is divided into 3 parts: BID Trace, Network Trace, and Auth Trace.

################## BID TRACE ##############
##
## Only traces on Windows
## Does not trace JDBC Drivers
## Does not trace .NET Core Microsoft.Data.SqlClient
## For more information on BID Tracing, including Linux, see: https://github.com/microsoft/CSS_SQL_Networking_Tools/wiki/Collect-a-SQL-Driver-BID-Trace
##

BIDTrace        = Yes                                   # Controls the overall category of collection
BIDWOW          = No                                    # No | Only | Both     (whether 32-bit apps are traced)
BIDProviderList = System.Data System.Data.SNI

# BIDProviderList = OLEDB SQLOLEDB DBNETLIB MSOLEDBSQL MSOLEDBSQL19                         # These are the currently supported OLE DB Providers
# BIDProviderList = ODBC SQLSRV32 DBNETLIB MSODBCSQL11 MSODBCSQL13 MSODBCSQL17 MSODBCSQL18  # These are the currently supported ODBC Drivers
  • This section is turned on or off by the BIDTrace=Yes|No line.
  • 32-bit apps can be traced via the BIDWOW line. No=64-bit, Only=32-bit, Both=32-bit and 64-bit.
  • What specific drivers are traced is controlled by the BIDProviderList. This is space-delimited. There are 3 versions of this line - two are commented out, but all three can be active at a time. You can customize or combine the lines to include only the providers you want, mixing and matching from multiple lists, or create an additional line with your own provider list.
################## NETWORK TRACE ##########
##
## Only traces on Windows
## For more information on network tracing, see: https://github.com/microsoft/CSS_SQL_Networking_Tools/wiki/Collect-a-Network-Trace
##

NETTrace   = Yes      # Controls the overall category of collection
NETSH      = Yes      # Default for Windows; built-in
NETMON     = No       # Must be installed separately
WIRESHARK  = No       # WireShark can see VPN traffic, must be installed separately
Pktmon     = No       # Windows 2019 and later, Windows 11 and later, Windows 10 builds equivalent to Windows 2019.
  • NETTrace controls the overall section.
  • Specific network trace providers can be enabled on the following lines. We recommend NETSH except for special circumstances.
  • Pktmon is not currently implemented.
  • NETMON and/or WireShark must be installed separately in order to be used.
  • Selecting more than one network trace provider is allowed, but not recommended because of performance and storage issues.
################## AUTH TRACE #############
##
## Equivalent to parts of the Directory Services Auth trace script, but more focused
##

AUTHTrace      = Yes    # Controls the overall category of collection
ssl            = Yes    # Certificate logs
credssp_ntlm   = Yes    # NTLM logs
KERBEROS       = Yes    # Kerbberos logs
LSA            = Yes    # Local security authority logs
EventViewer    = Yes    # Application, System, and Security event logs
  • AuthTrace controls the overall section.
  • Various security providers can be individually enabled or disabled, but this is not recommended.
  • The EventViewer setting collects the last 24 hours of various event logs.

Usage

SQLTrace is driven by settings in the SQLTrace.ini file. The downloaded INI file is configured to capture a network trace using NETSH, Auth traces, and 64-bit driver traces for Windows Full Framework .NET drivers for SQL Server (System.Data.SqlClient).

   .\SQLTrace.ps1 -Help
   .\SQLTrace.ps1 -Setup [-INIFile SQLTrace.ini]
   .\SQLTrace.ps1 -Start [-INIFile SQLTrace.ini] [-LogFolder folderpath]
   .\SQLTrace.ps1 -Stop [-INIFile SQLTrace.ini]
   .\SQLTrace.ps1 -Cleanup [-INIFile SQLTrace.ini]
  • Help lists the above usage.
  • Setup is required once before taking BID traces. And application you want to trace must be restarted if it is a web site or a service app or other type of long-running applications. Applications, such as SSIS, which launch a new process for every job, do not need to be restarted.
  • Start begins tracing what is controlled by the INI file. You can optionally specify an alternate INI file. A folder will be created in the current directory with a time-stamp-generated name unless you specify otherwise.
  • Stop terminates the traces. This may take a while as NETSH can take 2-5 minutes to stop and organize the traces. DO NOT terminate the command Window as traces will continue running in the background.
  • Cleanup removes the BID Trace registry keys. This step is optional and will not affect application performance if they are left in place.

Disclaimers

Note: This tool does not trace .NET Core drivers, JDBC Drivers, or anything on Linux or MAC operating systems.
Note: This tool captures various logs. It does not change any system settings.
Note: This tool does save the log files to the current folder or a specified folder.
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.

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

Clone this wiki locally