SQL Servers Assessment for the Meltdown and Spectre Vulnerabilities

By: Svetlana Golovko   |   Updated: 2018-01-25   |   Comments (6)   |   Related: > Security

Problem

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?

Solution

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.

Patching Scenarios

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:

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.

xp_cmdshell

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.

CLR

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.

External Scripts

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.

Next Steps

• 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.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips