**** WARNING **** 

This article contains content suitable only for people who own propeller beanies and are not shy about donning them in public.

Shameless plug: If you are confronted with a situation that requires you prove what encryption protocol and level you are using, I encourage you to venture forth and give it a go. If you feel lost, that this is above your head, or you don't have time and just need it done, I can do almost all this for you and give you the answers remotely. Helping companies with engineering and computer issues is all I do, full time, all the time. Just ask my wife <smile>.

If you own a propeller hat, go and get it now, put it on, and start it spinning. Slowly at first, but by the time I'm done she'll be spinning at redline or beyond. So lube it up and lets git'r spinnin!

I was working on a project for a client who had some security concerns between an application I wrote and an SQL Server 2008 R2 back end database I had setup. They felt the connection was using obsolete SSL protocols that were easy to crack. These were two Windows based boxes.

While I believed the connection used whatever the underlying schannel provider negotiated with the far side as that is how all the Microsoft world works, I wasn't 100% sure. How could I be? I didn't write the code - the only thing you can ever really truly be 100% certain of is something you've done 100% on your own.

The goal is this: Prove the connection is secure and not using an older insecure SSL level.

The questions are really "How close can you come to 100% certainty?" and "How certain is 'Certain Enough'?"

Lets start at 0% certain.

I started off at Microsoft's site doing the requisite searching for various phrases. Found some juicy bits, but nothing I could point to that stated anything I was willing to bet my paycheck on. Expanding the search, I found many people asking the same question but again no definitive answer.

The best answer I found is "It depends" - which got me to "Maybe", or to quantify that I'd put it at a 50% chance. Again, nothing I'm going to bet on.

Spoiler alert: I found the answer, and I also didn't find anyone else giving the answer, which is usually what prompts me to put it down in an article.

You can relax the propeller for a few minutes. I'm going to talk like you are a 3rd grader.

When you type https://gmail.com into your browser, the following conversation takes place over the internet between your computer - the client computer represented by C:, and the server at Google, represented by S:. I'm going to drop the bits of the conversation that don't add anything to the discussion.

C: Hello, Gmail.com - You got your ears on?

S: That's a big ten-four good buddy! I'm ready to talk anytime. Y'all go ahead now anytime you are ready!

C: Look, I'd really like to talk securely as I might say something that I really don't want anyone else to listen in on, so I want to speak in some language that only the two of us will understand. I can speak the following: SimpleEasyToBreakCrypto, MildCrypto, HeyThisIsPrettyGoodCrypto, ThisCryptoWasBrokenByTheGreeksIn5BC, Best1960sCryptoThatWasBrokenIn1995, UltraHighStrengthTitaniumNSAGiveMeAllYouveGotCrypto, QuantumLeapLockedCrypto, WeGotThisFromTheAlienTechInArea51Crypto, or TimeVortexBackChannelHasntBeenInventedYetCrypto. Can you please pick one and let me know which you choose? Also, here is some random stuff I'd like you to use back to me so we can exchange some encryption keys that work with whatever language you'll choose.

The Gmail servers look over that list your computer sent and thinks for a few microseconds, then it picks whatever the best crypto from that list that the Gmail server also supports.

S: Wow! That is quite a list. Unfortunately, I'm running some older encryption software - so the one I choose is "HeyThisIsPrettyGoodCrypto", so lets talk that way. Here too is some random stuff you can use to me so we can exchange some keys that work with HeyThisIsPrettyGoodCrypto.

A whole lot more is going to go on between the two of them to make their conversation take place under a cone of silence. But that is enough to get the idea across.

Put your propeller hat back on, start it spinning slowly - 100 RPMs.

If you've read any of my other articles, I probably reference sniffing the network data in 30-40% of them. When diagnosing a system's strangeness, often it is the tool that lets me know where to go looking for the problem - and if you can divide a big problem into smaller ones, they are easier to find and destroy.

So I broke out my favorite network sniffer, Wireshark, and sniffed the connection. But first, lets sniff the conversation above and see how it works in a perfect world. I'm not going to repeat here what I've written elsewhere or what you can watch in the webcast I did for Microsoft on Debugging with a Network Sniffer. I will show you the sniffs and point out the important parts. Set your propeller on 1000 RPM and hang on!

The following are real network traces, real packets, with nothing changed:

All pictures are thumbnails - click to see the larger version.

At the very top of the screen the first column has a packet number - 124, 127, 128, 129, .... each row is one packet. A packet I click on is exploded into its data fields in the bottom section, interpreted from bits, nibbles, words, dwords, ... into interpreted easy to read geek speak. So those first 3 packets are the Syn/SynAck/Ack that all TCP socket connections start off doing with each other at the beginning of the session. This represents those first 2 messages, the "Hey, got your ears on?" and "Ten-Four good buddy, I'm here".

That 4th packet - #129 is shown in detail in the bottom section in white. This is this part of the conversation I'm going to translate below, first in sesame street speak and then in geek speak:

C: Look, I'd really like to talk securely as I might say something that I really don't want anyone else to listen in on, so I want to speak in some language that only the two of us will understand. I can speak the following: SimpleEasyToBreakCrypto, MildCrypto, HeyThisIsPrettyGoodCrypto, ThisCryptoWasBrokenByTheGreeksIn5BC, Best1960sCryptoThatWasBrokenIn1995, UltraHighStrengthTitaniumNSAGiveMeAllYouveGotCrypto, QuantumLeapLockedCrypto, WeGotThisFromTheAlienTechInArea51Crypto, or TimeVortexBackChannelHasntBeenInventedYetCrypto. Can you please pick one and let me know which you choose? Oh, and here is some random stuff I'd like you to use back to me so we can exchange some encryption keys that work with whatever language you'll choose.

Look at the parts I outlined in red.

C: I want to talk securely and want to do our handshake using TLS 1.0. Here is a hello message structured as a TLS 1.2 hello message.

The crypto languages are outlined in green.

C: I can speak any of these 15 different cipher suites:

<set PROPELLER_RPM=5000>
Cipher #1: TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256, or translated down from uber-geek to mega-geek, Transport Layer Security Elliptic-curve Diffie-Hellman Ephemeral key exchange signed with the Elliptic-curve Digital Signature Algorithm, with the Advanced Encryption Standard (also known as "AES" and Rijandel) with a key length of 128 bit encryption protecting the blocks of data (block cipher) and using the Signature Hash Algorithm with 256 bits (SHA-256) as the authentication hash.

Whew!

That was just #1 of the 15 ciphers proposed. Has your head exploded yet? Because we have 14 more to go... (just kidding...)

Hopefully you get the idea.

That whole section in green are all the different languages the client speaks.

Last item for this packet, the one I outlined in blue that starts "49F2E5...", that is the random stuff that will be used to exchange the keys. Which is way beyond the scope of this article, but I thought I'd mention it.

And now, lets look at the second packet! What is Gmail saying it wants to use to talk to my system?

** THIS IS THE JUICY PART ** (for this session at least).

We are on packet #132, shown here in detail:

Same pattern - Red, Gmail is answering the client's hello with its own Server Hello, Blue random blobbledegook 1e8d7585...

But the juicy item is in Green - This is the cipher you are looking for to answer "How will Gmail talk to my client during this session and this session only."

TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 ... or translated down from uber-geek to mega-geek, Transport Layer Security Elliptic-curve Diffie-Hellman Ephemeral with Rivest-Shamir-Adleman cryptosystem for the key exchange with the Advanced Encryption Standard (also known as "AES" and Rijandel) with a key length of 128 bits and Galois/Counter mode symmetric encryption protecting the blocks of data (block cipher) and using the Signature Hash Algorithm with 256 bits (SHA-256) as the authentication hash.

Propeller fallen off yet? No? Great!! Pressing onward...

I left off the rest of the whole thing which sets up the encrypted connection - there were keys exchanged, possibly certificates authenticating each other, maybe the certificates were checked to see if they were revoked, .... a whole lot of stuff that is certainly part of the whole setting up of the HTTPS: connection but not important to answer the question of this article:

What encryption method is protecting this connection?

Which we have the answer now... for Gmail and this session.

So the answer to the generic question is "It depends on what the two sides negotiate with each other."

Ok, here it is:

Packets 94-95-96 are the SYN/SYN-ACK/ACK of the TCP handshake (I'm going to stop pointing those out from here forward).

You are looking at packet #97. Wireshark interprets this as "TDS", or Tabular Data Stream. It has options and stuff, some of which are mildly interesting to me but not to this analysis.

The next packet, packet #98, you can see the details of below. It is also interpreted by Wireshark as "TDS". This is the first packet from a SQL client to the SQL Server where he is saying hello, asking to log in, asking if he can do an encrypted connection, etc. That is what you would see if I opened up all the option fields.

The reply comes back in packet 97:

Hmmm... another TDS with options. Maybe there will be something cool in the next packet?

No, another TDS with more options.... Maybe packet 99 will be better?

Well now we have a little something different - we see this is a Pre-Login message of type TLS Exchange which is by the top red arrow, but that is all we know. Or, more accurately, that is all the protocol dissector inside Wireshark is telling us. That big blob of blue binary data near the bottom are the raw bytes. There is the name of the server the client wants to talk to down by the 2nd red arrow and a bunch of bytes after it I obscured to hid the server's name, but suffice to say other than that name nothing else was understandable by humans without some secret decoder ring nearby.

So we know we are either in the heart of it or darn close... the next packet, #100, shows us this:

It is another TLS Exchange, and inside the text of the packet we can see the words "@SSL_Self_Signed_Fallback" and some ascii digits. So we know SQL Server is using some certificate it minted on its own to protect the communication channel, but again we have no idea what protocol it is using - is it using the one cracked by the ancient Romans in 4BC or UltraHighStrengthTitaniumNSAGiveMeAllYouveGotCrypto? Who knows?

Spoiler alert: Moving the traffic to port 443 so Wireshark uses the standard TLS dissector on the packets doesn't reveal anything. Been there, done that, got the t-shirt to prove it - take my word for it.

I knew that SQL Server did its little hello up front before negotiation of the secured socket, but had no idea it was doing things in its own way until I sniffed the data. The good news was this took all of two minutes to discover, so I didn't waste a whole lot of effort on it.  

Meanwhile, back at various search engines, nothing definitive. The best possible answer was found here:

https://blogs.msdn.microsoft.com/sql_protocols/2007/06/29/ssl-cipher-suites-used-with-sql-server/

"When enabling channel encryption between the application and SQL Server, users may wonder what encryption algorithm is being used to protect their data.  Unfortunately, this isn’t an easy question to answer and here’s why. SQL Server (both 2005 and 2000) leverages the SChannel layer (the SSL/TLS layer provided by Windows) for facilitating encryption.  Furthermore, SQL Server will completely rely upon SChannel to determine the best encryption cipher suite to use."

The best answer I can find is from 2007 - that is 9 years ago - whose answer is "it depends..."

Surely in the last 9 years someone has come up with something more definitive, haven't they?

No, they haven't. (and don't call me Shirley.)

This is pretty much how every program running on a Windows platform operates - it calls down to SChannel to setup a secure communications link and SChannel does all the heavy lifting. If it didn't, every program would have to bring along all its own encryption and protocol algorithms and not many programs do this.

The blog article references a bunch of other articles that say what suites are supported. I also knew I'd disabled those obsolete protocols.

So I totally believed 100% that I was secure.

However, as Tom Cruise said in A Few Good Men, "It doesn't matter what I believe, it only matters what I can prove."

So, while I believe it 100%, would I be willing to bet my paycheck against yours that is truly how it operates? (For those who don't know me, that is my ultimate confidence level.)

No, I would not make that bet. My belief may have been at 100%, but my confidence that what I believed from everything else I was reading was also true wasn't at 100%.

And my client insisted that if I couldn't prove that I wasn't using an old, brittle, insecure SSL layer, then alternate paths to security would have to be taken. That client of mine - she can be a real ball-breaker - but she keeps me on my toes. Given time constraints and the answer I couldn't put my hands on quickly, we went down an alternate security path.

However, unsolved problems will not let me rest. During times when I was waiting for something else to finish, my research continued.

Many people when confronted with a question they don't know have a reluctance to say the words I don't know. I'm guessing they think it makes them look dumb.

Me? I love saying the words I don't know. Those three little words are always suffixed with comma, followed by "but let me do some research and I'll figure it out."

When you're asked something you know, all that shows is that you know something. Boring. But when you're asked something you don't know, you have an opportunity to learn something new. If you're in the computer field and you're not learning something new every time you are sitting behind the keyboard, then you are falling behind.

So I always look forward to those times where I can say "I don't know, but let me research that and get back to you."

And sure enough, this was one of those times!

The search continued - perhaps there was an indication somewhere in SQL Server what suite it had negotiated with the client - like maybe an sp_who showing an encryption level column from the master table? That would have been way too easy. I didn't find any smoking gun evidence, which doesn't mean it doesn't exist.

The closest thing I found was a select from the sys.dm_exec_connections, shown here:

Not shown are the IP address the connection is from, which is mine, along with other interesting parameters. So I know that at least SQL Server thinks it is an encrypted connection, but again no indication of what encryption type, algorithm, protocol, etc. is in use.

How about in my application code, where I can query all kinds of properties of the SqlConnection object and I also have access to a whole lot of stuff going on in the OS, perhaps I can find out there? I found nothing in the data connector's properties giving any clue. I didn't drill deep into the OS.

It all came down to the layers of the protocol stack - Both SQL and my application program were sitting on top at the application layer and could request an encrypted connection from the layers below but couldn't see into those layers to determine how they were operating.

The data is there - I have the packets. So, propeller hat up to 10,000 RPMs.

I've done my share of protocol analysis. From reverse engineering Kalatel camera control protocols for the MDOT project, writing kernel level IP stack code, was the senior engineer with Motorola when we created the RF Analyzer for listening in on the digital police radio transmissions to diagnose faults and / or validate protocol compliance, analyzing the Docsis communications for an in-flight entertainment system and thus finding bugs in Connexant cable modem chips ... I'm no stranger to protocols.

If you look here you will find Microsoft's TDS 4.2 protocol specification. Page 40 section 2.2.6.4 shows the PRELOGIN packet:

There it is - that first byte in the blue section (payload) of packet #100 is a 0x12, so I have this type of packet in front of me. Now all I have to do is count the bytes, suck out the part at the bottom that represents the SSL_PAYLOAD, put those together and lay the template of the SSL negotiation, find where the encryption type(s) / protocols are defined, and I'll have my answer!

All doable. But very tedious manually.

A computer could do this in a few microseconds - as in "thousands of times between any two keystrokes as I'm typing this"

Not a cost effective use of time or energy.

Propeller hat back down to 1000 RPMs...

All that brought me full circle back to the sniffer - I have the data, I need to find some tool that will do the protocol analysis. Wireshark has been my go to diagnostic tool since 2003. Before that, I always used Microsoft Network monitor. You may not know this, but in the NT 3.51 days there was a neutered version of network monitor included in a tools directory on the install disk. I say neutered because it couldn't sniff in promiscuous mode - and when debugging with a network sniffer often the best way is by passively listening to two systems instead of installing the sniffer on one of them. You could get a promiscuous mode if you knew where to find a version that wasn't neutered, but that's beyond this article.

Before that it was a sniffer in a UNIX box whose name I can't remember.

"Everything old is new again"

If you watch the webcast I did for Microsoft on debugging with a network sniffer, I point out a couple of the traces that were captured with network monitor that screamed out exactly what the problem was even though I was displaying the packets in Wireshark. I did some more searching, and apparently Microsoft stop development of Network Monitor years ago.

The group morphed their tool into something called Microsoft Message Analyzer (MMA). I had heard of MMA before - via Microsoft Connect, I've been getting beta and release notifications about it since Beta 2 in Feb. 2013, but I never took the time to sit down and really look at it. I don't have a lot of experience with MMA - yet! I can see there is a whole lot of power in the tool and what it can catch her and analyze. It can be used for network data captures as well.

I took my Wireshark capture, loaded it into MMA, and took apart those evil TDS packets.

Whoop! There it is (reminder: Click to see the full size image):

These are the same 3 packets from the SQL secure socket handshake I displayed earlier, except now you are looking at them through MMA's protocol dissection lens. If you open up packet #99 you will see that now MMA interprets the inside of the packet with more details that Wireshark only identified as TDS.

This should look very similar to the secure socket handshake you first saw above in my Gmail capture. In red I highlighted the client side's initial handshake. In green is that whole list of cipher suites with those same user friendly names displayed by Wireshark. You also see that random blob in blue.

"But David, how do I know this is really the same packet / same data / same handshaking sequence?"

Well, you truly don't know because I could be making all this up. But lets take the first few bytes of the random stuff and compare them to the first few bytes of the random stuff I showed in packet 99 the first time:

220 53 79 218 when converted to hex are DC 35 4F DA, and they show up in the Wireshark view of the packet in bytes 4D through 50, outlined them in purple here:

Now you know why I called out those random bytes earlier.

Really, all this is for nothing because it doesn't show what the server actually chose for the cipher suite - and that was the real problem - it didn't matter what I believed, it mattered what I could prove.

If you didn't say TL;DR already, here is the proof in packet #100. The server is replying with TLS 1.0 instead of 1.2 because he hadn't yet been patched with this patch right here.

And what cipher suite was chosen by the server? TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA. You can look that up if you really want.

So the definitive bet-your-paycheck-on-it answer is "Yes, SQL Server in this instance for this connection negotiated itself a nice secure communications channel."

If you read the details of the Microsoft blog post from 2007 I referenced earlier you will see that encryption levels / algorithms are not under either SQL Server or the application's control or even visibility. What is negotiated is dependent on the configuration of both client and server computers as to what protocols the two are willing to accept in a mutually agreed upon negotiation.

Fret not, all is not lost - there are ways you can influence these. Here are ugly and cooler geeky way to nudge systems to using better or worse encryption:

"How to restrict the use of certain cryptographic algorithms and protocols in Schannel.dll":
https://support.microsoft.com/en-us/kb/245030
https://msdn.microsoft.com/en-us/library/windows/desktop/aa374757(v=vs.85).aspx

Lots of registry tweaking, rebooting, and testing.

And the easier, not quite as user hostile way provided by the wonder folks at nartac.com called "IISCrypto" which you can get here:
https://www.nartac.com/Products/IISCrypto

The program's title says it is for IIS, but really it configures the registry so all of schannel requests match whatever you want and don't want. What you see above is a plain vanilla Windows 7 SP1 box that has been fully patched and updated via Windows Update as of 3/15/2016 - yet it would gladly make a connection via vulnerable protocols and crackable encryption.

"David, why would I want worse encryption?"

Perhaps your audience is a whole bunch of people that are still on XP and what you are serving them really doesn't need the UltraHighStrengthTitaniumNSAGiveMeAllYouveGotCrypto but WayMoreCompatibleWithOlderSystemsCrypto. It all depends on your application, what data you are transmitting, the requirements of the project, and who you are trying to protect the data from.

How do you know how secure the connection is? By configuring one side to only support what you will accept. It doesn't matter which side, so probably the side that has the most impact. In this case, even though I wrote the code running at the user's workstation I still had no control over the protocol the machine would connect back to the database with. However I did have control over the server, and that was configured to prohibit those creaky old protocols.

My only regret was not having this tool on my belt before I needed it as I probably could have saved a lot of time and money moving things around if I had the proof at hand. But rest assured that will not happen again.

If you need help proving the security on your connections, try following along - you can get your own copy of Microsoft Message Analyzer from here. If this is over your head and you need it verified please give me a call or an email. You can find details in the Contacts section of this web site, and many more articles in the Cool Stuff section.

Final Words...

If you found this helpful or not, please send me a brief email -- one line will more than do. Or more! I love talking protocols & debugging among other things.

I can be reached at:

das (at-sign) dascomputerconsultants (dot) com

Enjoy!
David Soussan

Copyright (C) 2016 DAS Computer Consultants, LTD.  All rights reserved.

References

Microsoft Message Analyzer Download: https://www.microsoft.com/en-us/download/details.aspx?id=44226
Some quick start tutorials: https://technet.microsoft.com/en-us/library/jj714801.aspx
The MMA Team blog: https://blogs.technet.microsoft.com/messageanalyzer/

Hotfix which adds TLS 1.2 support to SQL Server 2008 R2 x64: https://support.microsoft.com/en-us/hotfix/kbhotfix?kbnum=3144114&kbln=en-us
(yes, I installed it, sniffed it, and verified it lets SQL Server 2008 R2 x64 negotiate with TLS 1.2!)

TDS Protocol in SQL: https://msdn.microsoft.com/en-us/library/ee209073(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/dd304523.aspx
https://en.wikipedia.org/wiki/Tabular_Data_Stream
TLS Negotiation packet structure: https://en.wikipedia.org/wiki/Transport_Layer_Security
TLS Negotiation RFC: https://tools.ietf.org/html/rfc5246
Cipher suites in SChannel: https://msdn.microsoft.com/en-us/library/aa374757(VS.85).aspx
Bottom of this post shows someone has a nice dissector for TDS SSL negotiation: https://connect.microsoft.com/SQLServer/feedback/details/1073492/sql-server-depends-on-old-ssl-tls-versions

(C) 2016 DAS Computer Consultants, LTD. All Rights Reserved.