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'
GOWireshark 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.
When you open up Wireshark for the first time you will see a welcome page of sorts. To get started go to the Capture menu and select “Options…”, if you are a keyboard person you can use CTRL + K.
This is the options window you should see, with my settings shown. If you have more than one NIC on your server (backup network, production network, etc.) you can select the NIC by clicking the drop down arrow beside the ‘Interface’ box. Wireshark should list each NIC it detected at startup. The only setting I added was typing “port 1433” into the Capture Filter box. This is to capture only the packets that pertain to SQL Server. If your instance is not configured to use the default port for SQL Server you will need to enter the port your instance is configured to use.
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).
This is what my stream looks like. I highlighted a few sections of interest. Notice you can pick out my server’s hostname SHAGGY and then the database I’m connecting to “SeeMyData”. If you have a named instance you would probably see it as well. I’m just on a default instance. I also noticed the “SSL Self Signed” text. I believe this is associated with SQL Server encrypting the username and password being passed with the login packet. This would be confirmed by this Books Online article that states: “Credentials (in the login packet) that are transmitted when a client application connects to SQL Server are always encrypted. SQL Server will use a certificate from a trusted certification authority if available. If a trusted certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started, and use the self-signed certificate to encrypt the credentials.” I have not configured a CA server for my lab so SQL Server is using the self-signed certificate. You can see when that self-signed certificate is created by taking a quick look at your first ERRORLOG for the SQL Server instance when it was started.
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”.
You can just follow through the Data Connection Wizard, using the SQL login and password we created above.
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
You might be wondering as I am, why can I now see my login in plain text, when this article stated my credentials are always encrypted? While it does only state the “login packet” I found that the issue has to do with ODBC connections. (I got a little help from the #sqlhelp hastag on Twitter in figuring this out.) I went back and performed the same test using SQLCMD and SSMS, that use OLE DB to connect, finding that it does not display the username in clear text. The query I used in SQLCMD can be seen here, and the results showing no username can be seen here. I searched around Google, but have not been able to find anything to explain why an ODBC connection does this.
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.
