This question is really just “how does windows determine a cipher strength for an encrypted connection” as SQL server just hands off to the windows schannel.dll to deal w/ this. But I was looking to determine the answer for SQL Data in transit encryption.
This was pretty muddy to track down. There are some reference details here, here and here.
At the end of the day, it comes down to an OS version question (potentially influenced by some OS/registry settings or patches). I couldn’t find any matrix anywhere so I am going to start one here. I am going to start out w/ the few I tested and hopefully fill this in w/ reader submissions (hint, hint). The guidance I can find seems to say that Win 2k8R2 and Win 7 have the same schannel capabilities so I am going to list them together.
How to use this table: Find the client OS listed vertically down the left side and then find the server OS listed horizontally across the top. Where the row and column meet is your the cipher which, under the default settings, they should negotiate to use. More on the ciphers here.
How did we determine this? The only way I was able to find was to sniff the beginning of the SSL conversation. Netmon 3.3 is an excellent tool for this. Note that you may need to set your Windows parsers from stub to full in order to decrypt the TDS packets. You can do this in Tools -> options -> Parser. Set Windows to ‘Full’
Start your trace before you start your encrypted connection. I like netmon b/c it will separate the network traffic into conversations. Find the conversation you are looking for by IP and port. For my purposes I was looking for my web server IP and my DB IP going to port 1433 on my DB.
Once you have found your conversation, you are going to want to find the SSL conversation. In netmon, it will look like this:
What happens here, generally, is that the client offers a list of supported ciphers in the SSL: Client Hello. In netmon, you can see this by selecting that packet as I have done above. In the ‘Frame details’ pane, you can expand ssl: Client Hello.–> TlsRecordLayer: –> SSLHandshake –> Client Hello:. You should see a list of CipherSuites listed in order of preference. See the bottom of the picture below.
The server will respond with cipher it wants. You can see this in the (likely) next packet under ssl –> TlsRecofdLayer-> SSLHandshake –> ServerHello –> CipherSuite. In this case, both client and server are running Win 2k8 r2.
As you can see, between Win 2k8r2 and Win 2k8r2 we attain TLS_RSA_WITH_AES_128_CBC_SHA.
There are several methods to influence the SSL Handshake. The links above will be useful in starting that journey.
One note on AES-128 cipher strength. For a measure of scale, a computer with a billion processing elements, each capable of trying a billion keys every second, would be able to try (2^60 keys/second). That means it would take (2^128 keys) / (2^60 keys/second) = 2^68 seconds to brute-force check all 128-bit keys. That is about 10^13 years to crack the key, or about 1000 times the age of the universe. Realistically, you would only need to try half the keys on average so that would be 500 times the age of the universe.
Or you could find a flaw in the algorithm...
or steal the certificate/password...
This was pretty muddy to track down. There are some reference details here, here and here.
At the end of the day, it comes down to an OS version question (potentially influenced by some OS/registry settings or patches). I couldn’t find any matrix anywhere so I am going to start one here. I am going to start out w/ the few I tested and hopefully fill this in w/ reader submissions (hint, hint). The guidance I can find seems to say that Win 2k8R2 and Win 7 have the same schannel capabilities so I am going to list them together.
| Client\Server | Windows 2008 R2 | Windows 2003 SP2 | Windows 2000 SP4 |
| Windows 2008R2/7 | TLS_RSA_WITH_AES_128_CBC_SHA | SSL_RSA_WITH_RC4_128_MD5 | SSL_RSA_WITH_RC4_128_MD5 |
| Windows 2003 SP2 | SSL_RSA_WITH_RC4_128_SHA | SSL_RSA_WITH_RC4_128_MD5 | SSL_RSA_WITH_RC4_128_MD5 |
| Windows XP SP3 | SSL_RSA_WITH_RC4_128_SHA | SSL_RSA_WITH_RC4_128_MD5 | SSL_RSA_WITH_RC4_128_SHA |
How did we determine this? The only way I was able to find was to sniff the beginning of the SSL conversation. Netmon 3.3 is an excellent tool for this. Note that you may need to set your Windows parsers from stub to full in order to decrypt the TDS packets. You can do this in Tools -> options -> Parser. Set Windows to ‘Full’
Start your trace before you start your encrypted connection. I like netmon b/c it will separate the network traffic into conversations. Find the conversation you are looking for by IP and port. For my purposes I was looking for my web server IP and my DB IP going to port 1433 on my DB.
Once you have found your conversation, you are going to want to find the SSL conversation. In netmon, it will look like this:
What happens here, generally, is that the client offers a list of supported ciphers in the SSL: Client Hello. In netmon, you can see this by selecting that packet as I have done above. In the ‘Frame details’ pane, you can expand ssl: Client Hello.–> TlsRecordLayer: –> SSLHandshake –> Client Hello:. You should see a list of CipherSuites listed in order of preference. See the bottom of the picture below.
The server will respond with cipher it wants. You can see this in the (likely) next packet under ssl –> TlsRecofdLayer-> SSLHandshake –> ServerHello –> CipherSuite. In this case, both client and server are running Win 2k8 r2.
As you can see, between Win 2k8r2 and Win 2k8r2 we attain TLS_RSA_WITH_AES_128_CBC_SHA.
There are several methods to influence the SSL Handshake. The links above will be useful in starting that journey.
One note on AES-128 cipher strength. For a measure of scale, a computer with a billion processing elements, each capable of trying a billion keys every second, would be able to try (2^60 keys/second). That means it would take (2^128 keys) / (2^60 keys/second) = 2^68 seconds to brute-force check all 128-bit keys. That is about 10^13 years to crack the key, or about 1000 times the age of the universe. Realistically, you would only need to try half the keys on average so that would be 500 times the age of the universe.
Or you could find a flaw in the algorithm...
or steal the certificate/password...
Hello Cornasdf,
ReplyDeleteActually I was using Nessus and it detected that the DB server scanned had the "SSLv3 Padding Oracle On Downgraded Legacy Encryption Vulnerability (POODLE)" vulnerability on port 1433. As I need to proove that it really use the sslv3 (Security Team), I will try your guide.
So in conclusion:
- The Cipher and Protocol used in a connexion between a SQL Server and a client depends on the list of supported ciphers sent by the client to the server.
- The server choose from the availables Ciphers/Protocols in the Certificate installed and used by the O.S. where Microsoft SQL Server is installed, and determine the best secure Server/Client Cipher combinaison to use.
So Nessus sent to the server a list of supported ciphers including the SSLv3 ones.
Do you think that a mitigation would be to change the O.S. configuracion (a Windows Server 2012) to disable the use of SSLv3, by following this article?:
http://cloudacademy.com/blog/how-to-fix-poodle-on-windows-server-2012/
Ps: Can you publish the pictures again please?
Ikh4.
Hi, that is a bummer the pics are gone, I'll see if I can dig them up some where.
ReplyDeleteFor hardening tls on a Windows server, we use a modified version of the script here: https://github.com/NWebsec/NWebsec.AzureStartupTasks/wiki/TLS-hardening
Your article is fine but leaves several vulnerabilities open as poodle is just one among many. You want to shut down ssl completely, enable all the tls versions and limit your ciphers, especially if you have older expert grade ciphers still supported.
I recommend the ssllabs https checker as a much more complete check.
https://www.ssllabs.com/ssltest/
Good luck, feel free to reach out if you have further questions.