How can I decode SQL Server traffic with wireshark?

Question

I can capture the packets using wireshark, but I can't decode the stream into anything intelligible.

This item in the WireShark bug database suggests that maybe this isn't possible in SQL Server 2005 or newer. But several people on Stack Overflow claimed this was a good method in answers to this question:

How to validate SQL Server traffic is encrypted?

Any help appreciated.

Solution 1

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!

Note: Microsoft Message Analyzer was deprecated in late 2019, and is no longer available for download.

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.

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

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:

• Microsoft Network Monitor: http://www.microsoft.com/en-us/download/details.aspx?id=4865
• NMDecrypt: http://nmdecrypt.codeplex.com/releases/view/85581

Solution 2

Not wireshark, but for me the Microsoft Message Analyzer worked great for that.

To get all the sent commands

1. Start a new session
2. Add Live Trace as as Data Source
3. Select Scenario (I chose Local Network Interfaces)
4. Enter a session filter expression like *address == 10.1.2.129 to filter only traffic to your sql server.
5. Click start
6. Right click on column header in the massage table and select Add columns...
7. Add TDS > SQLBatch > SqlBatchPacketData > SQLText

This should give you something like the following

Unfortunately there is no autoscroll implemented at the moment, but you can sort by timestamp and have the new queries popping up at the top.

Solution 3

The question that you are refering to is how to prove that the traffic is encrypted.

So they were using wireshark to show that you could not read it.

The encryption was weak on earlier versions of SQL server, but I don't think that it is easy to decrypt SQL Server 2005 traffic.

Solution 4

Wireshark decodes and shows you captured data when understand the protocol (and layer). That means the captured data isn't encripted. If the data is encripted (SSL, ie), WS will only show SSL handshakes and raw data.

View more solutions

Comments

[Pete]

But the question is, how did they prove that it was encrypted? You need to decode the TDS stream and it is not obvious (to me at least) how to do this in wireshark.

[David Ching]

MS Network Monitor has been replaced with Microsoft Message Analyzer and it indeed does nicely decrypt the TLS within TDS (NMDecrypt isn't required).

[dalini]

This is wrong, as the MS-SQL-Protocol in use does SSL/TLS inside the protocol itself not on the outer most layer, which is quite common. So this is wrong in this case and won't indicate the correct answer if looking for ssl handshakes - there are never any in this case!

[Evan Mulawski]

This product is now deprecated with no replacement.

[Evan Mulawski]

This product is now deprecated with no replacement.

[dalini]

to bad! was a neat tool... rip microsoft message analyzer - but well most probably to much effort in parallel existence of wireshark - question there: can wireshark by now decode TDS properly? really cool the scripting stuff for each protocol step... but well