What is a SQL CIS Benchmark?
A SQL CIS Benchmark refers to a set of best practices and guidelines developed by the Center for Internet Security (CIS) for securing and configuring relational database management systems (RDBMS) that use SQL (Structured Query Language).
When an organization follows a SQL CIS Benchmark for SQL hardening, it helps ensure that the SQL database is configured in a way that aligns with recognized security best practices.
What is a SQL Server Benchmark?
A “SQL Benchmark” and a “SQL Server Benchmark” are related concepts, but they are not the same. The difference lies in the specific context and focus of the benchmarking process. SQL benchmarks can help compare the relative performance of different SQL servers or configurations. Let's explain:
SQL Benchmark: A SQL benchmark typically refers to the process of evaluating the performance of SQL queries and database systems in general, regardless of the specific database management system (DBMS) being used. It’s a broad term that assesses the performance and efficiency of SQL queries, data retrieval, and database management.
SQL Server Benchmark: A SQL Server benchmark is more specific. It focuses on benchmarking a Microsoft SQL Server, which is a particular relational database management system (RDBMS) developed by Microsoft. A SQL Server benchmark assesses the performance of SQL Server instances, databases, and queries.
What are the SQL CIS Benchmarks?
A CIS benchmark is a set of guidelines and best practices developed by the Center for Internet Security (CIS) of prescriptive configuration recommendations to help organizations secure their computer systems and networks.
The SQL CIS Benchmark document provides explicit recommendations for establishing a secure configuration posture for Microsoft SQL Servers. The most recent benchmark as of this article is the CIS Microsoft SQL Server 2022 Benchmark v1.0.0. CIS Microsoft SQL Server 2019, 2017, 2016, 2014, 2012 and 2008 are among the SQL Server benchmark documentation available by CIS.
CIS SQL Server Benchmark settings and 'Rule Name' serve as the cornerstone to resilience of SQL Server environments. The CIS SQL Benchmark is rooted in industry expertise and security standards. Lets delve into key recommendations that are fundamental to fortifying SQL Server environments, ensuring resilience against potential security threats and vulnerabilities using the SQL CIS Benchmark.
The following are from the CIS SQL Server Benchmark settings and the remediation Procedure with systematic instructions for applying recommendations to the target system to bring it into compliance according to the recommendation:
Rule Name: Ensure the ‘sa’ Login Account is set to ‘Disabled’ (Automated)
Description: 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.
Remediation: Execute the following T-SQL query
| USE [master]
GO DECLARE @tsql nvarchar(max) SET @tsql = ‘ALTER LOGIN ‘ + SUSER_NAME(0x01) + ‘ DISABLE’ EXEC (@tsql) GO |
Rule Name: Ensure the ‘sa’ Login Account has been renamed (Automated)
Description: The sa account is a widely known and often widely used SQL Server login with sysadmin privileges. The sa login is the original login created during installation and always has principal_id=1 and sid=0x01.
Remediation: Replace the <different_user> value within the below syntax and execute to rename the sa login.
| ALTER LOGIN sa WITH NAME = <different_user>; |
Rule Name: Ensure ‘Cross DB Ownership Chaining’ Server Configuration Option is set to ‘0’ (Automated)
Description: The cross db ownership chaining option controls cross-database ownership chaining across all databases at the instance (or server) level.
Remediation: For AWS RDS Instances, please refer to the documentation for using Parameter
Groups here: Working with parameter groups
Run the following T-SQL command:
| EXECUTE sp_configure ‘cross db ownership chaining’, 0;
RECONFIGURE; GO |
Rule Name: Ensure ‘Database Mail XPs’ Server Configuration Option is set to ‘0’ (Automated)
Description: The Database Mail XPs option controls the ability to generate and transmit email messages from SQL Server.
Remediation: Run the following T-SQL command:
| EXECUTE sp_configure ‘show advanced options’, 1;
RECONFIGURE; EXECUTE sp_configure ‘Database Mail XPs’, 0; RECONFIGURE; GO EXECUTE sp_configure ‘show advanced options’, 0; RECONFIGURE; |
What are SQL Server Hardening Best Practices?
Hardening a SQL Server security involves implementing security measures to protect the server from potential threats and vulnerabilities. By adopting SQL hardening best practices, organizations can establish a secure database environment, actively monitor for suspicious activities, and mitigate risks associated with potential security threats and vulnerabilities.
Here are some SQL hardening best practices for security measures:
Column-level protection
Organizations frequently find the necessity to secure data at the column level, particularly when dealing with customer details, employee information, proprietary knowledge, product data, healthcare records, financial data, and other forms of sensitive information stored within SQL Server databases. Columns housing sensitive data often encompass elements such as identification/social security numbers, mobile phone numbers, first and last names, financial account details, and any other information that might be classified as personally identifiable information (PII).
Row-level protection
Row-Level Security (RLS) empowers the utilization of user execution context to manage access to rows within a database table. RLS guarantees that users can exclusively view records relevant to their permissions, providing a form of ‘record-level’ security for your application without necessitating substantial modifications to the application itself.
File encryption
Transparent Data Encryption (TDE) safeguards data at the file level by implementing encryption-at-rest for database files. It ensures that database files, backup files, and tempdb files remain inaccessible and unreadable without the proper certificates to decrypt the database files. The absence of TDE could potentially expose data to unauthorized access if physical media, such as drives or backup tapes, is compromised. TDE seamlessly integrates with all other security features in SQL Server, providing robust protection. Through real-time I/O encryption and decryption of data and log files, TDE utilizes a database encryption key (DEK) stored in the user database. This encryption key can further be secured using a certificate, itself protected by the database master key of the master database. Employ TDE as a comprehensive solution to protect data at rest, backups, and tempdb.
Auditing and reporting
To conduct an audit of the SQL Server, establish an audit policy at either the server or database level. Server-level policies extend their application to all databases on the server, both existing and newly created ones. For ease of management and login auditing, activate server-level auditing and permit database-level auditing to inherit the server-level properties across all databases.
Identities and authentication
SQL Server offers two authentication modes: Windows Authentication mode and ‘SQL Server and Windows Authentication mode’ (mixed mode).
It’s important to note that logins operate independently from database service accounts. Initially, associate logins or Windows groups with database users or roles individually. Subsequently, allocate permissions to users, server roles, and/or database roles, enabling access to database objects.
Enhancing the security of your identities and authentication methods can be achieved through the following recommendations and best practices:
Implement least-privilege role-based security strategies to optimize security management.
-
-
- Adopt the standard practice of assigning Active Directory users to AD groups, ensuring the existence of AD groups within SQL Server roles, and granting SQL Server roles the minimal permissions necessary for application functionality.
-
Opt for Active Directory instead of SQL Server authentication whenever feasible, particularly favoring Active Directory over storing security at the application or database level.
-
-
- Disabling the account becomes a straightforward process if a user departs from the company.
- Remove users from groups is easily achievable when users undergo role changes or exit the organization, with group security recognized as a best practice.
-
Utilize Multi-Factor Authentication for accounts with machine-level access, including those employing Remote Desktop Protocol (RDP) for logging into the machine.
Mandate robust and intricate passwords that are resistant to easy guessing and exclusive to the specific account. Regularly update passwords and adhere to Active Directory policies.
Restricted access and permissions assigned to the Active Directory account of the Database Administrator (DBA)
Consider excluding DBA accounts from the sysadmin role, opting to assign CONTROL SERVER permissions to DBA accounts instead of incorporating them as sysadmin role members.
Data lineage and data integrity
Maintaining historical records of data alterations over time proves advantageous in addressing inadvertent modifications to the data. It also serves a valuable role in auditing application changes, offering the capability to recover data elements when unauthorized modifications are introduced by malicious actors.
Security assessment tools and evaluation
Configuration and monitoring tools offer the capability to manage surface-area security, recognize potential data security enhancements, and conduct a best practice assessment of your SQL Server environment’s security at the instance level.
