The Last Pickle

SQL Server 2005 connections can be encrypted by the SQL Server using SSL rather then using OS level IPSec. In fact according to Encrypting Connections to SQL Server part of the connection is always encrypted…

“Credentials (in the login packet) that are transmitted when a client application connects to SQL Server 2005 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.”

This seems like a change from the often held belief that SQL Server 2000 performed its login in the clear. So I wanted to check that I was reading this correctly; that every login to a SQL Server server will encrypt the user name and password and that this cannot be disabled.

I fired up the old Wire Shark and set up a filter to track traffic in both directions from a SQL Server instance running on Vista and SSMS running on a XP machine.

My first test was just to login as SA and then click through the packets and see if I could find SA or the password in the bytes, no luck there. Next I decided to search the trace using the Wire Shark find functions, to make sure the string I was after was in the trace this time I connected and then execute print 'sa' against the server.

Searching the trace found two occurrences of ’sa’, one in a packet from the client to the server followed be the response packet as follows:

Request Packet
0000  00 50 8d d9 5d 33 00 17  f2 ed bb 62 08 00 45 00   .P..]3.. ...b..E.
0010  00 66 83 b4 40 00 40 06  32 c2 c0 a8 01 65 c0 a8   .f..@.@. 2....e..
0020  01 66 e7 83 05 99 72 95  61 97 58 54 3f 87 80 18   .f....r. a.XT?...
0030  ff ff 8b 96 00 00 01 01  08 0a 06 be b3 ea 02 1f   ........ ........
0040  b4 ad 01 01 00 32 00 00  01 00 16 00 00 00 12 00   .....2.. ........
0050  00 00 02 00 00 00 00 00  00 00 00 00 01 00 00 00   ........ ........
0060  70 00 72 00 69 00 6e 00  74 00 20 00 27 00 73 00   p.r.i.n. t. .'.s.
0070  61 00 27 00                                        a.'.             

Response Packet
0000  00 17 f2 ed bb 62 00 50  8d d9 5d 33 08 00 45 00   .....b.P ..]3..E.
0010  00 6a 1f 4e 40 00 80 06  00 00 c0 a8 01 66 c0 a8   .j.N@... .....f..
0020  01 65 05 99 e7 83 58 54  3f 87 72 95 61 c9 80 18   .e....XT ?.r.a...
0030  00 fe 84 78 00 00 01 01  08 0a 02 1f b6 69 06 be   ...x.... .....i..
0040  b3 ea 04 01 00 36 00 33  01 00 ab 1e 00 00 00 00   .....6.3 ........
0050  00 01 00 02 00 73 00 61  00 06 50 00 49 00 43 00   .....s.a ..P.I.C.
0060  4b 00 4c 00 45 00 00 01  00 00 00 fd 00 00 f7 00   K.L.E... ........
0070  00 00 00 00 00 00 00 00                            ........         

While this is not definitive proof that the credentials are encrypted its seems like enough for me. When looking at packets exchanged when the client connects there is a packet sent from the server to the client that Wire Shark decodes as a TDS Response that it says contains contains several tokens one of which is called “Token 0xad Login Acknowledgement”. When I look at the other information in this packet it includes strings such as:

• “Changed database context to ‘master’.”
• “Changed language setting to us_english.”

And a decoded token that according to Wire Shark changes the Code Page to 1033. So I am going to take this packet as the login response packet.

The previous packet from the client does not contain anything that looks like the sa username or its password.

Encryption using certificates

To test what happens when certificate is specified and SQL Server is configured to require encryption I followed How to: Enable Encrypted Connections to the Database Engine and installed a self signed certificate on the server. Then I configured the server to use the certificate and force encryption and installed the public certificate on the client.

Running the second test again Wire Shark could not find the string “sa” in any packet exchanged between the servers. It could not find the print 'sa' statement nor could it find the result of the query.

It looks like by default SQL 2005 does encrypt its login packets. And that it is possible to encrypt the entire conversation either using a self signed certificate generated by SQL Server or an installed certificate.