How can I decode SQL Server traffic with wireshark?

sql-server wireshark
Pete picture Pete · Jan 7, 2010 · Viewed 37.3k times · Source

I can capture the packets using wireshark, but I can't decode the stream into anything intelligible. This bug suggests that maybe this isn't possible in SQL Server 2005 or newer... https://bugs.wireshark.org/bugzilla/show_bug.cgi?id=3098, but several people on Stack Overflow claimed this was a good method in answers to this question: How to SQL Server traffic is encrypted?. Any help appreciated.

Answer

dalini picture dalini · Jun 16, 2014

Edit (2017-05-02): Microsoft Network Monitor - has been replaced by Microsoft Message Analyzer - which serves the same purpose. See also comment below this answer or the answer further down for how to use it!

Original Answer:

There is another much underrated tool from Microsoft itself: 'Microsoft Network Monitor'. Basically this is very similar to wireshark with the exception that some specific MS protocols have better parser and visualisation support than wireshark itself and obviously it would only run under windows ;-).

The tool is quite old and looks abandoned (havn't seen a newer release so far) but still does an good job and the grammar for defining new protocols is quite neat/interesting - so this still possess a lot of power for the future. mnm 3.4 about dialog

Analysis Example - Recording is filtered for TDS - so the other packets are discared mostly:

Example Session for TDS (MSSQL)

This is also true for sql server connections. The MNM can even visualize the resultsets going over the wire - quite neat. Nonetheless wireshark as mentioned above would be sufficient to validate encryption and applied certificates on the wire itself. Means it can understand the TDS-Protocoll fully.

Handling TLS

Also with an extension (so called experts) 'NmDecrypt' and the right certificates (including private keys) - it is possible to decrypt protocolls - quite nice for TDS which uses TLS INSIDE of TDS - no wonder - no one has really implemented that yet as a fully supported protocoll for wireshark ;)

So far - regarding MSSQL-Traffic - or to be more precice TDS-Protocol this is the best tool I've come across so far. Wireshark is cool - but in this case MNM is 'better'. Have phun! ;)

Links for the tools:

Related questions

How do I filter SQL Server traffic between app and DB servers using Wireshark?

I'm trying to identify the source of some ill-timed connection resets. I'm trying to use Wireshark to capture the traffic that goes between the application server and database server. How do I set up a filter for this in Wireshark?

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More