The Right Database Monitoring Tools Make All the Difference
Tuesday, June 26, 2018 - click here to learn more and to register
2018 started out with bad news for most IT professionals. A new “speculative execution side-channel attacks” vulnerability affected many modern processors and operating systems. This vulnerability is very complex and requires patching on many layers, including hardware, operating systems and the application layer.
Database Administrators (DBAs) have to be prepared to patch every SQL Server starting with SQL Server version 2008 and the recommended patching procedure is different for different servers depending on SQL Server configuration settings and features used.
How can we determine what servers are affected and what steps should we take to protect SQL Server?
In this tip we will provide a "Meltdown/Spectre SQL Server Assessment Script" that you can run in Central Management Server (CMS) against all of your SQL Servers. This script will produce a report with recommendations/scenarios for patching and display configurations/features that are not secure and enabled on SQL Server.
Then we will provide a couple of remediation steps to review unsecure use cases.
After remediation we will run the Main Assessment Script again and review the final recommendations.
In this article (which is being updated daily) Microsoft provides guidance for SQL Server patching for “speculative execution side-channel attacks” vulnerabilities. There are the following patching scenarios provided in this article:
- First is for cloud platforms (SQL Azure and Data Warehouse) that does not require any patching
- Second involves only SQL Server patching (see details in the article)
- Third is the most complicated one and involves SQL Server patching as well as additional steps
- Fourth is for SQL Server on Linux, so only SQL Server 2017 is affected
What do we check?
The Main Assessment Script will check SQL Server versions and configuration settings/features that put your server at risk. The script will generate a basic report that later can be used for security patch planning.
SQL Server Version Check
Below is a table with SQL Server versions (last column) that are required in order to be protected:
|SQL Server Version||Service Pack / CU level||Reference||Final Build Number After Patching|
|Microsoft SQL Server 2017||GDR||KB4057122||14.0.2000.63|
|Microsoft SQL Server 2016||GDR SP1||KB4057118||13.0.4210.6|
|Microsoft SQL Server 2014||SP2 CU10||KB4057117||12.0.5571.0|
|Microsoft SQL Server 2012||SP4 GDR||KB4057116||11.0.7462.6|
|Microsoft SQL Server 2008 SP4||SP4 GDR||KB4057114||10.0.6556.0|
|Microsoft SQL Server 2008 R2||SP3 GDR||KB4057113||10.50.6556.0|
Please note that the patch list is current as of January 19, 2018. Microsoft as well as other vendors are still working on releasing additional patches and updates, for the list of available fixes see this article.
To add an additional patch check for the new patches add another "Build" condition in the "patch" CTE in the Main Script:
... patch AS (SELECT CASE WHEN MajorVersion = 10 AND Build < 6556 ...
To update the Product Build version if it's changed by Microsoft for the existing patch just update the "Build" condition.
Extensibility Features/Mechanisms Check
The following extensibility features/mechanisms are checked in addition to the SQL Server version. If any of them are enabled, the patching scenario may require additional steps (Scenario #3):
- Using xp_cmdshell
- Using COM Objects via sp_OACreate
- CLR enabled
- External scripts enabled for running R/Python external scripts
- Using Extended Stored Procedures
- SQL Agent running ActiveX Scripts
- Using Linked Servers
Meltdown/Spectre SQL Server Assessment Main Script
We will run this script in Central Mangaement Servers (CMS) against our "Meltdown Group":
USE [master] GO DECLARE @features NVARCHAR(256), @os_ver NVARCHAR(256) -- Server Configuration Options Enabled SELECT @features = COALESCE(@features + ', ' + [name], [name]) FROM sys.configurations WHERE [name] IN ('xp_cmdshell', 'Ole Automation Procedures', 'clr enabled', 'external scripts enabled') AND value_in_use =1; -- R/Python related binary installed IF (SELECT CAST(SERVERPROPERTY('IsPolybaseInstalled') AS SMALLINT) + CAST(SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS SMALLINT)) > 0 BEGIN SELECT @features = COALESCE(@features + ', ' + 'R/Python related binary installed', 'R/Python related binary installed') END -- Extended Procedures IF EXISTS (SELECT [object_id] FROM sys.all_objects WHERE [type] = 'X' and is_ms_shipped = 0 ) BEGIN SELECT @features = COALESCE(@features + ', ' + 'Non-MS Extended Procedure(s)', 'Non-MS Extended Procedure(s)') END -- SQL Server Agent Jobs with ActiveX step IF EXISTS (SELECT j.job_id FROM msdb.dbo.sysjobsteps s JOIN msdb.dbo.sysjobs j ON s.job_id = j.job_id WHERE s.subsystem = 'ActiveScripting') BEGIN SELECT @features = COALESCE(@features + ', ' + 'SQL Agent job(s) with ActiveX', 'SQL Agent job(s) with ActiveX') END -- Linked Servers IF EXISTS (SELECT server_id FROM sys.servers WHERE is_linked = 1 OR server_id > 0 ) BEGIN SELECT @features = COALESCE(@features + ', ' + 'Linked Server(s)', 'Linked Server(s)') END SET @os_ver = 'Windows' IF (SELECT SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(200)), 1, CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(200)),1) -1 ) ) > 13 BEGIN SELECT @os_ver = host_platform FROM sys.dm_os_host_info END ; WITH ver AS -- Current Versions (SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion, SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(200)), 1, CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(200)),1) -1 ) AS MajorVersion, RIGHT(SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(30)), 1, LEN(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(30)))-CHARINDEX('.', REVERSE(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(30))))), 4) AS Build ), patch AS -- Comparing with Patch (SELECT CASE WHEN MajorVersion = 10 AND Build < 6556 THEN 'Yes' -- both - SQL Server 2008 and SQL Server 2008 R2 WHEN MajorVersion = 11 AND Build < 7462 AND Build NOT IN (6615, 6260) THEN 'Yes' WHEN MajorVersion = 12 AND Build < 5571 AND Build NOT IN (5214) THEN 'Yes' WHEN MajorVersion = 13 AND Build < 4466 AND Build NOT IN (4210, 2218, 1745) THEN 'Yes' WHEN MajorVersion = 14 AND Build < 3015 AND Build NOT IN (2000) THEN 'Yes' WHEN MajorVersion < 10 THEN 'NA' -- version is lower than SQL Server 2008 and not supported ELSE 'No' END AS SQLPatchIsRequired FROM ver ) SELECT CASE WHEN SQLPatchIsRequired = 'Yes' AND @features IS NULL THEN 'Scenario 2' WHEN SQLPatchIsRequired = 'Yes' AND @features IS NOT NULL AND @os_ver = 'Windows' THEN 'Scenario 3' WHEN SQLPatchIsRequired = 'Yes' AND @features IS NOT NULL AND @os_ver = 'Linux' THEN 'Scenario 4' WHEN SQLPatchIsRequired = 'No' AND @features IS NULL THEN 'Fully Patched' WHEN SQLPatchIsRequired = 'No' AND @features IS NOT NULL THEN 'SQL Patch installed, review additional steps' WHEN SQLPatchIsRequired = 'NA' THEN 'Patch is Not Available - Unsupported Version' END AS patching_scenario, @features AS unsec_features_enabled, * FROM ver, patch GO
Here are the results:
As we can see only 3 servers out of 14 are patched or need Scenario #2 for patching.
Remediation Before the Patching
Ideally, we want to install only the SQL Server patch (Scenario #2) and don't perform additional steps that are required for Scenario #3.
We are going to review all issues listed in the third column and then run our Main Assessment Script again.
We will run scripts below to find more about unsecured features that are enabled and validate if we need them.
In addition to the following configuration option check:
USE [master] GO SELECT [name], value_in_use FROM sys.configurations WHERE [name] = 'xp_cmdshell' AND value_in_use =1 GO
We will check if there are any explicit permissions are set on the xp_cmdshell procedure:
USE [master] GO SELECT p.major_id, object_name(p.major_id ) as obj_name, l.name , state_desc, permission_name FROM sys.database_permissions p JOIN sys.database_principals l ON p.grantee_principal_id = l.principal_id WHERE object_name(p.major_id ) = 'xp_cmdshell' GO
If we know that xp_cmdshell is not used we should perform the following steps:
USE [master] GO REVOKE EXECUTE ON [sys].[xp_cmdshell] TO [TempDBA] GO EXEC sys.sp_configure 'xp_cmdshell', 0 GO RECONFIGURE GO
Read this tip to find out if disabling xp_cmdshell in SQL Server is really secure.
Using COM Objects via sp_OACreate
You will need to check with vendors before you disable this feature if it's used for an application or monitoring OLE Automation Stored Procedure usage.
Run the following script to disable the Ole Automation Procedures feature:
USE [master] GO EXEC sys.sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sys.sp_configure 'Ole Automation Procedures', 0 GO RECONFIGURE GO EXEC sys.sp_configure 'show advanced options', 0 GO RECONFIGURE GO
Read this tip about different ways to monitor stored procedure usage.
We will perform several checks before we decide if we can disable CLR.
First, we will check if there are any non-Microsoft assemblies installed:
USE [master] GO SELECT assembly_id, name, permission_set_desc FROM [sys].[assemblies] GO
In our case we have only one Microsoft's assembly on all our Demo servers - "Microsoft.SqlServer.Types":
This check by itself doesn't provide enough information about CLR use.
The next step is to check permissions related to the CLR:
USE [master] GO SELECT p.class, p.class_desc, p.major_id, p.minor_id, p.grantee_principal_id, p.grantor_principal_id, p.[type], p.[permission_name], p.[state], p.state_desc, l.[name] FROM sys.server_permissions p JOIN sys.server_principals l ON p.grantee_principal_id = l.principal_id WHERE p.[permission_name] LIKE '%ASSEMBLY%' GO
Note the Master Data Services (MDS) login in the first row. In our example we can safely disable CLR on DEMO_MDS server (where MDS is not installed anymore):
USE [master] GO EXEC sys.sp_configure 'clr enabled', 0 GO RECONFIGURE GO
We know at this point that on DEMO_SSIS SQL Server we can't disable CLR as this configuration option is required for the SSIS Catalog.
The next check we need to run on the remaining SQL Servers where CLR is enabled. We are going to check SQL Server logs for the messages related to the assemblies:
USE [master] GO EXEC master.dbo.xp_readerrorlog 0, 1, N'appdomain' GO
If CLR is enabled, but not used you may see just a single event:
Here are events on SQL Server that host the SSIS Catalog database:
Note that the latest log (log ID = 0) may not have enough events and you may need to check past logs too:
In our case we have enough evidence that CLR is in use on the remaining DEMO servers, so we can't disable it. Our patching scenario will be Scenario #3.
Microsoft’s definition of the external scripts in our case is:
"R and Python packages running through the external scripts mechanism or run from the standalone R/Machine Learning studio on the same physical machine as SQL Server."
In addition to the following configuration option check:
USE [master] GO SELECT [name], value_in_use FROM sys.configurations WHERE [name] = 'external scripts enabled' AND value_in_use =1 GO
We will check if there are any permissions related to this feature:
USE [master] GO SELECT p.class, p.class_desc, p.major_id, p.minor_id, p.grantee_principal_id, p.grantor_principal_id, p.[type], p.[permission_name], p.[state], p.state_desc, l.[name] FROM sys.server_permissions p JOIN sys.server_principals l ON p.grantee_principal_id = l.principal_id WHERE p.[permission_name] LIKE '%EXECUTE ANY EXTERNAL SCRIPT%' GO
Read this article about executing external scripts.
Here is how you disable the external scripts configuration option:
USE [master] GO EXEC sys.sp_configure 'external scripts enabled', 0 GO RECONFIGURE WITH OVERRIDE GO
Read more about executing R Code in SQL Server here.
Microsoft refers only to the configuration option for R and Python Code on SQL Server, but you may need to perform the following check as well if R/Python Code runs from the standalone R/Machine Learning studio on SQL Server host:
USE [master] GO SELECT CASE WHEN SERVERPROPERTY('IsPolybaseInstalled') + SERVERPROPERTY('IsAdvancedAnalyticsInstalled') > 0 THEN 'Installed' ELSE 'Not Installed' END GO
Non-Microsoft Extended Procedures
In this check we need to find out if there are any Extended Procedures that are not shipped by Microsoft:
USE master GO SELECT o.[name], o.type_desc, l.name login_name, state_desc, permission_name FROM sys.all_objects o LEFT JOIN sys.database_permissions p ON o.object_id =p.major_id LEFT JOIN sys.database_principals l ON p.grantee_principal_id = l.principal_id WHERE o.[type] = 'X' and o.is_ms_shipped = 0 GO
These are valid Extended Procedures that ae required to run an application.
Read this tip to learn how to secure SQL Server Extended Procedures.
SQL Agent running ActiveX Scripts
This script will find SQL Agent Jobs that have an ActiveX step:
USE msdb GO SELECT j.[name] AS job_name, s.step_name, 'NNN' AS command, s.subsystem FROM msdb.dbo.sysjobsteps s JOIN sysjobs j ON s.job_id = j.job_id WHERE s.subsystem = 'ActiveScripting' GO
Consider replacing ActiveX steps with, for example, PowerShell steps where possible.
Read this note from Microsoft about limitations and restrictions of using ActiveX job steps. Note, that the ActiveX subsystem is not available anymore in SQL Server 2016 and higher versions.
Linked Servers with Non-Microsoft Providers
The following check is for the SQL Servers with Linked Servers:
SELECT [name] as linked_srv_name, product, provider, data_source,s.server_id, is_linked, is_remote_login_enabled, l.local_principal_id, uses_self_credential, remote_name FROM sys.servers s JOIN sys.linked_logins l ON s.server_id = l.server_id WHERE s.is_linked = 1
As per Microsoft's guidance, non-Microsoft providers are considered to be unsafe.
In our example we have 2 linked servers that are not in use anymore (on DEMO_MDS and DEMO_LINKSRV2). We can remove them safely:
USE [master] GO EXEC dbo.sp_dropserver @server=N'TI', @droplogins='droplogins' GO
The third linked server ("AD") to the Active Directory is using the Microsoft Provider, so we are going to keep it.
Final Check and Results
At this point, we have validated each Feature/Configuration Option use and we will run our Main Assessment Script again:
Note that after the remediation we have 6 out of 14 servers that will require extra steps (Scenario #3) for the protection comparing to the initial 11 servers.
Note also that SQL Server 2005 is not supported anymore by Microsoft and patches are not available. Running SQL Server 2005 presents a security risk to your environment. Consider upgrading any servers older than SQL Server 2008 versions to newer versions. Keep in mind that Extended Support for SQL Server 2008 and SQL Server 2008 R2 ends on 2019-07-09.
- Make sure that you update your DBA documentation to verify patch levels before enabling any features we reviewed above.
- Keep track of the newly released Microsoft SQL Server patches related to the “speculative execution side-channel attacks” vulnerabilities here.
- Read other Security tips here.
- Check out Central Management Server tips here.
- Here are tips about SQL Server Configurations.
Last Update: 2018-01-25
About the author
View all my tips