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.
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:
SQL Server Replication
Full-text and Semantic Extractions for Search
Data Quality Services
Analysis Services Engine
Reporting Services - Native and/or SharePoint
Master Data Services
Distributed Replay Server and Client
Various Tools and Clients
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.