Step-by-Step Guide to Enabling SQL Server Encryption: Using Wireshark to Verify Network Data Security
Apr 27, 2024
Edition: Saturday, April 27th, 2024
Over the past several weeks we've implemented a CA, generated a certificate, and enabled SQL Server network encryption. We validated encryption was being used via the sys.dm_exec_connections dmv. This week, I'll take the validation one more step by using Wireshark.
Wireshark is a free, open-source tool used for network analysis. With it, we can capture and interactively browse the traffic running on a computer network. In our case, we'll use it to review traffic between our client machine and SQL Server.
Here's what I have in my environment:
- DC1 - Windows Server 2022 (domain controller)
- CA1 - Windows Server 2022 (certificate authority)
- SRV2 - Windows Server 2022 (client server with SSMS installed)
- SRV3 - Windows Server 2022 (SQL Server 2022 Developer Edition)
Goal for today:
- Review network packets, using Wireshark, to compare the differences between encrypted and nonencrypted traffic between SRV2 and SRV3.
Let's jump in.
Wireshark:
You can download Wireshark from https://www.wireshark.org/. There are several options to choose from during the installation. I went with all of the defaults for this exercise. I installed Wireshark on SRV2 in my environment. After installation, open Wireshark.
To start a capture, click capture and then start.
You'll immediately notice there's a lot of network traffic occurring. Here's where filters are helpful. We only care about the traffic between the client machine, SRV2 (192.168.153.133), and the SQL Server 2022 host SRV3 (192.168.153.156).
Click in the "Apply a display filter" box and enter the filter below.
((ip.src == 192.168.153.133 && ip.dst == 192.168.153.156) || (ip.src == 192.168.153.156 && ip.dst == 192.168.153.133))
Don't forget to apply it.
Wireshark is still too noisy for our purpose. Let's add to the filter and only to include traffic using the TDS protocol.
((ip.src == 192.168.153.133 && ip.dst == 192.168.153.156 && tds) || (ip.src == 192.168.153.156 && ip.dst == 192.168.153.133 && tds))
Next, I'll start the capture, and run the following query from SSMS - SELECT Email FROM dbo.Customers WHERE CustomerID = 1.
I am currently not enforcing encryption. Below is the output from Wireshark after running the query.
We can see the query being sent to SQL Server and then the response back. Can we see the query text and the data being streamed back to the client? Let's find out.
Right-click on the first line in the output, select follow, and the TCP Stream.
In the TCP Stream window, we first see the query in plain text.
Click on the drop-down box that currently shows "Entire Conversation" and change it to Target IP --> Client IP (in my case 192.168.153.156 --> 192.168.153.133). The data returned is now displayed in plain text.
I'll enable encryption, restart SQL Server, and run the query again to compare the results.
Following the TCP Steam again, we can see everything is encrypted.
Conclusion:
We've only scratched the surface of what you can do with Wireshark. If you're interested in learning more, check out the documentation page at https://www.wireshark.org. If you don't have an environment to test in, I created the "6 Step Virtual Sandbox Jumpstart" on how to get started using free tools. Check it out here.
Thanks for reading! Have a great week!