SQL Server Security - Instance Configurations

In previous posts I have described one of the steps in our security audit of a SQL Server instance where we checked logins and their permissions. One of our other steps is to check settings at the instance level. What is installed and how it is configured.

Active services
Check which SQL Server services are installed and which are actually in use. Every service installed poses a risk and therefore you should only install the services and features that are really needed. If they are needed at a later time, it is easy to add or activate them. If there are services that are not used, they should be uninstalled. Check if the following are installed and used per instance:

Database Engine
SQL Server Replication
Full-text and Semantic Extractions for Search
Data Quality Services
Analysis Services Engine
Reporting Services - Native and/or SharePoint
Integration Services
Master Data Services
Distributed Replay Server and Client
Various Tools and Clients
Documentation Components

Global settings
There are a number of settings that it is recommended to check because they pose a risk. The ones we check in particular are:

• Ad Hoc Distributed Queries - Should be inactive if not needed. Is a security risk because users can enter passwords in plain text when connecting to providers.
• Clr enabled - Should be inactive if not needed. Can be a security risk depending on whether there is control over what the objects do.
• Cross db ownership chaining - Allows rights to be bypassed between databases. Should be inactive.
• Xp-cmdshell - Should be inactive to avoid network and OS related attacks.

All configuration is in sys.configurations

SELECT name, value_in_use 
FROM sys.configurations
WHERE configuration_id IN (16391, 1562, 400, 102, 16390, 16393)

Enable login auditing

SQL Server has functionality to audit login attempts. During the audit, the information is written to the SQL Server error log. There are four different levels:

• None - No revision.
• Failed logins only - Only failed login attempts are audited which is normally the default setting.
• Successful logins only - Only successful login attempts are revised.
• Both failed and successful logins - All login attempts are audited.

At least "Failed logins only" must be selected. This allows you to detect, for example, a possible "brute force" attack or other unauthorized login attempts.

Check what is configured for the instance with:

DECLARE @level int
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'AuditLevel', @level OUTPUT
SELECT CASE WHEN @level = 0 THEN 'None'
WHEN @level = 1 THEN 'Successful logins only'
WHEN @level = 2 THEN 'Failed logins only'
WHEN @level = 3 THEN 'Both failed and successful logins' 
END AS [Selected revision level for login attempts]

Also check that there is an alert configured for severity 14 which is security related error.

SELECT CASE WHEN COUNT(sa.id) = 0 THEN 'Alert does not exist' ELSE 'Alert configured ' + CASE WHEN COUNT(so.id) = 0 THEN 'but no notification to operator' ELSE 'with notification to operator' END END AS [Alert status]
FROM msdb.dbo.sysalerts sa
LEFT OUTER JOIN msdb.dbo.sysnotifications sn ON sa.id = sn.alert_id
LEFT OUTER JOIN msdb.dbo.sysoperators so ON sn.operator_id = so.id
WHERE sa.severity = 14

Failed login attempts - Check if there are any failed login attempts in the SQL Server error log too:

EXEC master.dbo.xp_readerrorlog 0, 1, N'login failed', NULL, NULL, N'DESC

Allowed authentication methods
As far as possible, only Windows authentication should be allowed, which is much more secure than SQL authentication. If mixed mode is chosen, the reason for this should be verified and documented.

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Authentication' WHEN 0 THEN 'Windows and SQL Server Authentication' END AS [Authentication model]

Port and SQL Browser
It is normally recommended not to use the default port 1433 or dynamic ports but to change to a static one. It is also recommended not to use SQL Browser and instead specify the port in the connection string on the application page.

Check configured port:

DECLARE @port varchar(20), @key varchar(100)
IF CHARINDEX('',@@SERVERNAME,0) <>0 BEGIN
               SET @key = 'SOFTWAREMICROSOFTMicrosoft SQL Server'
+@@SERVICENAME+'MSSQLServerSupersocketnetlibTCP'
END ELSE BEGIN
               SET @key = 'SOFTWAREMICROSOFTMSSQLServerMSSQLServerSupersocketnetlibTCP'
END

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@key,@value_name='Tcpport',@value=@port OUTPUT
SELECT 'Port Number' = CONVERT(varchar(10),@port)

Control SQL Browser Service:

DECLARE @REGKEY nvarchar(128)
DECLARE @IsRunning table(running bit)
SET @REGKEY = 'SystemCurrentControlSetServicesSQLBrowser'
INSERT INTO @IsRunning
EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT running FROM @IsRunning) = 1 BEGIN
                      DECLARE @state table([state] nvarchar(25))
                      DECLARE @msg nvarchar(155)
                      INSERT INTO @state
                      EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',N'sqlbrowser'
                      SET @msg = 'SQL Browser Service is installed'
                      SELECT @msg = @msg + CASE [state] WHEN 'Running.' THEN 'and is active' ELSE 'but is not active at the moment' END
                      FROM @state
                      PRINT @msg
END ELSE BEGIN
                      PRINT 'SQL Browser Service is not installed'
END

By then we have gone through two of the three steps we check, login and instance configuration. The third step is the review of rights and settings at the database level. More on that next time.

/Björn