I’ve yet to hop into Wireshark with a database connection problem and figured it out because of the network capture. It is not the first place I stop. It has never come to that, I’ve never needed to go that deep. I have always used the other, more topical tools to diagnose a connection problem.
I have had an occasion where I wanted to go deeper. I wanted to find out for sure if the login information in a SQL connection was encrypted if you didn’t specifically setup TLS. The answer was it depends.. on the client. Most all implementations are encrypted. I happened to come across an old implementation that was not. It was a good answer to find out for sure and going deep with a network trace was the only way.
Today I set out to look at some packet captures of MSSQL connections and see if anything interesting showed itself. Turns out I found something very interesting. Have you ever connected to an instance of SQL server with SSMS and had it take for ever to load the list of folders? Lets take a look at a normal start to a SQL connection. I filtered out the IP and default SQL port.
This all happens in short order. In about 3 milliseconds, the client and server have completed their TCP handshake and negotiated on the TDS protocol in order to send the secured SQL username and login.
Now we have the jankity trace. The connection I attempted directly after installing SQL was much slower.
And no wonder it was slower. Look at all those re-transmissions. If an ACK is not received by the client affirming the packet was received and undamaged, the client will send the packet again.
In this particular case, I was reminded that the default amount of memory for a Hyper-V VM is 512MB. This was not quite enough to run through an install and have all the things open that I did. I shut the VM down added some RAM and my connections were running much more smoothly after that.