How to find SQL Query text, network protocol information via Wireshark

Recently I had to find out if SQL server connections are getting encrypted or not and if so whether it's happening over TLS 1.2 or not. Wireshark made it very easy and provided lots of other info as well.
In wireshark, you can use filter of the sort tcp.port == 1113 && ip.addr == <SQL server's IP address> and then run the capture. I am using TCP port 1113 in my connection string. This filter would allow you to concentrate only on the packets that you want to inspect. You can also add !tcp.analysis.keep_alive_ack && !tcp.analysis.keep_alive to filter out the 'Keep alive' TCP ack packets.

If the communication is not encrypted - that is SQL server doesn't force encryption and application connection string doesn't have the encrypt=true attribute - then when I run the query, I will see a flurry of packets on wireshark console. If I right click on one of the top ones and then click Follow TCP stream, a new window will open up. At the bottom of it, you will be able to see the SQL query that I sent and the response from the server later. On the main console window, I see that the protocol used is TCP suggesting that the whole SSL layer (and therefore encryption) is missing. That is why I am able to see the query and response in plain text. The query I ran was "SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS HostName; "




If the communication is encrypted - that is SQL server is forcing encryption and application connection string has encrypt = true attribute (and of course all the other conditions for SSL handshake, certificate validation etc have been met) - I can still do all the steps above but I will only see some encrypted text like below. On the main console window, I see that in my case the protocol used is TLS 1.2 since there is an additional Secure Socket Layer. 




If I expand the Secure Socket Layer, I can see the protocol used is TLS 1.2. Same information is available on the packet row as well.



No comments: