The Meltdown and Spectre attacks are newly announced ways of hacking CPUs. They affect both Windows and Linux. If you’re running under virtualization or cloud IaaS, any other running guest can try to leverage the attacks to compromise your unrelated guest.
These are a really big deal.
Cloud providers are forcing reboots as they patch things. Normally, that’d be the big punch line of this post – expect random reboots this week – but that only mentions a casual note here.
This morning, Microsoft published a KB article with SQL Server guidance. All versions of SQL Server are affected (other than SQL Server 2008 on Itanium, but who uses that?) Microsoft’s KB goes back to 2008, but there’s no reason to believe that 2005/2000 aren’t affected as well – they’re just no longer supported.
Microsoft’s guidance is concise and clear: if you’re running SQL Server on bare metal, and you trust the code, and no other code/apps are running, then you might be able to get by without patching. Everybody else is in for a rough road, though.
The KB article includes this performance advisory:
Microsoft continues to do performance evaluation on the patched binaries. However, at the time of publication, Microsoft has not yet validated SQL Server performance with all microcode patches, nor has it validated performance in all Linux environments. Customers are advised to evaluate the performance of their specific application when applying patches. Please validate the performance impact of enabling microcode changes before deploying into a production environment. Microsoft will update this section with more information when it is available.
That’s because some test results have found big slowdowns when the operating system is patched for Meltdown and/or Spectre. These are big vulnerabilities in the processors themselves, and OS vendors are having to make big changes that aren’t tuned for performance yet. Early benchmarks yesterday were showing 30% drops in PostgreSQL performance, but thankfully newer benchmarks have been showing smaller drops. Red Hat’s benchmarks show 3-7% slower analytics workloads, and 8-12% slower OLTP. They also caution that:
We expect the impact on applications deployed in virtual guests to be higher than bare metal due to the increased frequency of user-to-kernel transitions.
To know if the Meltdown & Spectre patches are affecting your performance, you’ll need to track wait stats before & after the patch. Wait stats trending is built into every SQL Server performance monitoring tool out there, or you can use the free/easy way: use the Power BI Dashboard for DBAs. Based on the early reports I’ve been reading from Postgres users, I wouldn’t be surprised to see higher SOS_SCHEDULER_YIELD and WRITELOG waits.
There’s no easy fix – you just gotta do performance tuning, throw more hardware at the problem, and/or hope that OS & app vendors learn lessons about the new bottlenecks in this new way of doing business, and release performance tuning patches quickly.
Here’s the heads-up part for DBAs.
This morning’s SQL Server 2017 CU3 and SQL Server 2016 SP1 CU7 are being treated as security updates. The KB article isn’t clear about exactly what changes were made for Meltdown & Spectre, but it does give a list of recommended mitigations if you can’t patch. It includes disabling CLR, disabling external scripts like R & Python, removing linked servers, and removing extended stored procedures.
That means your sysadmins may apply these SQL Server patches as part of their urgent patching routine, as the KBs note:
These patches affect SQL Server in other ways, not just as a single security patch. Remember, cumulative updates include all updates along the way – so 2016 SP1 CU7 includes everything from SP1 CU1, CU2, CU3, CU4, CU5, and CU6 too.
To learn what’s been patched, head on over to SQLServerUpdates.com where we maintain a list of detailed 2016 cumulative updates and 2017 cumulative updates.
Erik Says: If you scroll way down to the end of the KB 4073225, there’s a note about disabling CLR that really gets the noggin joggin.
Update Jan 4: Windows 2016, 2012R2, and 2008R2 are getting updates too, but no fix for 2008 or 2012 will be coming:
Update Jan 5: Microsoft says hold off on patching SQL Servers that act as SCCM back ends. (Thanks to Mark in the comments!)
26 Comments. Leave new
I’m wondering if this is going to impact people who use the Integration Services Catalog feature, which requires enabling CLR integration.
I’m hoping / betting not, as I’d expect the CLR code required for that would be “trusted,” but one never knows…
Time to get patching and testing, I guess.
I’d like to know this also. I’m assuming it always allow untrusted code if you enable CLR server config option. SQL 2017 enables “CLR strict security” by default. It looks like this feature can also be enabled on older versions by using a TF (https://support.microsoft.com/en-gb/help/4018930/update-adds-the-clr-strict-security-feature-to-sql-server-2016). Think this might be the answer to prevent untrusted code execution. I’m going to give it a go on a test server and see if it impacts SSIS CLR integration.
What’s the purpose of the SQL Server patches? Mitigate the performance impact of the OS patch? Fix incompatibility issues with the OS patch? Something else?
Eric – that’s a great question, and I wish that Microsoft was a little more clear about the answers. The fact that you’re asking me says a lot.
> Erik Says: If you scroll way down to the end of the KB, there’s a note about disabling CLR that really gets the noggin joggin.
sorry, can you clarify? i couldn’t find the term “CLR” anywhere in KB405856.
Yeah, the first KB, not that one: https://support.microsoft.com/en-us/help/4073225/guidance-for-sql-server
ah, thanks!
Guess who got all 120 SQL servers rebooted yesterday morning without warning? Yep, me.
Note of caution regarding patching SQL for SCCM:
https://www.reddit.com/r/SCCM/comments/7o9p3z/hold_off_on_patching_your_sccm_dbs_sql_server/
https://twitter.com/djammmer/status/949122372384141312
Ouch! Thanks for the heads up.
Hi Brent,
If I disable the extensibility (CLR and ‘external scripts enabled’,sp_addlinkedserver,sp_addextendedproc) ,
is it still required to install the SQL security patches for sql 2016?
Thanks
One of your follower 🙂
Sanjeev – given that Microsoft hasn’t told us what security patches are in this, you’ll need to ask them. The rest of us simply don’t know until Microsoft documents it. Sorry!
Thanks , last one more question/doubt ,Windows team planning to patch the all servers with in one week , is it ok ? because After windows patching 30% drops in PostgreSQL performance, right . what about for SQL server performance counters after OS patching and update on that ?
Thanks
Sanjeev – so, I’m gonna be brutally honest here: read the KBs in the post, and there’s performance advice from Microsoft. Don’t expect people to spoon-feed you here. This is a big deal.
Thanks
I haven’t seen anything on SQL2014 yet. I would assume this is affected as well. Does anyone know if Microsoft has a mitigation plan for that as well
Shaun – your best bet there would be to contact Microsoft. We only know what Microsoft has said publicly.
[…] Microsoft SQL Server specific: https://www.brentozar.com/archive/2018/01/sql-server-patches-meltdown-spectre-attacks/ […]
[…] References: How to check if your database server is protected against meltdown/spectre Speculative Attacks Technical Paper SQL Server Patches for Meltdown and Spectre Attacks […]
Additional info for SCCM:
https://blogs.technet.microsoft.com/configurationmgr/2018/01/08/additional-guidance-to-mitigate-speculative-execution-side-channel-vulnerabilities/
TLDR; Patch and follow guideliness in KB4073225 except for steps:
• Running SQL Server with CLR enabled (sp_configure ‘clr enabled’, 1)
• Using Linked Servers (sp_addlinkedserver)
Does anyone know the answers to the following:
1) If Windows Server patch is suppose to address this issue at the OS level, why do we need to also apply a SQL Server patch for the same issue?
2) Will the performance degradation be cumulative (e.g. SQL Server is slower because of the SQL Server patch and the OS Patch than with the OS patch alone)?
Thanks!
Michael
To answer question 1, since the flaw is at the hardware level, it affects all software. This is one of those situations where you have to forget the OSI model you may have learned in school. Software doesn’t sit “on top” of the OS when we are talking about communicating to hardware. A software program doesn’t have to go through the OS to send data to the processor. It can, and often does, go directly to the processor and ram via the requisite driver to read/write information. Because of this, an OS patch will not prevent someone from using the exploit on unpatched software. A driver patch could, in theory, plug this hole, but driver patches are rare and often pretty simple in terms of what they can do. Such a patch might be worse in terms of performance than simply patching all of your software.
For question 2, it really depends, but possibly. If the software writes directly to CPU or memory as above, the degradation would not be cumulative, but if there is some other process in which it talks to another software or to the OS itself, it is possible you will get hit twice on performance.
~Shaun
Are the SQL updates available through SCCM? We are running SCCM 1706 and I don’t see these updates in WSUS.
I performance tested our application after the CU7 update on the SQL server and found a 28% decrease in multi user capacity and 50% increase in average response times
Hi Brent- Just to clarify you say: “The KB article isn’t clear about exactly what changes were made for Meltdown & Spectre, but it does give a list of recommended mitigations if you can’t patch.”
Does this mean if you are on a patched version of SQL Server that you do not need to take additional mitigations such as disabling CLR, external scripts, etc.?
Nathan – the place to check would be Microsoft, not a blogger, heh. Go ahead and open a support ticket with Microsoft to find that out.