SQLNA
This is a command-line tool that will read a network packet capture file or a series of chained files and produce a report highlighting potential areas of interest.
SQLNA captureFile [/output outputFile] [[/sql ipaddress,port]...] [/listConv] [/filterFmt NETMON | WireShark | Auto]
Examples:
SQLNA c:\temp\mytrace.cap ... writes to c:\temp\mytrace.log
SQLNA c:\temp\mytrace.pcap ... writes to c:\temp\mytrace.log
SQLNA c:\temp\mytrace.pcapng ... writes to c:\temp\mytrace.log
SQLNA c:\temp\mytrace*.cap /output c:\temp\trace.log
SQLNA c:\temp\mytrace.cap /sql 10.0.0.2,1433 ... hints at SQL Server
Supported file formats: NETMON 2.x, PCAP, PCAPNG, ETL. (ETL files must end with .ETL)
Supported link types: Ethernet, Wi-Fi, NDIS, Linux Cooked Capture
Supported protocols: IPV4, IPV6, GRE, ERSPAN II & III, VNETTag, 802.1Q, TCP, UDP, SMP, SSRP, TDS, KerberosV5, DNS
Note: SQL Network Analyzer only analyzes TCP traffic, not Named Pipes or Shared Memory. The client and server need to be on different machines for conversations to show in the network trace.
This is a C# application and requires the full .NET 4.x framework on Windows. Download the SQLNA.EXE and SQLNAUI.EXE executables to a folder of your choice. It is ready to use.
SQL Server Network Analyzer UI provides a graphical interface to select files for parsing and setting options. Download SQLNAUI.EXE into the same folder as SQLNA.EXE. It is ready to use and will build the command-line and execute SQLNA.EXE from the GUI interface.
- Listing all files read including the beginning and ending timestamps found in each file to help line up with other logs.
- Listing the amount of SQL Server traffic found compared to overall TCP traffic.
- Listing all SQL Servers found, regardless of port number, along with statistics about each server.
- Listing all Domain Controllers found and how many DNS, Kerberos, LDAP, and MSRPC conversations are seen for them.
- Optional, off by default: Listing statistics about all successful connections that execute queries.
- Listing all SQL conversations that end with a reset connection, along with statistics.
- Listing all SQL conversations that do not complete a login, or where the LoginAck comes after the connection was closed.
- Listing all connections to the DC that are not responded to.
- Listing all Named Pipes connections found.
- Listing all SQL conversations that had an Attention packet.
- Listing all SQL conversations that did not use TLS 1.2 and whether requested by the client or forced by the server.
- Listing all SQL conversations that were redirected (via Application Intent = readonly or the Azure DB Gateway) to another server.
- Listing ephemeral port usage.
- Listing SQL Browser traffic.
- Listing failed Kerberos SPN requests.
- Listing failed DNS requests.
- A CSV file with statistics on each TCP and UDP conversation that you can filter and pivot in Excel.
- A diagnostic file containing the console window output plus additional information.
This report displays the version of SQL Network Analyzer, the command-line used to run the tool, and the date on which the analysis was performed.
This report displays all the files included in the report, including the start and end time of each file, and the file size. In later reports, the Files column shows which file the conversation starts and ends in to make it easier to locate the beginning or end of the conversation for manual analysis.
Note: Files are ordered by the timestamp of the frames in the file, not by filename and not by the file's timestamp. If the last file of a chained NMCAP capture was terminated improperly, the frame table won't be written and it may appear first in the list.
WARNING SQL Network Analyzer loads all files into memory to perform the analysis. The size of all files should not exceed RAM * 0.8. If you have a large number of chained files, you can partition them in subfolders and analyze each group separately.
While SQL Network Analyzer can read files larger than 2GB, but many analysis tools cannot. To prevent problems and to improve filter performance when performing a manual analysis, generate chained captures of between 100MB and 200MB.
This report shows the total amount of TCP traffic in the trace and the total amount of SQL traffic in the trace. If the amount of SQL traffic is a low % of the overall traffic and there are connection reliability issues, it could be an indication of a large amount of other traffic, such as a backup, flooding the network and reducing reliability.
This report also lists the IP address of the machine the network capture was taken on. This is based on having a "Bad checksum" when the machine is sending packets and Checksum Offloading is enabled. If Checksum Offloading is not enabled, then the IP address cannot be identified.
This report identifies all the SQL Server instances found in the network trace, even if not on port 1433, and various properties of the servers and their traffic statistics. The statistics show how busy each server is and if there are any problems that you should be aware of. Some information is gained from login packets and SQL Browser traffic. If these items are not present in the trace, some columns may be blank. These statistics will be broken out in later reports.
This report displays all domain controllers that could be identified from conversations on ports 53 (DNS), 88 (Kerberos), or 389 (LDAP). MRSPC conversations (ports vary) are also identified as SQL Server uses this service to confirm NTLM logins.
The following Domain Controllers were visible in the network trace:
IP Address Files Clients Conversations Kerb Conv DNS Conv LDAP Conv MSRPC Conv MSRPC Port Frames Bytes
---------- ----- ------- ------------- --------- -------- --------- ---------- ---------- ------ -------
10.10.10.1 0 2 80 0 61 0 5 49673 448 104,027
10.10.10.2 0 1 17 4 0 5 7 49673 292 111,353
This report displays which conversations were seen to have logged in successfully. Conversations that started before the trace was started are omitted, as are conversations that fail to log into the database or are fully encrypted (Encrypt=Yes).
Each SQL Server has a separate table in the report.
This report displays the conversations that contain one or more RESET or ACK+RESET packets, and who emitted the first RESET and when.
Note: In general, RESET packets are emitted upon receiving additional packets after the conversation was terminated by an ACK+RESET or an ACK+FIN. MARS connections always end with a RESET as the client emits an SMP:FIN packet followed immediately by an ACK+FIN packet. When the server responds with its own SMP:FIN packet, the client emits a RESET packet. This particular closing sequence is normal. Some non-Microsoft drivers also terminate normal conversations with an ACK+RESET rather than an ACK+FIN.
Each SQL Server has a separate table in the report.
After each table is a histogram, plotting the resets over time. Each space is 1% of the time span of the total network trace. The vertical axis is a log scale, increasing by 3x each level. This can give a visual feel for whether the resets are randomly scattered or clustered and likely due to some more global event.
This histogram shows clustered resets. This could be due to an overloaded switch dropping a number of conversations at once, or some other similar reason.
This report lists all conversations for which a login sequence was found, but SQL Network analyzer was not able to find the LoginAck packet, i.e. the server response packet containing the LoginAck token. This could represent a login failure, i.e. the connection was closed before the login could be completed, or perhaps a probe connection.
It also lists any conversations that received a login failure response, along with the error message, and it flags failures due to Diffie-Hellman compatibility issues.
The histogram lets you visually get a feel for the distribution of the login timeouts and failures.
The login progress flags indicate which types of login packets were found in the connections. For example, if you just see S PL PR, then SQL Server has likely run out of available worker threads at the moment, especially if there are a cluster of similar failures.
Note: Some false positives may also be displayed:
- Encrypted logins will have the LoginAck packet encrypted so the application cannot detect it.
- The login continues after the trace stopped.
- Already opened encrypted connections. Note: most of these are suppressed.
This report lists all failed connections (SYN packets with no response) to the identified domain controllers. The start and end offsets can be used to line up any failures with login failures and may point to issues with the DC affecting the timeliness of login processing. Events appearing in the histogram will show slightly earlier than the connection failure as this is based on the time of the final packet in the conversation.
This report lists all Named Pipes connections to SQL Server found in the network trace. It does not perform an further analysis. This is mainly so you can alter the connection strings to use TCP, which allows for a better analysis.
In this example, a connection was made to each of two SQL Server instances on the same server. Note the client port # is the same for both connections.
The following Named Pipes conversations we detected in the network trace:
Server Address Pipe Name NETMON Filter (Client conv.) File Frame Offset Time
-------------- -------------------------------------- ----------------------------------------------- ---- ----- --------- ---------------
192.168.2.102 \\con2016\pipe\MSSQL$SQL2017\sql\query IPV4.Address==192.168.2.101 AND tcp.port==62194 0 69 21.519245 05:32:59.340 PM
192.168.2.102 \\con2016\pipe\sql\query IPV4.Address==192.168.2.101 AND tcp.port==62194 0 127 32.076712 05:33:09.897 PM
The client generally raises an Attention to the server if it times out a query and needs to cancel it.
TLS 1.2 is supported by SQL Server 2008 and later, as well as related driver versions. This report can show if any SQL Servers or clients have not been updated to the latest security standards.
In an Always-On cluster, you can offload read-only traffic from the primary server. In SQL Azure databases, the login gateway service will redirect connections to the actual SQL Server IP address and port number. This report displays information about any connections that are redirected to another server.
Normally, Windows 2008 and later allows for 16K outgoing connections from any particular client. If a client makes 70 connections/sec in a sustained manner to SQL Server or any other service, it could potentially exhaust the port pool and fail new connections because a new port could not be granted.
It can also be a sign the application is not using connection pooling. This is time consuming and resource intensive on the server and could result in connection delays or timeouts.
The table is only displayed when 1000 connections/minute are reached.
This report shows whether any SQL Browser traffic is reported. SSRP is a UDP-based protocol. It is connectionless in nature.
It will show one of four states:
- There was no SSRP traffic at all for the SQL Server. This is normal if listening on port 1433 or if the port is hard-coded or specified in a SQL Alias.
- There is SSRP traffic and all requests were responded to.
- There is SSRP traffic and none of the requests were responded to. This typically indicates a firewall issue and UDP port 1434 should be unblocked on the server and/or on a network-based firewall.
- There is SSRP traffic and only some requests were responded to. This can indicate an issue with the SQL Browser service itself, and probably should be restarted at a minimum.
This report shows Kerberos SPNs that could not be found or produced some other error. SQL SPNs normally have the MSSQLSvc service type, but since you can specify a custom SPN that could have any service type in the connection string, or even the SQL Server service account in place of an SPN, the report shows all failures.
The report also shows whether delegation was requested, which is the norm for SQL SPNs.
This report shows all DNS requests that resulted in an error response. This could affect connectivity if your SQL Server name appears in the list.
For anyone interested in checking out the code, the following page goes over the basic application structure.