Recent Searches

No recent searches

    Popular Articles

      Articles
      View all
        Topics
        View all
          Tickets
          View all
            no results

            Sorry! nothing found for

            SQL server hardening guide

            Modified on Mon, 27 Apr at 2:16 AM

            TABLE OF CONTENTS



            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.

            Error rendering macro 'toc' : [com.ctc.wstx.exc.WstxLazyException] com.ctc.wstx.exc.WstxUnexpectedCharException: Unexpected character ';' (code 59) expected '=' at [row,col {unknown-source}]: [1,43149]


            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:


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

            CISDescriptionReason
            2.2Ensure 'CLR Enabled' Server Configuration Option is set to '0'Required for underlying logic
            2.9Ensure 'Trustworthy' Database Property is set to 'Off' Required for CLR access
            2.11Ensure SQL Server is configured to use non-standard ports

            Not supported for default instances . May have issue with changing port on default instance as Synergetic config does not allow supplying of port number in the configuration file. However, this would works okay for named instances using the SQL Browser Service but then CIS 2.12 could not be performed to 'hide' the instance. 

            2.14Ensure the 'sa' Login Account has been renamedSynergetic has dependencies on DB owner matching the user that created the CLRs, which is normally ‘sa’ and set the DB owner to dbo (which is linked to sa).
            2.17Ensure no login exists with the name 'sa'As above, ‘sa’ user is required but can be disabled
            3.1Ensure 'Server Authentication' Property is set to 'Windows 
            Authentication Mode'            		Synergetic requires mixed mode - normal staff and admin user accounts can all use Windows Auth but the application has internal SQL user accounts (zSynergetic_*) managed by the patch process and used for each application
            3.4Ensure SQL Authentication is not used in contained databasesAs above, Synergetic uses contained users for the zSynergetic* application user accounts
            6.2Ensure 'CLR Assembly Permission Set' is set to 'SAFE_ACCESS' 
            for All CLR Assemblies

            Current Synergetic CLR settings are defined as follows: 

            System.Drawing UNSAFE_ACCESS 

            SynStreamCrypt SAFE_ACCESS 

            Synergetic.Database.CLR UNSAFE_ACCESS 

            GroupConcat SAFE_ACCESS 

            SqlRegEx SAFE_ACCESS 

            Synergetic.Database.CLR.XmlSerializers EXTERNAL_ACCESS 

            Was this article helpful?

            That’s Great!

            Thank you for your feedback

            Sorry! We couldn't be helpful

            Thank you for your feedback

            Let us know how can we improve this article!

            Select at least one of the reasons
            CAPTCHA verification is required.

            Feedback sent

            We appreciate your effort and will try to fix the article

            Preview
            0 of 0