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

Sunday, May 20, 2018

Thanks for joining us at SQLSat Dallas!

Engaged crowds and great questions at both my morning sessions on Saturday morning at SQLSat Dallas, an event put together by a team of expert SQLSat organizers, including SQL Server 2017 Administration Inside Out co-author Mindy Curnutt.

Thanks to everyone who attended (and especially those who attended both!) my back-to-back sessions, What's New In SQL Server 2017 and Think Like a Certification Exam.

I've uploaded my presentation slide decks for download to my Github and also to the SQLSaturday.com schedule website.

Again, asking only the tough questions.
Regrettably we won't be able to make SQLSat Pensacola in June, but we will see the #SQLFamily at SQLSaturday Houston on June 23 and then of course at our hometown SQLSat Baton Rouge on August 11. Baton Rouge's Call for Speakers is open for another 10 days or so, submit a session today!

Tuesday, May 15, 2018

See you at SQLSat Dallas!

Looking forward to making the road trip on Friday and joining the #SQLFamily at SQLSat Dallas 2018 this Saturday. It's my first SQLSat Dallas since my last trip to SQLSat Dallas in November 2013, and I'm looking forward to seeing everyone again.

I'll be speaking twice in the morning, back to back, with my two newest presentations: What's New In SQL Server 2017 and Think Like a Certification Exam.

Also check out presentations from fellow Baton Rougeans Thomas LeBlanc and John Wells!

Saturday, May 05, 2018

Thanks for joining us at the Spring Houston Tech Fest 2018

Congrats to Michael, Eric, John, all the organizers and volunteers, and all the speakers for a successful Spring Houston Tech Fest 2018. It was a pleasure for the wife and I to get back into speaking at this conference, and oh did we, with a combined six hours of quality Assaf material. It was also great to see a presence from local schools and IT companies, including a whole bunch of folks from Improving. As a local tech conference organizer myself, a big personal thank you to Improving for bringing a host of speakers.

Thanks to usergroup.tv, video of my first presentation, "What's New in SQL Server 2017", is available online.

This was the first time I presented my "Think Like A Certification Exam" session, based on my experience as a writer and SME for the SQL Server exams since 2012. Thanks for everyone who attended and asked so many great questions. And best of luck to all of you who are still students as you launch your careers! Maybe just wait a few years before shooting for the MCSE. :)

I also presented my "What's New In SQL Server 2017" session, gave away a copy of SQL Server 2017 Administration Inside Out, and was joined again by my colleague Steve Schaneville for "Twilight TimeZone: Handling Time in Your App Architecture", our delve into the murky depths of handling time zones in your applications.

All the slides from all three presentations are available for download here.

During my "Think Like a Certification Exam" we asked the hard questions. 

Tuesday, May 01, 2018

Ready for Houston Tech Fest 2018

Houston Tech Fest is this weekend!

In 2016, there was a wedding. In 2017, rescheduled at the last minute because of Harvey, I couldn't reschedule. But prior to 2016, I had spoken at Houston Tech Fest going back to 2010 (at least, memory fails). I'm really happy to be returning to this annual event in its new season and venue, this weekend at the San Jacinto College Central Campus in Pasadena, TX.

Since you're familiar with SQLSaturdays, Houston Tech Fest will feel quite familiar, though with a broader technology scope. Register for this free Saturday training event today: houstontechfest.com

I'll be speaking in three time slots:

Session 1: What's New In SQL Server 2017

Session 3: Think Like a Certification Exam

Session 5: Twilight TimeZone: Handling Time in Your App Architecture (jointly presented with fellow Sparkhound Principal Consultant Steve Schaneville)

My wife Christine will also be speaking three times at the event, and will almost certainly draw more folks to her sessions than any of mine will. Not jealous!

See you there!

Wednesday, March 14, 2018

What's New In SQL Server 2017? at the March '18 Baton Rouge SQL Server User Group

Thanks to everyone to attended the BRSSUG meeting tonight at our sponsor's location at the Baton Rouge Alliance Safety Council location and their awesome classroom training facility.

See you next month at the BRSSUG/BRDNUG meeting back at our usual location at the Technology Park.

All of our speakers recently at the user groups are local speakers, some speaking for the first time. It's great to see new local speakers growing into the role! Looking forward to hearing more from these and more home-grown tech speakers in the future!

Here's the slidedeck to download for my presentation of What's New in SQL Server 2017, let me know if you have any questions or followup.

Wednesday, March 07, 2018

SQL 2017 Administration Inside Out - link dump

Hello all-

More than a year's work went into the newest Inside Out book by Microsoft Press, I was happy to see it cross the finish last year and go into publication in February.

Here's a link dump of all the things in and around the book:
The writing team, in cover order:

Thursday, February 22, 2018

Activate Conference 2018: Presentation info

A special hello if you're visiting this blog post during or after my workshops on Thursday afternoon, February 22 at the Louisiana Tech Park as park of the Activate Conference 2018!

Here are all the links, downloads, and more you need for these two workshops on Thursday, Feb 22:

Databases 101 for the Aspiring App Dev
Student Track
2:45 PM - 4:15 PM

Your Applications and modern SQL Server 2017
Professional Track
4:30 PM - 6:00 PM

Links to get setup with SQL Server:

This is the link to download SQL for FREE:
The tools to dev in the Microsoft ecosystem are all free:
Download the WideWorldImporters sample database:

Presentation Downloads:

Slide decksDownload link  (.pptx files) 

ToolboxGithub (.sql files)
  • Look for toolbox.zip for an easy download, or 
  • Click on each file then download, or 
  • Click on each file, click “raw” on each file to copy/paste

Wednesday, February 21, 2018

Activate Conference 2018: Three days of Tech Learning

Tomorrow I'll be giving two workshops at the super-slick-branded Activate Conference 2018 in Baton Rouge at the Louisiana Technology Park, a conference filled with people and topics much cooler than me and my two database workshops on Thursday, February 22.

Activate is an event that aims to enrich our local tech community by establishing and furthering the careers of individuals in the web and technology field.

3-Day registration for the whole conference, which includes a day of workshops, a hackathon, and a Saturday conference, is free. VIP Admission is also available which includes a t-shirt, swag bag and kickoff dinner (tonight!).

On Saturday, Sparkhound will be hosting a table with our second annual Lego deconstruction competition, with an prize for the fastest times!

See you there, and best of luck to the organizers: Isral, Quinton, Lynsey, and all their many helpers and volunteers!

Thursday, February 15, 2018

"What's New in SQL 2017?" at the Baton Rouge SQL Server UG meeting

We had a great joint meeting tonight of the Baton Rouge SQL and .NET User Groups at the Louisiana Tech Park, and I gave the first version of a growing presentation on new features of SQL Server 2017 (with quite a few new SQL 2016 features in there for good measure).

Thanks to everyone who attended from both user groups!

Link to the presentation slidedeck here (including all the hidden slides I couldn't get to tonight).

I included a question from our SQL Sever 2017 Administration Inside Out author "fun" survey, you can read the whole survey on my blog.

Thanks to Thomas Leblanc for photo

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.