What does my SQL Server data look like over the wire?

By: Shawn Melton   |   Comments (4)   |   Related: > Monitoring

Problem

How can I see what the outgoing and incoming traffic to my SQL Server instance looks like? Are there any tools that can help with this? Check out this tip to learn more.

Solution

I have been wondering this same thing. I decided to build a test lab to find out. I will tell you up front that I am not a network engineer, I'm a DBA. So don't expect to much detail on the network packet stuff. In this tip I will go over what I used for the test, how I set it up, and what I used to test it.

The network analyzers that are out there are fairly easy to use and obtain. Wireshark is my preferred tool to use. There are some other ones out there, but this is the most popular one, and fairly straight forward to setup and use on Windows operating systems. You can download it from here.

Please note that Wireshark uses memory to store packet information as it is capturing it. So I would not suggest running this on a production server by any means. If you want to do this in production environment then install Wireshark on a secondary server. I would then work with your network engineer/team to have port mirroring configured on the production server's port, to your secondary server's port. This will copy all outgoing and incoming packets from your production server to the port your secondary server is on.

Server Set

I have created a simple virtual server using VMWare Player (free) running Window Server 2003 R2 Enterprise Edition (x64) and SQL Server 2005 SP4 Enterprise Edition (x64). I took the default configuration that VMWare Player gave me, and then just installed the specified operating system on it.

SQL Server Setup

It is a default installation (click Next) of SQL Server. I installed it as a default instance only and made no configuration changes to SQL Server at this point. For the database, I used the below script that will create a database in simple recovery mode, add 1 single table and populate it then create a SQL Login. I will use these objects to see what I can capture on the SQL Server instance.

--Create Database
CREATE DATABASE [SeeMyData]
GO
ALTER DATABASE [SeeMyData] SET RECOVERY SIMPLE
GO
--Create Basic Table and add data
USE [SeeMyData]
CREATE TABLE dbo.UserData
 (
 Name nvarchar(50) NULL,
 City nvarchar(25) NULL,
 State char(2) NULL
 )  ON [PRIMARY]
GO
--Populate the table with a few rows of data
USE [SeeMyData]
GO
INSERT INTO dbo.UserData VALUES ('Shawn Melton','Montgomery','AL')
INSERT INTO dbo.UserData VALUES ('Scooby Doo', 'Ghost Town', 'TX')
INSERT INTO dbo.UserData VALUES ('He-Man', 'Greyskull', 'TN')
INSERT INTO dbo.UserData VALUES ('Kringer', 'Greyskull', 'TN')
GO
--Create SQL Login to use for testing
USE [master]
GO
CREATE LOGIN [WhatISee] WITH PASSWORD=N'P@ssword2011'
 , DEFAULT_DATABASE=[SeeMyData]
 , CHECK_EXPIRATION=ON
 , CHECK_POLICY=ON
GO
USE [SeeMyData]
GO
CREATE USER [WhatISee] FOR LOGIN [WhatISee]
GO
EXEC sp_addrolemember N'db_datareader', N'WhatISee'
GO

Wireshark Setup

Now we need to setup Wireshark to capture our activity. I'm not going to go through the installation, you can go here if you need help with it (pretty much just click 'Next' throughout). I have installed Wireshark on the same server SQL Server is running, strictly because this is my test environment. The next few screenshots will walk you through how I setup a session in Wireshark.

Now capture something

I want to do a quick test to make sure my filter is going to actually capture the traffic for my SQL Server instance. I found a quick method that can be used to do a quick database connection on a user's computers is to create a UDL file (Universal Data Link). [To create one real quick just open up Notepad, go to File > Save As. Name the file "test.udl" (include the double quotes) and save it to your desktop.] My test connection is shown below that I did from my local desktop to the VM server, along with what Wireshark captured using the filter we set above:

Now if you want to see what that just did, right-click on any of the packets that were captured and select "Follow TCP Stream". This will allow you to see the data from the TCP stream as the application layer sees it (source: Wireshark Chapter 7.2).

Now lets capture some queries

Prior to continuing I cleared the previous captured packets. You can do this, in Wireshark, by going to the Capture menu and select "Restart". Keyboard shortcut is CTRL + R. This will clear your screen of captured packets. I'm going to use Excel 2010 that I have on my local desktop to play with the table in the SeeMyData database. With Excel all I'm going to do is setup a connection under the Data ribbon "From SQL Server".

Once I have completed the wizard and have data showing in the Excel worksheet I go back and stop the capture in Wireshark. The captured packets show I caught 91 packets total for this test. I can now do as I did above and just right-click on one of the packets, then select "Follow TCP Stream". You can see what my TCP Stream shows here. I highlighted three things that show up in this packet capture:

• Server name (default instance)
• SQL login - WhatISee
• SELECT query to pull the data into Excel

So you might be thinking that your application uses ODBC connection and performing the same test shown above you find it is passing the username. What can you do? Well there is hope and SQL Server can help. A setting that I have been reading about lately on protecting my data-in-transit has been the "Force Encryption". It is mentioned in one of the links above. You can also see information on it from here and here. The second link will show you how to configure it for your instance of SQL Server.

After turning this on, I re-executed the test above and you can see the TCP stream here that I captured. You will find that not only do you not see the username being passed, you cannot pick out anything compared to what you could previously. You should note that I'm only using the self-signed certificate that SQL Server generates. A more secure method would be to apply a certificate to the server from an actual Certificate Authority. This will provide a little deeper encryption and make it a little bit tougher for folks to decrypt your traffic.

Next Steps

• You can find some additional information on security best practices with SQL Server 2008 here. There is some good information and document links to read through.
• Also be sure to check out other great tips that are available on MSSQLTips.com. The best way to do this is to become a free member and receive the daily newsletter that has all the latest tips.

About the author

Shawn Melton started his IT career in 2002 and has experience working with SQL Server 2000, 2005, 2008, and 2008 R2.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips