Synergetic databases and applications require adequate security configuration through multiple layers of the environment. The objective of this document is to provide guidelines to hardening a Microsoft SQL server.

Whilst all care has been taken in preparing this guide, Education Horizons Group does not warrant that the contents of this guide (i.e. information, recommendations, opinions or conclusions contained in this guide (“Information”)) is accurate, reliable, complete or current. The Information does not purport to contain all matters relevant to the usage of Synergetic software. The Information has been prepared on the basis of circumstances and technology current as at the date of the report and care should be taken by the School to determine if circumstances have changed in a manner which would affect the Information. To the extent permissible by law, Education Horizons Group shall not be liable for any errors, omissions, defects or misrepresentations in the Information or for any loss or damage suffered by persons who use or rely on such Information (including by reasons of negligence, negligent misstatement or otherwise). If any law prohibits the exclusion of such liability, Synergetic limits its liability to the re-supply of the Information, provided that such limitation is permitted by law and is fair and reasonable.

The recommendations herein provided are based on the Center for Internet Security (CIS) hardening guides and benchmarks for SQL server 2016 or above running on Windows Server 2016 or above. Each recommendation should be considered with reference to the specific environment requirements. Changes may result in applications not functioning as expected, particularly where there configuration differs from the base products.

A server level backup as well as a backup of the individual configuration files should be taken prior to making any changes. 

The server will need to be rebooted after the changes have been made

All commands supplied are to be run in an elevated command shell or elevated PowerShell as required or SQL Server management studio, connected as a priviledged user.

Installation, updates and patches

Ensure Latest SQL Server Service Packs and Hotfixes are Installed

SQL Server patches contain program updates that fix security and product functionality issues found in the software. These patches can be installed with a hotfix which is a single patch, a cumulative update which is a small group of patches or a service pack which is a large collection of patches.

Audit

SELECT SERVERPROPERTY('ProductLevel') as SP_installed,
SERVERPROPERTY('ProductVersion') as Version;

Remmediation

Identify the current version and patch level of your SQL Server instances and ensure they contain the latest security fixes. Make sure to test these fixes in your test environments before updating production instances. The most recent SQL Server patches can be found at:

• Hotfixes and Cumulative updates: https://docs.microsoft.com/en-us/sql/databaseengine/install-windows/latest-updates-for-microsoft-sql-server?view=sql-serverver15&viewFallbackFrom=sql-server-2016
• Service Packs: https://support.microsoft.com/en-us/help/3177534/how-to-obtainthe-latest-service-pack-for-sql-server-2016

Ensure Single-Function Member Servers are Used

It is recommended that SQL Server software be installed on a dedicated server - that is the server is only used to host SQL, particularly in production. (Often, in testing environments, the server is both SQL, and Web.)  This architectural consideration affords security flexibility in that the database server can be placed on a separate subnet allowing access only from particular hosts and over particular protocols.

Audit

Ensure that no other roles are enabled for the underlying operating system and that no excess tooling is installed.

Remmediation

Uninstall excess tooling and/or remove unnecessary roles from the underlying operating system.

Surface area reduction

SQL Server offers various configuration options, some of them can be controlled by the sp_configure stored procedure

Ensure 'Ad Hoc Distributed Queries' Server Configuration Option is set to '0'

Enabling Ad Hoc Distributed Queries allows users to query data and execute statements on external data sources. This functionality should be disabled.

Audit

SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = 'Ad Hoc Distributed Queries';

Remediation

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;

Ensure 'Cross DB Ownership Chaining' Server Configuration Option is set to '0'

The cross db ownership chaining option controls cross-database ownership chainingacross all databases at the instance (or server) level. Note.  Cross DB Ownership chaining is required for the ICON environments.

Audit

SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = 'cross db ownership chaining';

Remediation

EXECUTE sp_configure 'cross db ownership chaining', 0;
RECONFIGURE;
GO

Ensure 'Ole Automation Procedures' Server Configuration Option is set to '0'

The Ole Automation Procedures option controls whether OLE Automation objects can be instantiated within Transact-SQL batches. These are extended stored procedures that allow SQL Server users to execute functions external to SQL Server.

Audit

SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = 'Ole Automation Procedures';

Both value columns must show 0 to be compliant.

Remediation

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;

Ensure 'Remote Access' Server Configuration Option is set to '0'

The remote access option controls the execution of local stored procedures on remote servers or remote stored procedures on local server. This will also disable linked server functionality. Consider if this is required.

Audit

SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = 'remote access';

Both value columns must show 0.

Remediation

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'remote access', 0;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;

Ensure 'Remote Admin Connections' Server Configuration Option is set to '0'

The remote admin connections option controls whether a client application on a remote computer can use the Dedicated Administrator Connection (DAC). If in a cluster environment, DAC can be used to manage the cluster. Consider this if required.

Audit

USE master;
GO
SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = 'remote admin connections'
AND SERVERPROPERTY('IsClustered') = 0;

If no data is returned, the instance is a cluster and this recommendation is not applicable. If data is returned, then both the value columns must show 0 to be compliant.

Remediation

EXECUTE sp_configure 'remote admin connections', 0;
RECONFIGURE;
GO

Ensure 'Scan For Startup Procs' Server Configuration Option is set to '0'

The scan for startup procs option, if enabled, causes SQL Server to scan for and automatically run all stored procedures that are set to execute upon service startup.

Audit

SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = 'scan for startup procs';

Remediation

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'scan for startup procs', 0;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;

Ensure Unnecessary SQL Server Protocols are set to 'Disabled'

SQL Server supports Shared Memory, Named Pipes, and TCP/IP protocols. However, SQL Server should be configured to use the bare minimum required. By default, Synergetic only requires TCP/IP to be enabled. Consider the environment requirements before changing.

Audit

Open SQL Server Configuration Manager; go to the SQL Server Network Configuration. Ensure that only required protocols are enabled.

Remediation

Open SQL Server Configuration Manager; go to the SQL Server Network Configuration. Ensure that only required protocols are enabled. Disable protocols not necessary.

Ensure 'Hide Instance' option is set to 'Yes' for Production SQL Server instances (Non clustered instances only)

Non-clustered SQL Server instances within production environments should be designated as hidden to prevent advertisement by the SQL Server Browser service. Do not set this for a clustered environment.

Audit

1. In SQL Server Configuration Manager, expand SQL Server Network
   Configuration, right-click Protocols for <InstanceName>, and then select
   Properties.
2. On the Flags tab, in the Hide Instance box, if Yes is selected, it is compliant.

Remediation

1. In SQL Server Configuration Manager, expand SQL Server Network
   Configuration, right-click Protocols for <InstanceName>, and then select
   Properties.
2. On the Flags tab, in the Hide Instance box, select Yes, and then click OK to close the
   dialog box. The change takes effect immediately for new connections

Ensure the 'sa' Login Account is set to 'Disabled'

The sa account is a widely known and often widely used SQL Server account with sysadmin privileges. This is the original login created during installation and always has the principal_id=1 and sid=0x01.

Before making any changes, ensure that an alternative sysadmin account exists and logon is known and tested. Without this, the sa account cannot be reenabled easily. Do not delete the sa account. See appendix.

Audit

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

No rows should be returned to be compliant.
An is_disabled value of 0 indicates the login is currently enabled and therefore needs remediation.

Remediation

Ensure an alterative sysadmin privilege account has been created. Failure to do so will leave the database inaccessable for sysadmin commands.

USE [master]
GO
DECLARE @tsql nvarchar(max)
SET @tsql = 'ALTER LOGIN ' + SUSER_NAME(0x01) + ' DISABLE'
EXEC (@tsql)
GO

Ensure 'xp_cmdshell' Server Configuration Option is set to '0'

The xp_cmdshell option controls whether the xp_cmdshell extended stored procedure can be used by an authenticated SQL Server user to execute operating-system command shell commands and return results as rows within the SQL client.

Audit

SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = 'xp_cmdshell';

Both value columns must show 0 to be compliant.

Remediation

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;

Ensure 'AUTO_CLOSE' is set to 'OFF' on contained databases

AUTO_CLOSE determines if a given database is closed or not after a connection terminates. If enabled, subsequent connections to the given database will require the database to be reopened and relevant procedure caches to be rebuilt

Audit

SELECT name, containment, containment_desc, is_auto_close_on
FROM sys.databases
WHERE containment <> 0 and is_auto_close_on = 1;

No rows should be returned.

Remediation

Replace <database_name> with each database name found by the Audit Procedure:

ALTER DATABASE <database_name> SET AUTO_CLOSE OFF;

Authentication and Authorization

Ensure CONNECT permissions on the 'guest' user is Revoked within all SQL Server databases excluding the master, msdb and tempdb

Remove the right of the guest user to connect to SQL Server databases, except for master, msdb, and tempdb.

Audit

Replace <database_name> as appropriate

USE <database_name>;
GO
SELECT DB_NAME() AS DatabaseName, 'guest' AS Database_User,
[permission_name], [state_desc]
FROM sys.database_permissions
WHERE [grantee_principal_id] = DATABASE_PRINCIPAL_ID('guest')
AND [state_desc] LIKE 'GRANT%'
AND [permission_name] = 'CONNECT'
AND DB_NAME() NOT IN ('master','tempdb','msdb');

Remediation

Replace <database_name> as appropriate

USE <database_name>;
GO
REVOKE CONNECT FROM guest;

Ensure 'Orphaned Users' are Dropped From SQL Server Databases

A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance and is referred to as orphaned and should be removed.

Audit

Replace <database_name> as appropriate

USE [<database_name>];
GO
EXEC sp_change_users_login @Action='Report';

Remediation

If the orphaned user cannot or should not be matched to an existing or new login using the
Microsoft documented process (https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshootorphaned-users-sql-server), run the following T-SQL query in the
appropriate database to remove an orphan user:

Replace <database_name> as appropriate

USE [<database_name>];
GO
DROP USER <username>;

Ensure the SQL Server’s MSSQL Service Account is Not an Administrator

The service account and/or service SID used by the MSSQLSERVER service for a default instance or MSSQL$<InstanceName> service for a named instance should not be a member of the Windows Administrator group either directly or indirectly (via a group). This also means that the account known as LocalSystem (aka NT AUTHORITY\SYSTEM) should not be used for the MSSQL service as this account has higher privileges than the SQL Server service requires.

The SQL Server Configuration Manager tool should always be used to change the SQL Server’s service account. This will ensure that the account has the necessary privileges. If the service needs access to resources other than the standard Microsoft defined directories and registry, then additional permissions may need to be granted separately to those resources.

Audit

Verify that the service account (in case of a local or AD account) and service SID are not members of the Windows Administrators group.

Remediation

In the case where LocalSystem is used, use SQL Server Configuration Manager to change to a less privileged account. Otherwise, remove the account or service SID from the Administrators group. You may need to run the SQL Server Configuration Manager if underlying permissions had been changed or if SQL Server Configuration Manager was not originally used to set the service account.

Ensure the SQL Server’s SQLAgent Service Account is Not an Administrator

The service account and/or service SID used by the SQLSERVERAGENT service for a default instance or SQLAGENT$<InstanceName> service for a named instance should not be a member of the Windows Administrator group either directly or indirectly (via a group). This also means that the account known as LocalSystem (AKA NT AUTHORITY\SYSTEM) should not be used for the SQLAGENT service as this account has higher privileges than the SQL Server service requires.

The SQL Server Configuration Manager tool should always be used to change the SQL Server’s service account. This will ensure that the account has the necessary privileges. If the service needs access to resources other than the standard Microsoft defined directories and registry, then additional permissions may need to be granted separately to those resources.

Audit

Verify that the service account (in case of a local or AD account) and service SID are not members of the Windows Administrators group.

Remediation

In the case where LocalSystem is used, use SQL Server Configuration Manager to change to a less privileged account. Otherwise, remove the account or service SID from the Administrators group. You may need to run the SQL Server Configuration Manager if underlying permissions had been changed or if SQL Server Configuration Manager was not originally used to set the service account.

Ensure the SQL Server’s Full-Text Service Account is Not an Administrator

The service account and/or service SID used by the MSSQLFDLauncher service for a default instance or MSSQLFDLauncher$<InstanceName> service for a named instance should not be a member of the Windows Administrator group either directly or indirectly (via a group). This also means that the account known as LocalSystem (AKA NT AUTHORITY\SYSTEM) should not be used for the SQLAGENT service as this account has higher privileges than the SQL Server service requires.

The SQL Server Configuration Manager tool should always be used to change the SQL Server’s service account. This will ensure that the account has the necessary privileges. If the service needs access to resources other than the standard Microsoft defined directories and registry, then additional permissions may need to be granted separately to those resources.

Audit

Verify that the service account (in case of a local or AD account) and service SID are not members of the Windows Administrators group.

Remediation

In the case where LocalSystem is used, use SQL Server Configuration Manager to change to a less privileged account. Otherwise, remove the account or service SID from the Administrators group. You may need to run the SQL Server Configuration Manager if underlying permissions had been changed or if SQL Server Configuration Manager was not originally used to set the service account.

Ensure only the default permissions specified by Microsoft are granted to the public server role

public is a special fixed server role containing all logins. Unlike other fixed server roles, permissions can be changed for the public role. In keeping with the principle of least privileges, the public server role should not be used to grant permissions at the server scope as these would be inherited by all users.

Audit

SELECT *
FROM master.sys.server_permissions
WHERE (grantee_principal_id = SUSER_SID(N'public') and state_desc LIKE
'GRANT%')
AND NOT (state_desc = 'GRANT' and [permission_name] = 'VIEW ANY DATABASE' and
class_desc = 'SERVER')
AND NOT (state_desc = 'GRANT' and [permission_name] = 'CONNECT' and
class_desc = 'ENDPOINT' and major_id = 2)
AND NOT (state_desc = 'GRANT' and [permission_name] = 'CONNECT' and
class_desc = 'ENDPOINT' and major_id = 3)
AND NOT (state_desc = 'GRANT' and [permission_name] = 'CONNECT' and
class_desc = 'ENDPOINT' and major_id = 4)
AND NOT (state_desc = 'GRANT' and [permission_name] = 'CONNECT' and
class_desc = 'ENDPOINT' and major_id = 5);

This query should not return any rows.

Remediation

1. Add the extraneous permissions found in the Audit query results to the specific logins to user-defined server roles which require the access.
2. Revoke the <permission_name> from the public role as shown below

USE [master]
GO
REVOKE <permission_name> FROM public;
GO

Ensure Windows BUILTIN groups are not SQL Logins

Prior to SQL Server 2008, the BUILTIN\Administrators group was added as a SQL Server login with sysadmin privileges during installation by default. Best practices promote creating an Active Directory level group containing approved DBA staff accounts and using this controlled AD group as the login with sysadmin privileges. The AD group should be specified during SQL Server installation and the BUILTIN\Administrators group would therefore have no need to be a login.

Before dropping the BUILTIN group logins, ensure that alternative AD Groups or Windows logins have been added with equivalent permissions. Otherwise, the SQL Server instance may become totally inaccessible.

Audit

SELECT pr.[name], pe.[permission_name], pe.[state_desc]
FROM sys.server_principals pr
JOIN sys.server_permissions pe
ON pr.principal_id = pe.grantee_principal_id
WHERE pr.name like 'BUILTIN%';

This query should not return any rows.

Remediation

1. For each BUILTIN login, if needed create a more restrictive AD group containing only the required user accounts.
2. Add the AD group or individual Windows accounts as a SQL Server login and grant it the permissions required.
3. Drop the BUILTIN login using the syntax below after replacing <name> in [BUILTIN\<name>].

USE [master]
GO
DROP LOGIN [BUILTIN\<name>]
GO

Ensure Windows local groups are not SQL Logins

This is a best practice guideline from CIS. Local Windows groups should not be used as logins for SQL Server instances. However, in some environments, it may be necessary to use local groups instead of domain groups. Consider your environment requirements.

Audit

USE [master]
GO
SELECT pr.[name] AS LocalGroupName, pe.[permission_name], pe.[state_desc]
FROM sys.server_principals pr
JOIN sys.server_permissions pe
ON pr.[principal_id] = pe.[grantee_principal_id]
WHERE pr.[type_desc] = 'WINDOWS_GROUP'
AND pr.[name] like CAST(SERVERPROPERTY('MachineName') AS nvarchar) + '%';

This query should not return any rows.

Remediation

1. For each LocalGroupName login, if needed create an equivalent AD group containing only the required user accounts.
2. Add the AD group or individual Windows accounts as a SQL Server login and grant it the permissions required.
3. Drop the LocalGroupName login using the syntax below after replacing <name>.

USE [master]
GO
DROP LOGIN [<name>]
GO

Ensure the public role in the msdb database is not granted access to SQL Agent proxies

The public database role contains every user in the msdb database. SQL Agent proxies define a security context in which a job step can run.

Audit

USE [msdb]
GO
SELECT sp.name AS proxyname
FROM dbo.sysproxylogin spl
JOIN sys.database_principals dp
ON dp.sid = spl.sid
JOIN sysproxies sp
ON sp.proxy_id = spl.proxy_id
WHERE principal_id = USER_ID('public');
GO

This query should not return any rows.

Remediation

1. Ensure the required security principals are explicitly granted access to the proxy (use sp_grant_login_to_proxy)
2. Revoke access to the <proxyname> from the public role.

USE [msdb]
GO
EXEC dbo.sp_revoke_login_from_proxy @name = N'public', @proxy_name = N'<proxyname>';
GO

Password policies

The section deals with the setting of password policies for local SQL database accounts. This is only relevant if the expiration is disabled for accounts. CIS recommends all SQL accounts enforce expiration. Consider your environment requirements before actioning, particularly the recommendation to enforce password changes. If changing Synergetic accounts, please inform Synergetic to ensure continued support. This section assumes that the best practice of creating alternative sysadmin accounts and disabling the sa account has also been implemented.

Ensure 'CHECK_EXPIRATION' Option is set to 'ON' for All SQL Authenticated Logins Within the Sysadmin Role

Applies the same password expiration policy used in Windows to passwords used inside SQL Server.

Audit

SELECT l.[name], 'sysadmin membership' AS 'Access_Method'
FROM sys.sql_logins AS l
WHERE IS_SRVROLEMEMBER('sysadmin',name) = 1
AND l.is_expiration_checked <> 1
UNION ALL
SELECT l.[name], 'CONTROL SERVER' AS 'Access_Method'
FROM sys.sql_logins AS l
JOIN sys.server_permissions AS p
ON l.principal_id = p.grantee_principal_id
WHERE p.type = 'CL' AND p.state IN ('G', 'W')
AND l.is_expiration_checked <> 1;

This query should not return any rows.

Remediation

For each <login_name> found by the Audit Procedure, execute the following T-SQL statement:

ALTER LOGIN [<login_name>] WITH CHECK_EXPIRATION = ON;

Ensure 'CHECK_POLICY' Option is set to 'ON' for All SQL Authenticated Logins

Applies the same password complexity policy used in Windows to passwords used inside SQL Server.

Audit

SELECT name, is_disabled
FROM sys.sql_logins
WHERE is_policy_checked = 0;

The is_policy_checked value of 0 indicates that the CHECK_POLICY option is OFF; value of 1 is ON. If is_disabled value is 1, then the login is disabled and unusable. If no rows are returned then either no SQL Authenticated logins exist or they all have CHECK_POLICY ON.

Remediation

For each <login_name> found by the Audit Procedure, execute the following T-SQL statement:

ALTER LOGIN [<login_name>] WITH CHECK_POLICY = ON;

Ensure 'MUST_CHANGE' Option is set to 'ON' for All SQL Authenticated Logins

Whenever this option is set to ON, SQL Server will prompt for an updated password the first time the new or altered login is used.

Audit

1. Open SQL Server Management Studio.
2. Open Object Explorer and connect to the target instance.
3. Navigate to the Logins tab in Object Explorer and expand. Right click on the desired login and select Properties.
4. Verify the User must change password at next login checkbox is checked.

Remediation

Set the MUST_CHANGE option for SQL Authenticated logins when creating a login initially:

CREATE LOGIN <login_name> WITH PASSWORD = '<password_value>' MUST_CHANGE,
CHECK_EXPIRATION = ON, CHECK_POLICY = ON;

Set the MUST_CHANGE option for SQL Authenticated logins when resetting a password:

ALTER LOGIN <login_name> WITH PASSWORD = '<new_password_value>' MUST_CHANGE;

Auditing and logging

Ensure 'Maximum number of error log files' is set to greater than or equal to '12'

SQL Server error log files must be protected from loss. The log files must be backed up before they are overwritten. Retaining more error logs helps prevent loss from frequent recycling before backups can occur. Be aware that this will also increase the storage space requirements.

Audit

1. Open SQL Server Management Studio.
2. Open Object Explorer and connect to the target instance.
3. Navigate to the Management tab in Object Explorer and expand. Right click on the SQL Server Logs file and select Configure.
4. Verify the Limit the number of error log files before they are recycled checkbox is checked
5. Verify the Maximum number of error log files is greater than or equal to 12

Remediation

Adjust the number of logs to prevent data loss.

Ensure 'Default Trace Enabled' Server Configuration Option is set to '1'

The default trace provides audit logging of database activity including account creations, privilege elevation and execution of DBCC commands.

Audit

SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = 'default trace enabled';

Both value columns must show 1.

Remediation

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'default trace enabled', 1;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;

Ensure 'Login Auditing' is set to 'failed logins'

This setting will record failed authentication attempts for SQL Server logins to the SQL Server Errorlog. This is the default setting for SQL Server.

Capturing failed logins provides key information that can be used to detect\confirm password guessing attacks. Capturing successful login attempts can be used to confirm server access during forensic investigations, but using this audit level setting to also capture successful logins creates excessive noise in the SQL Server Errorlog which can hamper a DBA trying to troubleshoot problems. Elsewhere in this benchmark, we recommend using the newer lightweight SQL Server Audit feature to capture both successful and failed logins.

Audit

EXEC xp_loginconfig 'audit level';

A config_value of failure indicates a server login auditing setting of Failed logins only. If a config_value of all appears, then both failed and successful logins are being logged.

Both settings should also be considered valid, but as mentioned capturing successful logins using this method creates lots of noise in the SQL Server Errorlog.

Remediation

1. Open SQL Server Management Studio.
2. Right click the target instance and select Properties and navigate to the Security tab.
3. Select the option Failed logins only under the Login Auditing section and click OK.
4. Restart the SQL Server instance.

Ensure 'Login Auditing' is set to 'failed logins'

SQL Server Audit is capable of capturing both failed and successful logins and writing them to one of three places: the application event log, the security event log, or the file system.

We will use it to capture any login attempt to SQL Server, as well as any attempts to change audit policy. This will also serve to be a second source to record failed login attempts.

Audit

USE [master];
GO;
SELECT
S.name AS 'Audit Name'
, CASE S.is_state_enabled
WHEN 1 THEN 'Y'
WHEN 0 THEN 'N' END AS 'Audit Enabled'
, S.type_desc AS 'Write Location'
, SA.name AS 'Audit Specification Name'
, CASE SA.is_state_enabled
WHEN 1 THEN 'Y'
WHEN 0 THEN 'N' END AS 'Audit Specification Enabled'
, SAD.audit_action_name
, SAD.audited_result
FROM sys.server_audit_specification_details AS SAD
JOIN sys.server_audit_specifications AS SA
ON SAD.server_specification_id = SA.server_specification_id
JOIN sys.server_audits AS S
ON SA.audit_guid = S.audit_guid
WHERE SAD.audit_action_id IN ('CNAU', 'LGFL', 'LGSD');

The result set should contain 3 rows, one for each of the following audit_action_names:

• AUDIT_CHANGE_GROUP
• FAILED_LOGIN_GROUP
• SUCCESSFUL_LOGIN_GROUP

Both the Audit and Audit specification should be enabled and the audited_result should include both success and failure.

Remediation

CREATE SERVER AUDIT TrackLogins
TO APPLICATION_LOG;
GO
CREATE SERVER AUDIT SPECIFICATION TrackAllLogins
FOR SERVER AUDIT TrackLogins
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);
GO
ALTER SERVER AUDIT TrackLogins
WITH (STATE = ON);
GO

Encryption

Ensure 'Symmetric Key encryption algorithm' is set to 'AES_128' or higher in non-system databases

Per the Microsoft Best Practices, only the SQL Server AES algorithm options, AES_128, AES_192, and AES_256, should be used for a symmetric key encryption algorithm

Audit

Replace <database_name> as required

USE <database_name>
GO
SELECT db_name() AS Database_Name, name AS Key_Name
FROM sys.symmetric_keys
WHERE algorithm_desc NOT IN ('AES_128','AES_192','AES_256')
AND db_id() > 4;
GO

This query should not return any rows.

Remediation

Refer to Microsoft SQL Server Books Online ALTER SYMMETRIC KEY entry: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-symmetric-key-transact-sql

Ensure Asymmetric Key Size is set to 'greater than or equal to 2048' in non-system databases

Microsoft Best Practices recommend to use at least a 2048-bit encryption algorithm for asymmetric keys.

Audit

Replace <database_name> as required

USE <database_name>
GO
SELECT db_name() AS Database_Name, name AS Key_Name
FROM sys.asymmetric_keys
WHERE key_length < 2048
AND db_id() > 4;
GO

This query should not return any rows.

Remediation

Refer to Microsoft SQL Server Books Online ALTER ASYMMETRIC KEY entry: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-asymmetric-key-transact-sql

Share and File level access

Ensure that all shares and file level permission are configured for least priviledge access.  Do not use the Domain Users or Everyone group.

For example,

• The Synergetic windows client share should have only Staff read only share access, and Staff read only file level access
• The backup directory should only be shared with the SQL agent service account at the share and file level 

Additional considerations

Ensure 'SQL Server Browser Service' is configured correctly

In the case of a default instance installation, the SQL Server Browser service is disabled by default. Unless there is a named instance on the same server, there is typically no reason for the SQL Server Browser service to be running.

Audit

Check the SQL Browser service's status via services.msc or similar methods.

Remediation

Enable or disable the service as needed for your environment.

CIS Recommendation exceptions

There are a number of CIS recommendations that should not be implemented on Synergetic environments.  These are: