SSL in Microsoft SQL Server JDBC 1.2 driver

  • Article

One of the major difference between Microsoft JDBC Driver 1.1 and Driver 1.2 is its feature for SSL. If you want to connect to SQL Server 2005 using SSL then you can do this today with the driver. The process to get this done is documented in the help section . This post, is going to explore one more feature of the driver for SSL.

The Microsoft JDBC Driver 1.2 uses SSL for pre-login handshake, even when you have not requested for one or forced it to. This is a different approach that the driver takes compared to its predecessor.

In SQL Server 2005 a driver requesting for a SSL communication goes through the following pattern:

  1. If you have forced the SQL Server to use a certificate explicitly (SQL BOL mentions how to do this) then it will use it for all the communication including pre-login handshake and there after
  2. However, if you have not then the SQL Server tries to identify a valid certificate from the certificate store and uses it for Pre-Login handshake.
  3. If none of the above is true, then it will fallback to self generated SSL and use it for Pre-Login handshake.

The Self SSL information can be viewed in SQL Server Logs

clip_image002

Let me show you how to trace the SSL information the driver is using by leveraging the JDBC Driver Tracing feature .

clip_image003

In the program above, I have used the TDS.DATA to give us the exact information that we are interested in. Since, on my machine I don’t have a SSL installed, it uses the Self generated SSL. Let’s take a look at the log generated

<record>
  <date>2008-02-03T04:49:35</date>
  <millis>1201994375331</millis>
  <sequence>3</sequence>
  <logger>com.microsoft.sqlserver.jdbc.TDS.DATA</logger>
  <level>FINEST</level>
  <class>com.microsoft.sqlserver.jdbc.TDSChannel</class>
  <method>logPacket</method>
  <thread>10</thread>
  <message>/127.0.0.1:49192 TDSReader@dc8569 ( ConnectionID:1 TransactionID:0x0000000000000000) received Packet:1 from SPID:0 (602 bytes) 12 01 02 62 00 00 00 00 16 03 01 02 55 02 00 00 ...b........U... 46 03 01 47 A4 FA 87 1B A2 C4 BB A0 8A 35 FE EE F..G.........5.. 18 68 32 05 38 54 53 59 83 D4 DF 0C 75 17 01 48 .h2.8TSY....u..H 63 F9 67 20 62 0F 00 00 0C 3D 2A 0B 88 27 74 1C c.g b....=*..'t. 6E F0 92 61 FB 88 69 02 78 FF 6C F0 EB 75 8D 43 n..a..i.x.l..u.C E6 3A A6 4E 00 2F 00 0B 00 02 03 00 02 00 00 01 .:.N./.......... FD 30 82 01 F9 30 82 01 62 A0 03 02 01 02 02 10 .0...0..b....... DA 0F 90 FD 8B C4 03 BF 45 77 C6 09 38 E7 3E 58 ........Ew..8.>X 30 0D 06 09 2A 86 48 86 F7 0D 01 01 05 05 00 30 0...*.H........0 3B 31 39 30 37 06 03 55 04 03 1E 30 00 53 00 53 ;1907..U...0.S.S 00 4C 00 5F 00 53 00 65 00 6C 00 66 00 5F 00 53 .L ._.S.e.l.f._.S 00 69 00 67 00 6E 00 65 00 64 00 5F 00 46 00 61 .i.g.n.e.d._.F.a 00 6C 00 6C 00 62 00 61 00 63 00 6B 30 1E 17 0D .l.l.b.a.c.k0... 30 38 30 32 30 33 30 34 32 33 31 31 5A 17 0D 33 080203042311Z..3 38 30 32 30 33 30 34 32 33 31 31 5A 30 3B 31 39 80203042311Z0;19 30 37 06 03 55 04 03 1E 30 00 53 00 53 00 4C 00 07..U...0.S.S.L. 5F 00 53 00 65 00 6C 00 66 00 5F 00 53 00 69 00 _.S.e.l.f._.S.i. 67 00 6E 00 65 00 64 00 5F 00 46 00 61 00 6C 00 g .n.e.d._.F.a.l. 6C 00 62 00 61 00 63 00 6B 30 81 9F 30 0D 06 09 l.b.a.c.k0..0... 2A 86 48 86 F7 0D 01 01 01 05 00 03 81 8D 00 30 *.H............0 81 89 02 81 81 00 C5 C7 37 F6 C7 55 56 EB D7 25 ........7..UV..% F4 7D 6E C3 34 F4 84 07 4F C9 B7 79 AA 0E E5 DC .}n.4...O..y.... 16 99 60 CE B9 D4 39 25 33 13 F7 D3 25 56 96 E3 ..`...9%3...%V.. E2 29 C9 AF D3 41 29 05 58 37 D8 58 A3 10 C5 9C .)...A).X7.X.... EF C1 33 BC C6 0F 49 EB EC DD 6E 13 75 23 CD D1 ..3...I...n.u#.. 8B 4A E6 A8 CE 1B 0A B6 7B B2 37 68 88 1B 74 14 .J......{.7h..t. 9A 43 A9 D0 0A AE 52 F4 17 CE 2B 8F 4F D7 04 FB .C....R...+.O... A1 C0 55 3A FD F4 DC DC F5 5C C2 2C 15 E3 28 17 ..U:.....\.,..(. 4F 6F 79 99 50 3F 02 03 01 00 01 30 0D 06 09 2A Ooy.P?.....0...* 86 48 86 F7 0D 01 01 05 05 00 03 81 81 00 68 8A .H............h. F1 2E 44 A8 F3 0F 87 EB D4 19 64 92 16 E6 BB 0D ..D.......d..... CE 2B 5A 9B 2B DF 7F 6B 41 5B 21 AA C2 FD 68 95 .+Z.+..kA[!...h. FD DA 20 27 72 1A E1 E8 38 8B 9A A2 E8 1E D9 2F .. 'r...8....../ 43 39 89 5E 9D 36 56 D4 25 B6 AA 96 7C 44 9E F1 C9.^.6V.%...|D.. 4D E5 AA F7 2C 07 0D 32 12 6A CE 68 0E 1C 92 FC M...,..2.j.h.... 60 9B B9 A2 C9 17 45 AA 99 80 C2 A0 A4 F2 85 41 `.....E........A E1 FB 87 28 90 CC 64 20 62 5E 38 06 77 9B 48 81 ...(..d b^8.w.H. D3 9F 46 D6 92 58 10 2F 8A E1 38 1A BB 1F 0E 00 ..F..X./..8..... 00 00 ..</message>
  </record> 

The important thing to remember is that if you are not using explicitly forced SSL and have set the “encrypt=false” in the connection string, then too the driver uses SSL for only initial handshake and turns it off afterwards for the channel communication.

Hope this helps..