SQL Server Authentication + SSL

Question

Hi!

I noticed that with SSL authentication (On SQL Server 2005), the SSL handshake

is kind of "truncated" :

* Certificate is not sent from Server to client; (Which may be OK according to TLS)

*  The client does not send Client-Key-Exchange message (Which is not conforming to TLS)

This is clearly evident from tcpdump output.

However with Windows authentication, the client -server handshake is "compatible" with the TLS standard. Why just in SQL server authentication mode there is that deviation from the standard ?

More important, how is the key decided? In that mode, there is no certificate sent and no key exchange. Are the client and server using a built-in key? This might be insecure.

I'd be happy if someone can shed some light on the subject.

Answer 1

SSL is used for encryption not authentication for SQL Server.

Certificate is not needed to be send from Server to the client.

There is key exchange phase when negotiaging SSL session to setup shared session between client and server. I don't know what you see with tcpdump.

SQL Server SSL implementation based on the windows SSL api, i.e. schannel, that is used by lots of windows application including IE.

Answer 2

I want to clarify my question: how the key is exchanged? Do I have any control over that key? Is it Shared key ? Public/Private key pair? How often is it replaced? How is it generated? Is it exportable? How many bits ? 

I guess that question and the following ones are directed to people at MS:

IE's SSL protocol usage is compliant with TLS. TDS sessions of SQL Server with Windows authentication are compliant with TLS. Only SQL Server SQL authenticated TDS sessions are not. Why ? Is there an a technical reason, a security reason, or is it just so?

If it helps, what I see in TCPDUMP, when I use SQL Server authentication,  is that the Client sends TLS CLIENT_HELLO , then the server sends TLS SERVER_HELLO and CHANGE_CIPHER_SPEC , and that's it; from this point on everything is encrypted - but with TLS framing. TLS specifies that a CLIENT_KEY_EXCHANGE must be sent, but I guess the MS-TLS dropped this for some secret proprietary key exchange method.

Using Windows Authentication - the Client-Server session is compatible with TLS. (with some TDS wrapping)

 

 

Answer 3

If you choose windows authentication, SQL Server uses windows SPNEGO to authenticate, which can either be kerberos or NTLM depending on whether you have proper SPN setup in domain controller. In this case, you can see key exchange.

SQL authentication is shared key authentication. The shared key is kept in database. SSL is used to encrypt the share key. Its session encrypt key is generated in handshaking. As I said, for sql server, SSL is not used for authentication, but for encryption.

Whether SQL SSL use SSL2.0 SSL3.0 or TLS 1.0 is based on windows setting, so is Ciphers.

http://technet2.microsoft.com/WindowsServer/en/Library/ed5ae700-e05e-45ef-b536-45795dbb99a21033.mspx

For windows schannel architecutre and its behavior, http://technet2.microsoft.com/WindowsServer/en/Library/ed5ae700-e05e-45ef-b536-45795dbb99a21033.mspx.

In geneal case, not SSL resumption, you should see 4 handshake legs and the key exchange should happend in second and third leg.

I see cases that third party software or anti-virus software change windows dll and cause trouble. I hope that is not your case.

 

Answer 4

thank you. However, those documents specifiy explicitly, and more than once, that a client must send a CLIENT_KEY_EXCHANGE message. Whithout tht message, the parties can't have the PreMaster secret. However, with SQL Athentication, the client does not send that message. So my original question remains, how is the key exchanged?

Here are all the packets sent by the client and server.

Clinet==> Server :

0x0000   4500 0057 a842 4000 7e06 2c13 0a0a 0a01        E..W.B@.~.,.....
0x0010   0a0a 0a37 18be 0599 1374 8acb 51c8 a9b7        ...7.....t..Q...
0x0020   5018 fc00 4640 0000 1201 002f 0000 0100        P...F@...../....
0x0030   0000 1a00 0601 0020 0001 0200 2100 0103        ............!...
0x0040   0022 0004 0400 2600 01ff 0900 0000 0000        ."....&.........
0x0050   0000 0000 0080 00                              .......

That was a TDS packet , not an SSL one.

Server=> Client

0x0000   4500 0053 842a 4000 8006 4e2f 0a0a 0a37        E..S.*@...N/...7
0x0010   0a0a 0a01 0599 18be 51c8 a9b7 1374 8afa        ........Q....t..
0x0020   5018 ffd0 4f56 0000 0401 002b 0000 0100        P...OV.....+....
0x0030   0000 1a00 0601 0020 0001 0200 2100 0103        ............!...
0x0040   0022 0000 0400 2200 01ff 0900 0577 0000        ."...."......w..
0x0050   0000 00                                        ...

That was a TDS packet , not an SSL one.

 

Client=>Server

0x0000   4500 0096 a843 4000 7e06 2bd3 0a0a 0a01        E....C@.~.+.....
0x0010   0a0a 0a37 18be 0599 1374 8afa 51c8 a9e2        ...7.....t..Q...
0x0020   5018 fbd5 8205 0000 1201 006e 0000 0000        P..........n....
0x0030   1603 0100 6101 0000 5d03 0144 2c4a 7125        ....a...]..D,Jq%
0x0040   85d6 7679 124f 7e27 0c22 4805 d710 8482        ..vy.O~'."H.....
0x0050   387b 66f2 e000 1d05 a33c 1720 0d14 0000        8{f......<......
0x0060   06b9 b9b3 74e7 5544 a3ea 01ec 279c 24c4        ....t.UD....'.$.
0x0070   c4ea ba8a 2ca8 869d 0461 73c2 0016 0004        ....,....as.....
0x0080   0005 000a 0009 0064 0062 0003 0006 0013        .......d.b......
0x0090   0012 0063 0100                                 ...c..

This was a TLS CLIENT HELLO. I dissected it for your conviniance:

 ClientHello {
        ProtocolVersion = 3.1
        Random = {
          Time: Thu Mar 30 16:15:29 2006
         Bytes: 44 2c 4a 71 25 85 d6 76 79 12 4f 7e 27 0c 22 48 05 d7 10 84 82 38 7b 66 f2 e0 00 1d 05 a3 3c 17         }
Session ID = Len: 32 Bytes: 0d 14 00 00 06 b9 b9 b3 74 e7 55 44 a3 ea 01 ec 27 9c 24 c4 c4 ea ba 8a 2c a8 86 9d 04 61 73 c2
        ChipherSuites = len: 11 suites: { 00, 04 } { 00, 05 } { 00, 0a } { 00, 09 } { 00, 64 } { 00, 62 } { 00, 03 } { 00, 06 } { 00, 13 } { 00, 12 } { 00, 63 }
        CompressionMethod = len: 1 methods: 00
}

Server == > Client

0x0000   4500 00aa 842b 4000 8006 4dd7 0a0a 0a37        E....+@...M....7
0x0010   0a0a 0a01 0599 18be 51c8 a9e2 1374 8b68        ........Q....t.h
0x0020   5018 ff62 8d9f 0000 1201 0082 0000 0000        P..b............
0x0030   1603 0100 4a02 0000 4603 0144 2c4a a6bb        ....J...F..D,J..
0x0040   3b48 d5d6 e13d 9bd2 4722 7c58 f0f2 11c4        ;H...=..G"|X....
0x0050   8e3e 2bc0 a8d8 6d5d e00a bb20 0d14 0000        .>+...m]........
0x0060   06b9 b9b3 74e7 5544 a3ea 01ec 279c 24c4        ....t.UD....'.$.
0x0070   c4ea ba8a 2ca8 869d 0461 73c2 0004 0014        ....,....as.....
0x0080   0301 0001 0116 0301 0020 48c3 6f38 4ebd        ..........H.o8N.
0x0090   9185 2468 6438 1f70 ce24 ba60 0533 d7aa        ..$hd8.p.$.`.3..
0x00a0   931a 2e2f 63a1 e864 0471                       .../c..d.q

 Here the server did  a SERVER_HELLO and CHANGE_CIPHER_SPEC, and (probbaly) FINISHED in one packet.. Which is explicitly contradict both the MS document at the link above and the TLS protocol. I dissected the SERVER_HELLO for your conviniance:

 ServerHello {
        ProtocolVersion = 3.1
        Random = {
          Time: Thu Mar 30 16:16:22 2006
         Bytes: 44 2c 4a a6 bb 3b 48 d5 d6 e1 3d 9b d2 47 22 7c 58 f0 f2 11 c4 8e 3e 2b c0 a8 d8 6d 5d e0 0a bb         }
        Session ID = Len: 32 Bytes: 0d 14 00 00 06 b9 b9 b3 74 e7 55 44 a3 ea 01 ec 27 9c 24 c4 c4 ea ba 8a 2c a8 86 9d 04 61 73 c2
         CipherSuite = { 0x00 , 0x04 }
         CompressionMethod =  0x00
}

back to the client:

n 64339 (DF)
0x0000   4500 005b a844 4000 7e06 2c0d 0a0a 0a01        E..[.D@.~.,.....
0x0010   0a0a 0a37 18be 0599 1374 8b68 51c8 aa64        ...7.....t.hQ..d
0x0020   5018 fb53 7125 0000 1201 0033 0000 0000        P..Sq%.....3....
0x0030   1403 0100 0101 1603 0100 201f ee46 da99        .............F..
0x0040   660f 710a 8208 e625 d5d2 9882 e60e eb48        f.q....%.......H
0x0050   8c3c 2203 9292 45b8 30b9 03                    .<"...E.0..

This was an encrypted HANDSHAKE-type message. Encrypted with what key? Where in the above 2 packets was the key exchanged ? I guess this was a FINIHSED  message.

0x0000   4500 0175 a845 4000 7e06 2af2 0a0a 0a01        E..u.E@.~.*.....
0x0010   0a0a 0a37 18be 0599 1374 8b9b 51c8 aa64        ...7.....t..Q..d
0x0020   5018 fb53 3d72 0000 1703 0101 4842 0486        P..S=r......HB..
0x0030   9a98 7996 7454 1c85 541d 4124 b436 0e5b        ..y.tT..T.A$.6.[
0x0040   a9da ab08 90bd 2d59 3ed0 f0ca ab82 cdd2        ......-Y>.......
0x0050   81bf c4ef 745a 6059 afde 69c6 9ee4 6ef0        ....tZ`Y..i...n.

This is plain TLS APPLICATION_DATA. Again, encrypted with what key ?

 

To conclude, how is the client key exchanged ? As seen above, there is no packet that contains a key exchange message prior to encrypted data starting to flow.

Answer 5

What is your client version? Did you provision a valid certificate on the server side? Did you use sql server certificate hash to specify specific certificate to be used?

Answer 6

I'm using The client that is installed with SQL Server 2005. As I'm not MS-Savvy, could you tell me where can I find the exact version?

I have a valid certificate on the server side. I know that it is installed correctly since when I use the Windows authentication mode, the Certificate is sent, used, etc. I verified that with TCPDUMP , where I saw the entire handshake protocol progressing in perfect accordance to MS and the TLS documentation. Only SQL server authentication mode gives me grief.

As for specifing specific certificate - there is only one installed on the server.

Thanks!

Answer 7

Ury,

 You need to find out what the client driver you are using.

Answer 8

OK... Where do I look for that version ?

Thanks

Answer 9

  Are you using an application shipped with SQL Server, say sql server management studio? Or you wrote your own database application using SNAC, System.Data, MDAC?

 

Answer 10

I'm using the SQL Managment Studio from Microsoft. Not my own application.

Thanks

Answer 11

This is from TLS RFC 2246,

session_id
       This is the identity of the session corresponding to this
       connection. If the ClientHello.session_id was non-empty, the
       server will look in its session cache for a match. If a match is
       found and the server is willing to establish the new connection
       using the specified session state, the server will respond with
       the same value as was supplied by the client. This indicates a
       resumed session and dictates that the parties must proceed
       directly to the finished messages. Otherwise this field will
       contain a different value identifying the new session. The server
       may return an empty session_id to indicate that the session will
       not be cached and therefore cannot be resumed. If a session is
       resumed, it must be resumed using the same cipher suite it was
       originally negotiated with.

In your case, the session_id sent back by ServerHello.session_id agrees with ClientHello.session_id, so this looks like a resumed connection.

Is this your first connection to the server, or not? For windows authentication, you should see similiar behavior if it is not the first connection ever. For example, if you connect using SQL auth/windows auth, then disconnect, then connect use windows auth.

What is your client and server machine  OS version (winver.exe)? Are connections local or remote? What is the version of schannel.dll and secur32.dll on both machines?

Answer 12

OK ! You were right.

Using the previous sessions master key and the new random bytes, I was able to decrypt the rest of the TDS packets.

Thank you. By the way, if you don't mind me askng, do you work for MS ?

 

Answer 13

Ury,

  If it does convince you more that MS can ship great product and has good engineers, my answer to your last quest is yes.