Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

SQL Servers Assessment for the Meltdown and Spectre Vulnerabilities


By:   |   Last Updated: 2018-01-25   |   Comments (6)   |   Related Tips: More > 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:

SQL Server Version Service Pack / CU level Reference Final Build Number After Patching
Microsoft SQL Server 2017 GDR KB4057122 14.0.2000.63
RTM CU3 KB4058562 14.0.3015.40
Microsoft SQL Server 2016 GDR SP1 KB4057118 13.0.4210.6
SP1 CU7 KB4058561 13.0.4466.4
CU KB4058559 13.0.2218.0
GDR KB4058560 13.0.1745.2
Microsoft SQL Server 2014 SP2 CU10 KB4057117 12.0.5571.0
SP2 GDR KB4057120 12.0.5214.6
Microsoft SQL Server 2012 SP4 GDR KB4057116 11.0.7462.6
SP3 GDR KB4057115 11.0.6260.1
SP3 CU KB4057121 11.0.6615.2
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":

Central Management Server
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:

Initial Security Assessment 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
xp_cmdshell permissions

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":

Assemblies list

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
Assemblies permissions

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:

Even log example (CLR) - 2

Here are events on SQL Server that host the SSIS Catalog database:

Even log example (CLR) - SSIS

Note that the latest log (log ID = 0) may not have enough events and you may need to check past logs too:

Even log example (CLR) - multiple logs

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
Extended Procedures and Permissions

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
ActiveX SQL Agent Job step

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 
Linked Servers list

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:

Security Assessment Results after the Remediation

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.


Last Updated: 2018-01-25


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, March 27, 2018 - 10:49:48 PM - Svetlana Golovko Back To Top

 

 Please note, that a bug in the main script was fixed (only SQL Server 2017 check was affected).


Saturday, February 10, 2018 - 3:43:34 PM - Svetlana Golovko Back To Top

 The tip was updated to fix the logic in the versions check and also to add one more patch level check.


Thursday, February 08, 2018 - 4:06:21 PM - Jeremy Back To Top

And actually looks like there is a 7462 build so guessing you need to put the checks fir major version 11 and build 6615 and build 6260 above the check for build < 7462.

 

(SELECT CASE WHEN MajorVersion = 10 AND Build < 6556
 THEN 'Yes' -- both - SQL Server 2008 and SQL Server 2008 R2
 WHEN MajorVersion = 11 AND Build = 6615
 THEN 'No'
 WHEN MajorVersion = 11 AND Build = 6260
 THEN 'No'
 WHEN MajorVersion = 11 AND Build < 7462
 THEN 'Yes'


Thursday, February 08, 2018 - 3:50:54 PM - Jeremy Back To Top

Looks like there is also an isue with  

(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
 THEN 'Yes'
 WHEN MajorVersion = 11 AND Build = 6615
 THEN 'No'
 WHEN MajorVersion = 11 AND Build = 6260
 THEN 'No'
 WHEN MajorVersion = 12 AND Build < 5571
 THEN 'Yes'

I am not up to speed on builds, but shouldn't :

WHEN MajorVersion = 11 AND Build < 7462

Be :

WHEN MajorVersion = 11 AND Build < 6462

?

Otherwise we never hit :

WHEN MajorVersion = 11 AND Build = 6615

We are on 11 6615 and the script (as is) shows as neding to be patched.

 

 


Tuesday, January 30, 2018 - 7:59:36 AM - Greg Robidoux Back To Top

Thanks Linda.

The code has been fixed.

-Greg


Monday, January 29, 2018 - 5:02:36 PM - Linda LF Back To Top

 Is there a mistake on like #12 of the first script :

IF (SELECT CAST(SERVERPROPERTY('IsPolybaseInstalled') AS SMALLINT) +

Since there is nothing past the [+] ?  This seems to be lacking whatever other criteria was supposed to be applied to the IF statement.  What is this SELECT statement supposed to evaluate to being?

 


Learn more about SQL Server tools