Portcullis Labs
Research and Development

MS SQL Server audit: Surface area reduction (part 2)

Published 15/02/2018 | By BLH

Continuing on from part 1, we will look other benchmark settings that will help to reduce the surface area of attack.

Other settings

There are a number of other settings in the Center for Internet Security (CIS) Security Benchmark for SQL Server relating to surface area reduction that should be considered:

  • Set is_trustworthy settings for each database in sys.databases to off (2.10 in CIS SQL Server 2008R2; 2.9 in CIS SQL Server 2012)
  • Disable unnecessary SQL Server protocols (2.11 in CIS SQL Server 2008R2; 2.10 in CIS SQL Server 2012)
  • Configure SQL Server to use non-standard ports (2.12 in CIS SQL Server 2008R2; 2.11 in CIS SQL Server 2012)
  • Set the “Hide Instance” option to Yes for Production SQL Server instances (2.13 in CIS SQL Server 2008R2; 2.12 in CIS SQL Server 2012)
  • Disable the sa Login Account by setting is_disabled in sys.server_principals to yes where sid is 0×01 (2.14 in CIS SQL Server 2008R2; 2.13 in CIS SQL Server 2012)
  • Rename the sa Login Account (2.15 in CIS SQL Server 2008R2; 2.14 in CIS SQL Server 2012)

Set is_trustworthy setting is off for each database

Using the catalog view of databases (sys.databases), check whether the database is trusted or not using the “is_trustworthy_on”. Here we want to ensure that databases are considered not trusted and set to disabled (0).

Note: The msdb database is excluded as disabling the trustworthy setting may cause unexpected behaviour in SQL Server components that use information from the msdb database.

A query can be constructed as follows:

SELECT name FROM sys.databases WHERE is_trustworthy_on = 1 AND name != 'msdb' AND state = 0;

The Trustworthy setting can also be observed using SQL Server Management Studio.

  • Within the Object Explorer, navigate to the SQL Server Instance and expand the path to “Databases”
  • Right-click on each database under ‘Databases’ and ‘Databases\System databases’ and select properties
  • Click on “Options” page and scroll down in the right pane to “Miscellaneous” where “Trustworthy” is seen
Trustworthy option for database in SQL Server Management Studio
image-3385

Trustworthy option for database in SQL Server Management Studio

Disable unnecessary SQL Server protocols

Ideally the number of SQL Server protocols enabled should be reduced. It is noted that CIS Security Benchmark do not score this particular issue.

The list of SQL Server Protocols can be found in SQL Server Configuration Manager. To see the settings:

  • Go to the SQL Server Network Configuration in object explorer and navigate to “Protocols for”
  • Ensure that only required protocols are enabled

Note: Microsoft does not specify exactly which protocols should be disabled. However, the setting for a default installation of SQL Server 2008R2 for example appears to be.

  • NP (Named Pipes) – Disabled
  • SM (Shared Memory) – Enabled
  • TCP – Enabled
  • Via – Disabled

The following is query that performs a check on the protocols by checking the registry entry for each of the 4 possible protocols, NP (Named Pipes), SM (Shared Memory), TCP and Via. Note: It makes 4 separate queries for each protocol by finding whether registry entry is enabled or not with the results combined together using a union.

DECLARE @InstanceName nvarchar(50)
DECLARE @MajorVersion decimal
DECLARE @RegKey_Instance nvarchar(500)
DECLARE @RegValue_Instance VARCHAR(100)

DECLARE @RegKey nvarchar(500)
DECLARE @Value_Sm int
DECLARE @Display_Sm VARCHAR(20)
DECLARE @Value_Np int
DECLARE @Display_Np VARCHAR(20)
DECLARE @Value_TCP int
DECLARE @Display_TCP VARCHAR(20)
DECLARE @Value_Via int
DECLARE @Display_Via VARCHAR(20)

-- Get the Instance Name. Default is 'MSSQLSERVER'
SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),'MSSQLSERVER'))

-- Get the Major version number. 8=SQL2000, 9=SQL2005, 10=SQL2008, 11=SQL2012
-- Convert first 2 characters ('8.', '9.', '10', '11'). Convert to Decimal
SET @MajorVersion=CONVERT(decimal,CONVERT(varchar(2),(SERVERPROPERTY('ProductVersion'))))

-- Get the RegKey for Instance Name (e.g. 'MSSQLSERVER')
SET @RegKey_Instance='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

-- Get the RegValue for Instance Name (e.g. 'MSSQL10_50.MSSQLSERVER')
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey_Instance,
  @value_name = @InstanceName,
  @value = @RegValue_Instance OUTPUT

-- Get the RegKey for SM (Shared Memory) and whether protocol is enabled
SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@RegValue_Instance+'\MSSQLServer\SuperSocketNetLib\Sm'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled',
  @value = @Value_Sm OUTPUT

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @Display_Sm OUTPUT

-- Get the RegKey for Np (Named Pipes) and whether protocol is enabled
SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@RegValue_Instance+'\MSSQLServer\SuperSocketNetLib\Np'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled',
  @value = @Value_Np OUTPUT

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @Display_Np OUTPUT

-- Get the RegKey for TCP and whether protocol is enabled
SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@RegValue_Instance+'\MSSQLServer\SuperSocketNetLib\TCP'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled',
  @value = @Value_TCP OUTPUT

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @Display_TCP OUTPUT

-- Get the RegKey for Via and whether protocol is enabled
SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@RegValue_Instance+'\MSSQLServer\SuperSocketNetLib\Via'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled',
  @value = @Value_Via OUTPUT

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @Display_Via OUTPUT

SELECT @Display_Np as DisplayName, @Value_Np as Enabled
UNION SELECT @Display_Sm, @Value_Sm
UNION SELECT @Display_TCP, @Value_TCP
UNION SELECT @Display_Via, @Value_Via

The following is a sample result from a SQL Server 2008R2 database instance:

DisplayName          Enabled
-------------------- -----------
Named Pipes                    0
Shared Memory                  1
TCP/IP                         1
VIA                            0

Check port is running for SQL Server

An ideal practice is to configure the SQL server instance to not use the default TCP port of 1433. Using a non-default port helps protect the database from attacks directed to the default port. It is noted that CIS Security Benchmark do no score this particular issue.

This can be checked using netstat and looking for port 1433 in command prompt:

C:\>netstat -ano

Or with PowerShell:

C:\>netstat -ano | select-string 1433.+listening

Alternatively, SQL Server Configuration Manager can be used to see what port is set:

  • Go to the SQL Server Network Configuration in object explorer and navigate to “Protocols for”
  • Right-click on “TCP”
  • Click on the “IP Protocols” tab
  • Observe the IP address and port that has been set

You should also ensure the interface for each IP address (particularly Internet interface) is not enabled.

A query that uses the registry settings for the TCP service used by SQL Server can be made and the port returned as follows:

DECLARE @InstanceName nvarchar(50)
DECLARE @RegKey VARCHAR(100)
DECLARE @PortNumber VARCHAR(20)

-- Get the Instance Name. Default is 'MSSQLSERVER'
SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),'MSSQLSERVER'))

SET @RegKey = 'SOFTWARE\MICROSOFT\MSSQLServer\'+@InstanceName+'\Supersocketnetlib\TCP'
EXEC xp_regread
  @rootkey='HKEY_LOCAL_MACHINE', @key=@RegKey,
  @value_name='Tcpport', @value=@PortNumber OUTPUT

SELECT @InstanceName as Instance, @PortNumber as Port

Hidden option

Another good practice is to configure SQL Server instances within production environments as hidden to prevent advertisement by the SQL Server Browser service.

The “Hide Instance” state can be found in SQL Server Configuration Manager. To see the settings:

  • Go to the SQL Server Network Configuration in object explorer and navigate to “Protocols for”
  • Right-click “Protocols for”, and then select “Properties”
  • On the “Flags” tab, observe the “Hide Instance” box
  • In the “Hide Instance” box, select Yes to enable hiding the server instance

The following query can be used to determine if the server instance is hidden:

DECLARE @InstanceName nvarchar(50)
DECLARE @MajorVersion decimal
DECLARE @RegKey_Instance nvarchar(500)
DECLARE @RegValue_Instance VARCHAR(100)</pre>
DECLARE @RegKey nvarchar(500)
DECLARE @Value int

-- Get the Instance Name. Default is 'MSSQLSERVER'
SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),'MSSQLSERVER'))

-- Get the Major version number. 8=SQL2000, 9=SQL2005, 10=SQL2008, 11=SQL2012
-- Convert first 2 characters ('8.', '9.', '10', '11'). Convert to Decimal
SET @MajorVersion=CONVERT(decimal,CONVERT(varchar(2),(SERVERPROPERTY('ProductVersion'))))

-- Get the RegKey for Instance Name (e.g. 'MSSQLSERVER')
SET @RegKey_Instance='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

-- Get the RegValue for Instance Name (e.g. 'MSSQL10_50.MSSQLSERVER')
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @RegKey_Instance,
@value_name = @InstanceName,
@value = @RegValue_Instance OUTPUT

-- Get the RegKey for SM (Shared Memory) and whether protocol is enabled
SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@RegValue_Instance+'\MSSQLServer\SuperSocketNetLib'
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @RegKey,
@value_name = 'HideInstance',
@value = @Value OUTPUT

SELECT @InstanceName as 'InstanceName', @Value as 'HideInstance'

Check sa account has been disabled

A good practice is to have the widely known system admin account sa disabled. Enforcing this control reduces the probability of an attacker executing brute force attacks.

The following query shows the name of the system admin account, which sa by default and whether it is disabled:

SELECT name, is_disabled
FROM sys.server_principals
WHERE sid = 0x01;

Check sa account has been renamed

A good practice is to have the widely known system admin account sa disabled. It is more difficult to launch password-guessing and brute-force attacks against the sa account if the username is not known.

The following query lists the name of the system admin account:

SELECT name
FROM sys.server_principals
WHERE sid = 0x01;

Summary

In this article, you have seen a few more settings that can be used to configure to reduce the surface area of attack and improve the security of the SQL Server following good practices and the CIS Security Benchmark.

You can audit manually looking at SQL Server Management Studio and SQL Server Configuration Manager. You can also create a query that will read the catalog information for the databases and the registry to gather the information about the server instance.


Request to be added to the Portcullis Labs newsletter

We will email you whenever a new tool, or post is added to the site.

Your Name (required)

Your Email (required)