SQL Tact

pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

Tuesday, January 09, 2018

SQL Server on TLS 1.2: Checklist to disabling TLS 1.1 and 1.0

A common finding in security audits these days is the failure to conduct all communications via TLS 1.2. (Correspondingly, a common cause for sudden SQL Server application connectivity failures is a sysadmin's inadvisable, reckless deactivation of TLS 1.0 and 1.1 on a server. Been there.)

Moving SQL Server connections to TLS 1.2 is not solely (or even mostly) a SQL server change. We need to get all application/vendor developers in the loop to make the transition to TLS 1.2, apply a lot of .NET version-specific patches, and more.

Disabling TLS 1.0 and 1.1 on the Windows Server that runs the SQL instance is definitely something a lot of security-sensitive folks are wanting to do (what's TLS anyway?), but they’re often hamstrung by the applications connecting to the SQL server, or by features inside SQL Server itself that have been configured to use legacy algorithms or version settings.

Contrary to some opinion out there, connections will not use the lowest common denominator allowed by the server and the application's client. Connections will use TLS 1.2 if possible. This is usually a limitation of the application connectivity client or .NET framework version. But if you want to prevent (and therefore break/expose) connections from using TLS 1.0 or TLS 1.1, you need to disable TLS 1.0 and 1.1. This is the only way to make sure you're sniffing out the insecure connections.

We definitely need to test this out in pre-production before any production changes. The list of things needed to get onboard with TLS 1.2 could be lengthy, but it’s a worthwhile endeavor. An initial checklist to consider:

  • If using SQL Server prior to 2016, patch SQL Server. Info here
  • Any clients that use the .NET framework and ADO.NET connectors will need to get up to .NET Framework 4.6 to use TLS 1.2. See the "Additional fixes needed for SQL Server to use TLS 1.2" in this same link as above. There are patches for other connectivity platforms like ODBC and JDBC as well that are needed for both the client and servers. 
    • The clients/webservers/appservers, and the SQL Server will ALL need these .NET patches. There are patches needed for frameworks starting with .NET Framework 3.5 for TLS 1.2, again see link above.
    • For example, SQL Server Database Mail still uses .NET Framework 3.5 SP1, which needs a specific patch to allow TLS 1.2. See above link for OS-specific links.
  • You need to change registry keys on the Windows Server.
  • Anything in SQL Server that is encrypted using MD5 algorithms should probably be changed anyway (certificates, database keys, and endpoints for example) but it’s definitely going to be required for TLS 1.2. https://support.microsoft.com/en-us/help/3137281/fix-communication-that-uses-an-md5-hash-algorithm-fails-when-you-use-t In fact, starting with SQL Server 2016, all algorithms other than AES_128, AES_192, and AES_256 are deprecated.
  • SQLOLEDB will not receive support for TLS 1.2, so some connections using the OLEDB driver (as opposed to ODBC, Native Client, or ADO.NET) will need to be rewritten. 
    • The 'Microsoft.ACE.OLEDB.12.0' provider continues to work, apparently.
    • Linked server connections using @provider='SQLOLEDB' will continue to work, because this actually uses the 'SQLNCLI' provider, the SQL Native Client.
  • For SSRS, we also need to make sure SSRS is using only HTTPS. Again the above .NET framework components need to be updated on the SSRS server and application servers. https://blogs.msdn.microsoft.com/dataaccesstechnologies/2016/07/12/enable-tls-1-2-protocol-for-reporting-services-with-custom-net-application/.      

How can you tell what version of TLS is currently used for client connections? See my companion blog post on this topic.

SQL Server on TLS 1.2: XEvent session to catch TLS in use

How can you tell what version of TLS is currently used for client connections? Unfortunately there isn't a handy queryable column in sys.dm_exec_connections, which would be ideal, though there is a Connect item requesting this

For now, you can detect the TLS version with an Extended Events session starting with Service Pack 1 for SQL Server 2016 and Service Pack 4 for SQL Server 2012. You'll see the event "trace" in the channel Debug, which returns information from the SQL Network Interface (SNI) layer. (Note that the Debug Channel is not checked/displayed by default in the SSMS Extended Events search dialogue.)

See example below from SSMS.

Start your new XEvents session, then look for captured SSL Handshake events (function_name = Ssl:Handshake), which will contain the protocol, cipher, cipher strength, hash algorithm, and hash strength.

See example below.

Unfortunately, this event does not capture any other information about the login except for the Peer IP Address at the end of the "text" field, what you see in the screenshot above. Application and client information hasn't been exchanged yet with the SQL Server, so other fields are not populated. You can filter out the other SQL Network Interface (SNI) noise with a filter on your Extended Events session, to find successful and failed handshakes and their protocol.

See sample TSQL script to create the session below.


ADD EVENT sqlsni.trace(

    WHERE ([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%')))

What should I do to get my SQL Server only using TLS 1.2, and why? See my companion blog post on this topic.

Wednesday, December 20, 2017

Our SQL Server 2017 Administration Inside Out fun author survey

I was honored to be on the author team of the new SQL Server 2017 Administration Inside Out book by Microsoft Press (out in Feb 2018). While the hard work was done and 700+ pages of final edits were still being passed around, the team of four authors (and our beloved tech editor, Louis) joined me in a fun interview-style blog post.

The questions were answered in late November-early December, as we were finishing up the last edits and getting the book ready, finalizing standards, and keeping up with the astounding/frustrating amount of changes happening to the product and surrounding tools! It's not just the Azure environment that is rapidly improving and maturing...

You can pre-order the book today at the Microsoft Press store or Amazon or your favorite bookseller.

The writing team (in cover order):

Thanks in advance for reading our fun little diversion from our final edits!

1.What music if any did you listen to while writing? 

William Assaf: A lot of Black Keys, Clutch, Disparition, the Dunkirk soundtrack, Thank You Scientist, and more stuff in and around those.

Randolph West: EDM.

Sven Aelterman: “I’d do anything for love (but I won’t do that).” For some unknown reason, my wife kept humming that tune in the background.

Mindy Curnutt: If I was writing from home I had Alexa spin me up some random Coffee House music, she's pretty good at it.

Louis Davidson: Nothing particular, usually older, upbeat music like Led Zep, The Who, Rat Pack, etc. on my Sonos and I can rattle the house. Sometimes though, you gotta turn it off and pay deep attention. (And sometimes you crank up Will Smith and get jiggy with it when you are just ready to be done, but there is more work left.)

2.Where did you do most of your writing, and what was your usual setup? 

William Assaf: At home, with a three-monitor display. Usually had Word open on the left, OneNote and a web browser on the middle, and an RDP session to an Azure VM running the latest SQL Server instance right.

Randolph West: Under the bed, in a veil of tears. Sometimes on the couch while watching Supernatural on TV.

Sven Aelterman: I did most of my writing in Microsoft Word, between page 1 and 70.

Mindy Curnutt: Most of my writing was done on the 3rd floor of the Richardson, TX library (the quiet floor). I tried Starbucks, but it was way too noisy, and at home there are way to many distractions. Once at the library, the only distraction I could find was to walk around looking at what books they had, and that got old after awhile. You know you can bring coffee in the library with you?

Louis Davidson: I have a home office, where I do most of my work. It is great because it it the same computer I work on daily, with 1 21:9 and one 16:9 monitor connected to a Surface Pro 4 to work on, but sometimes it can be a drag sitting where you work all day as well. 

3.Did you learn anything about your personal writing skills or habits while working on the book? 

William Assaf: During the writing of the book, I became incapable of correctly typing certain wrods like authetnication and premisis, due to overuse.

Randolph West: MacBook Pro for life! I learned that I don’t like Microsoft Word.

Sven Aelterman: About the same as what I learned while writing the answers to these questions: give me a deadline, and I’ll find a way to push it back.

Mindy Curnutt: Yeah, I think I'm better at just focusing on one chapter at a time. Writing a book is a BIG commitment.

Louis Davidson: I was tech editor, so I didn’t directly write any of the text, just comments about it. Having written several books though, it is very similar. My worst habit that is most trouble is that I can’t stop when I have a project. So I get a chapter from a writer, and I say I need a week, but then I am up until 3 trying to finish!  

4.What's your favorite new feature of SQL Server 2017? 

William Assaf: The WSFC-less Availability Groups. So many uses, so much easier. Combined with automatic seeding, AG's are getting pretty close to point-and-click.

Randolph West: Linux support. Adaptive query processing. Linux support.

Sven Aelterman: Without a doubt, the fact that Reporting Services is a separate download. Everyone loves an additional installer.

Mindy Curnutt:  Resumable online index rebuilds!! How many times could I have used this. I wonder if Ola's updated his scripts yet to take this into consideration?

Louis Davidson: I am into database coding/designing mostly, so graph tables are highest for sure. They are pretty rudimentary now, but the future is very bright in what they are adding in a future version.

5.Now that the book is (almost) done, what now for your spare time? 

William Assaf: There's still edits, but... I have to catch up on a lot of family time, wife time, and me time (mostly video games, books, scotch, and a gym membership). Some training and online videos related should be coming soon.

Randolph West: Now I can go back to the stuff I abandoned in June, like spending time with my husband, as well as acting, directing, feeding my dog...

Sven Aelterman: I can’t help but look forward to reading all the glowing reviews on Amazon.com. And writing errata. And cursing at Microsoft for changing something else in Management Studio 17.4325.1 or Azure SQL Database that makes the book look like we wrote it in the last century.

Mindy Curnutt: Spare time? Hahahahaaaa (cough, cough). I'm choking. Hold on a minute....



No spare time. Never was spare time. What exactly does that feel like anyway?

Louis Davidson: Well, I am standing in a queue at Disney World writing these interview answers, though that can’t last forever, since magic comes at a price, and that price is cash. But after the holidays it is back to writing blogs, articles, and perhaps my own new book.

6. Thinking back, what parts of your career up to now specifically came in handy when writing this book? 

William Assaf: The recession pushed me into consulting in 2007, and now I spend most my time with health checks and performance tuning. Then, I spend time writing up recommendations documents, investigation results, knowledge transfer, etc. Consulting has prepared me for book writing, it seems.

Randolph West: Copy editing. And the time I worked at a student radio station, when I would pull a 36-hour shift. As for content, dropping tables in production was hugely helpful in knowing what not to do.

Sven Aelterman: Several academic courses come to mind, including Procrastination 101 and Delegating 666 (an advanced graduate level course!). Professionally speaking, more than likely the experience of two months of troubleshooting a SQL Server 2000 failover cluster only to find the SCSI interconnect cable was faulty.

Mindy Curnutt: The painful experience of College Finals.

Louis Davidson: Being a Microsoft MVP, really, and attending a lot of sessions there and at PASS Summit and SQL Saturdays. I mostly code, but I always am listening for stuff to tell coworkers and blog readers about. I try out most stuff myself already, especially when it is inside the box (meaning features where you need only one server to try it out.)

7. Aside from SQL Server Inside Out 2017 (pre-ordered), obviously, what is a cool gift idea for a SQL DBA this holiday season? 

(Sorry if this question is a little late for shipping deadlines...)

William Assaf: I'm a big fan of DonorsChoose.org, a site that helps you crowd-fund projects to improve local classrooms. My kid and I pick out classrooms every year to help. Make a donation to a local classroom with a cool project in their honor, or buy them a gift card. Also, I can personally recommend Randolph’s idea (https://twitter.com/william_a_dba/status/935349132583202817).

Randolph West: A wine cooler, containing a bottle for each day of December. I call it an Advent Cooler. https://www.amazon.com/3-Door-Back-Bar-Cooler-Refrigerator/dp/B01N3AG07I/ 

Sven Aelterman: For DBAs who think the cloud is a fad: http://jobs.tacobell.com/

For DBAs who think the cloud is a real: https://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Daps&field-keywords=umbrella

For significant others of DBAs (can’t leave them out!): https://www.amazon.com/s/ref=nb_sb_noss_2?url=search-alias%3Daps&field-keywords=kleenex&rh=i%3Aaps%2Ck%3Akleenex

Mindy Curnutt:  If you have Grandparents (or parents) that aren't tech savvy, there are these really cool photo frames that you can control through the Cloud. I'm planning on getting one for my Grandma.

Louis Davidson: Hmm. Lego always, but something good (DBAs make too much money for something simple. Maybe the Saturn rocket? https://shop.lego.com/en-US/LEGO-NASA-Apollo-Saturn-V-21309. Or Lego Millennium Falcon. https://shop.lego.com/en-US/Millennium-Falcon-75192. Personally, I am low on space for Legos, but I definitely want Lego BB8 https://shop.lego.com/en-US/BB-8-75187.