| - Dedicated server to specific application, no others, reduces attack vector.
- Install only the required roles for the OS and Tools for the SQL Server and OS.
Surface Area Reduction
Set the 'Ad Hoc Distributed Queries' Server Configuration Option to 0
Ad Hoc Distributed Queries Allow users to query data and execute statements on external data sources. This functionality should be disabled because it can be used to remotely access and exploit vulnerabilities on remote SQL Server instances and to run unsafe Visual Basic for Application functions.
Run the following to verify that both columns are zero.
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';
Set the 'CLR Enabled' Server Configuration Option to 0
The CLR enabled option specifies whether user assemblies can be run by SQL Server, enabling use of CLR assemblies widens the attack surface of SQL Server and puts it at risk from both inadvertent and malicious assemblies.
Run the following to verify that both columns are zero.
SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'clr enabled';
Set the 'Cross DB Ownership Chaining' Server Configuration Option to 0
This option allows controlling cross-database ownership chaining across all databases at the instance (or server) level. When enabled, this option allows a member of the db_owner role in a database to gain access to objects owned by a login in any other database, causing an unnecessary information disclosure. When required, cross-database ownership chaining should only be enabled for the specific databases requiring it instead of at the instance level for all databases by using the ALTER DATABASE <dbname> SET DB_CHAINING ON command. This database option may not be changed on the master, model, or tempdb system databases.
Run the following to verify that both columns are zero.
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';
Set the 'Database Mail XPs' Server Configuration Option to 0.
This option controls the generation and transmission of email messages from SQL Server and disabling Database Mail reduces the SQL Server surface, eliminates a DOS attack vector and channel to exfiltrate data from the database server to a remote host.
Run the following to verify that both columns are zero.
SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'Database Mail XPs';
Set the 'Ole Automation Procedures' Server Configuration Option to 0.
Extended stored procedures that allow SQL Server users to execute functions external to SQL Server and enabling this option will increase the attack surface of SQL Server and allow users to execute functions in the security context of SQL Server.
Run the following to verify that both columns are zero.
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';
Set the 'Remote Access' Server Configuration Option to 0.
Enables the execution of local stored procedures on remote servers or remote stored procedures on local server, this enables functionality that can be abused to launch a Denial-of-Service (DoS) attack on remote servers by off-loading query processing to a target.
Run the following to verify that both columns are zero.
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';
This option is not disabled, run the following to do so.
EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE; EXECUTE sp_configure 'Remote access', 0; RECONFIGURE; GO EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;
Set the 'Remote Admin Connections' Server Configuration Option to 0.
This setting controls whether a client application on a remote computer can use the Dedicated Administrator Connection (DAC). The Dedicated Administrator Connection (DAC) lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection. In a cluster scenario the administrator may not actually be logged on to the same node that is currently hosting the SQL Server instance and thus is considered "remote". Therefore this setting should usually be enabled (1) for SQL Server failover clusters; otherwise it should be disabled (0) which is the default.
Run the following to verify that both columns are zero.
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;
Set the 'Scan For Startup Procs' Server Configuration Option to 0.
This option causes SQL Server to scan for and automatically run all stored procedures that are set to execute upon service startup. Enforcing this control reduces the threat of an entity leveraging these facilities for malicious purposes.
Run the following to verify that both columns are zero.
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';
Set the 'Trustworthy' Database Property to Off.
The TRUSTWORTHY option allows database objects to access objects in other database under certain circumstances. Provides protection from malicious CLR assemblies or extended procedures.
Run the following to verify that both columns are zero.
SELECT name FROM sys.databases WHERE is_trustworthy_on = 1 AND name != 'msdb' AND state = 0;
 (This Audit resulted in no return value?)
Disable Unnecessary SQL Server Protocols.
SQL Server supports Shared Memory, Named Pipes, TCP/IP and VIA protocols. However, SQL Server should be configured to use the bare minimum required based on the organization's needs. Using fewer protocols minimizes the attack surface of SQL Server and in some cases can protect it from remote attacks. In SQL Server Configuration Manager we only use TCP/IP, Disable all other protocols within the SQL Native Client Configuration (32bit), SQL Server Network, and SQL Native Client Configuration.
Configure SQL Server to use non-standard ports. (Not Required)
- If enabled, the default SQL Server instance will be assigned a default port of TCP:1433 for TCP/IP communication. Administrators can also configure named instances to use TCP:1433 for communication. TCP:1433 is a widely known SQL Server port and this port assignment should be changed. Using a non-default port helps protect the database from attacks directed to the default port. In DSM this is not practiced since doing so will for DAC to listen on random ports and require special configurations opening an undesired number of additional ports.
Set the 'Hide Instance' option to 'Yes' for Production SQL Server instances
Non-clustered SQL Server instances within production environments should be designated as hidden to prevent advertisement by the SQL Server Browser service. Designating production SQL Server instances as hidden leads to a more secure installation because they cannot be enumerated. However, clustered instances may break if this option is selected.
In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance>, and then select Properties. 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.
Disable the 'SA' Login Account
The SA account is a widely known and often widely used SQL Server account with sysadmin privileges. Enforcing this control reduces the probability of an attacker executing brute force attacks against a well-known principal.
- For the DSM servers, the SA account is the only account which will connect to the Application settings as of DSM 9.x.
Rename the 'SA' Login Account.
The SA account is a widely known and often widely used SQL Server account with sysadmin privileges. It is more difficult to launch password-guessing and brute-force attacks against the SA account if the username is not known. It is not a good security practice to code applications or scripts to use the sa account. However, if this has been done renaming the SA account will prevent scripts and applications for authenticating to the database server and executing required tasks or functions.
- This was not done for the DSM instances due to DSM Engineering recommendations.
Set the 'xp_cmdshell' Server Configuration Option to 0
The xp_cmdshell procedure allows an authenticated SQL Server user to execute operating-system command shell commands and return results as rows within the SQL client. The xp_cmdshell is commonly used by attackers to read or write data to/from the underlying Operating System of a database server.
Run the following to verify that the run_value columns are zero.
- sp_configure 'show advanced options',1; RECONFIGURE WITH OVERRIDE; EXECUTE sp_configure 'xp_cmdshell';
Set AUTO_CLOSE 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. Opening contained databases to authenticate a user consumes additional server resources and may contribute to a denial of service.
Run the following to verify that the is_auto_close_on value is zero.
- SELECT name, containment, containment_desc, is_auto_close_on FROM sys.databases WHERE containment <> 0 and is_auto_close_on = 1;
Authentication and Authorization
Set The 'Server Authentication' Property To Windows Authentication mode.
Uses Windows Authentication to validate attempted connections. Windows provides a more robust authentication mechanism than SQL Server authentication.
Use Windows Authentication Mode when setup. To make changes:
- Open SQL Server Management Studio.
- Open the Object Explorer tab and connect to the target database instance.
- Right click the instance name and select Properties.
- Select the Security page from the left menu.
- Set the Server authentication setting to Windows Authentication mode.
- For DSM the SQL DB uses Windows Mixes mode for the SA account connection from the application, all services are using dedicated domain accounts
Revoke CONNECT permissions on the 'guest user' within all SQL Server databases excluding the master and tempdb
Removes the right of guest users to connect to SQL Server user databases. A login assumes the identity of the guest user when a login has access to SQL Server but does not have access to a database through its own account and the database has a guest user account. Revoking the connect permission for the guest user will ensure that a login is not able to access database information without explicit access to do so.
Execute the following for each database to determine if the guest user has CONNECT permission.
USE [ database_name ]; GO SELECT DB_NAME() AS DBName, dpr.name, dpe.permission_name FROM sys.database_permissions dpe JOIN sys.database_principals dpr ON dpe.grantee_principal_id=dpr.principal_id WHERE dpr.name='guest' AND dpe.permission_name='CONNECT'; If the name column contains guest and the permission_name is CONNECT, then run the following to revoke the permission.
-
- The guest user can not be disabled in master or tempdb.
Disable the CONNECT permission using the following:
USE [database_name ]; GO REVOKE CONNECT FROM guest;
Drop Orphaned Users 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. Orphan users should be removed to avoid potential misuse of those broken users in any way.
Run the following to identity orphaned accounts. Run this audit on a regular schedule by creating the script and scheduling it for an e-mail operator.
- EXEC sp_change_users_login @Action='Report';
-
If there are no results then there are no orphaned accounts. If there are orphaned accounts run the following
- DROP USER <username>;
Do not use SQL Authentication in contained databases.
Contained databases do not enforce password complexity rules. The absence of an enforced password policy may increase the likelihood of a weak credential being established in a contained database.
Run the following to identity database users that are using SQL authentication.
- SELECT name AS DBUser FROM sys.database_principals WHERE name NOT IN ('dbo','Information_Schema','sys','guest') AND type IN ('U','S','G') AND authentication_type = 2; GO
Leveraging ONLY Windows Authentication will ensure complexity rules are maintained for the Domain level requirements.
Password Policies
Set the 'CHECK_EXPIRATION' Option 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. Ensuring SQL logins comply with the secure password policy applied by the Windows Server Benchmark will ensure the passwords for SQL logins with Sysadmin privileges are changed on a frequent basis to help prevent compromise via a brute force attack.
Run the following to identity logins that do not have an authentication expiration. (Remove dsmdbtstlocal from Test)
SELECT SQLLoginName = sp.name FROM sys.server_principals sp JOIN sys.sql_logins AS sl ON sl.principal_id = sp.principal_id WHERE sp.type_desc = 'SQL_LOGIN' AND sp.name in (SELECT name AS IsSysAdmin FROM sys.server_principals p WHERE IS_SRVROLEMEMBER('sysadmin',name) = 1) AND sl.is_expiration_checked <> 1; You can change this behavior by running the following.
- ALTER LOGIN [login_name] WITH CHECK_EXPIRATION = ON;
Set the 'CHECK_POLICY' Option to ON for All SQL Authenticated Logins.
Applies the same password complexity policy used in Windows to passwords used inside SQL Server. Ensuring SQL logins comply with the secure password policy applied by the Windows Server Benchmark will ensure SQL logins are not blank and cannot be easily compromised via brute force attack.
Run the following to identity SQL logins and if their password complexity is enforced. A value of zero for PasswordPolicyEnforced indicates that the Check_Policy option is OFF.
- SELECT SQLLoginName = sp.name, PasswordPolicyEnforced = CAST(sl.is_policy_checked AS BIT) FROM sys.server_principals sp JOIN sys.sql_logins AS sl ON sl.principal_id = sp.principal_id WHERE sp.type_desc = 'SQL_LOGIN';
Auditing and Logging
Set the 'Maximum number of error log files' setting to greater than or equal to 12
SQL Server error log files must be backed up before they are overwritten and protected from loss.The SQL Server error log contains important information about major server events and login attempt information as well.
Open SQL Server Management Studio.
In Object Explorer expand Management > Right click SQL Server Logs > Select Configure.
- Verify the Limit the number of error log files before they are recycled checkbox is checked and set to greater than or equal to 12.
Set the 'Default Trace Enabled' Server Configuration Option to 1.
The default trace provides audit logging of database activity including account creations, privilege elevation and execution of DBCC commands. Default trace provides valuable audit information regarding security-related activities on the server.
Run the following to verify that both columns are zero.
- 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';
Set 'Login Auditing' to Both failed and successful logins.
Setting logs both successful and failed login SQL Server authentication attempts provides key information that can be used to detect\confirm password guessing attacks and used to confirm server access during forensic investigations.
Run the following to verify that the config_value is "all".
XP_loginconfig 'audit level';
- To change the value Open SQL Server Management Studio.
- Right click the Properties > Security > Select the option "Both failed and successful logins".
- Restart the SQL Server instance.
- Application Development
Set the 'CLR Assembly Permission Set' to SAFE_ACCESS for All CLR Assemblies.
Setting CLR Assembly Permission Sets to SAFE_ACCESS will prevent assemblies from accessing external system resources such as files, the network, environment variables, or the registry. Assemblies with EXTERNAL_ACCESS or UNSAFE permission sets can be used to access sensitive areas of the operating system, steal and/or transmit data and alter the state and other protection measures of the underlying Windows Operating System.
Run the following to verify that the permissions_set_desc columns is SAFE_ACCESS.
SELECT name, permission_set_desc FROM sys.assemblies where is_user_defined = 1;
Run the following to enforce this.
- ALTER ASSEMBLY assembly_name WITH PERMISSION_SET = SAFE;
RESOURCE
CIS_Microsoft_SQL_Server_2014_Benchmark_v1.0.0.pdf |
