Applies to
Summary
This article gives a brief description of the specific set of SQL Server security rules.
Description
Having its SQL Server instance environment safe and secure is the top task in every DBAs “to do” list. Even though it is the most important task, it’s not that easy to secure any SQL Server since this chore can be viewed as a series of steps, the steps that are required to be repetitive, and often on a daily basis.
It is always welcomed to have a solution that is allowing us to, in few simple and quick steps, check the current state of a SQL Server instance security and act immediately if any breaches or attacks took place.
Security Health check rules
By executing the following set of rules, the SQL Server is examined in search of any potential security issues, and a comprehensive report is displayed in the main grid of the Health tab.
Following high-level analysis rules will test the security of SQL Server instance(s):
- SQL Server files not on NTFS drives
- Dynamic data masking
- Operating system up to date
- Database Master Keys Encrypted by Password
- SQL browser service not enabled
- Symmetric keys encrypted by certificate
- Symmetric keys not created on system databases
- CLR should be disabled
- Common Criteria Compliance
- Max number of concurrent sessions
- Allow Updates to System Tables is disabled
- Default Trace enabled
- Error log files maximum number
- Asymmetric Key Size
- Full-Text search should not be installed
- Hide SQL Instance
- Replication component installed
- Transparent data encryption enabled for databases
- The server does not contain orphaned users
-
To execute the listed rules, please visit the Executing a health check analysis of the SQL Server instance Security category article
In continuation, we’ll give a brief explanation of the mentioned rules.
SQL Server files not on NTFS drives
This health check rule investigates if SQL Server binaries are located on NTFS drives.
Furthermore, if the rule fails, the appropriate message is displayed stating that listed files are not located on the partition with the NTFS file format.
The best practice here is to use the NTFS file system:
-
Refer to the following Database Files and Filegroups article for more information
Dynamic data masking
This rule checks if the databases on the selected SQL Server instance do not have masked columns and shows databases without dynamic data masking applied.
The dynamic data masking option is an advanced security layer that helps prevent unauthorized access to sensitive data by enabling customers to specify how much sensitive data to reveal with minimal impact on the application. Dynamic data masking can be configured on designated database fields to hide sensitive data in the result sets of queries.
The best practice here is to ensure dynamic data masking limits sensitive data exposure by masking it to non-privileged users:
-
For more information on the topic, please visit the Dynamic Data Masking article
Operating system up to date
This high-level analysis rule checks if the operating system is up to date.
It is not recommended to keep the operating system without the latest security patches, since malicious users can exploit known security weaknesses. The best practice here is to always keep the operating system up to date:
Database Master Keys Encrypted by Password
This SQL Server instance rule investigates if the database master key is encrypted by password.
If the rule fails, then the appropriate message is shown stating that listed databases do not have master key encrypted by password.
It is highly recommended that passwords used to encrypt the master key in the database must meet the Windows password policy requirements of the computer that is running the SQL Server instance:
-
More information on this topic can be found in the CREATE MASTER KEY (Transact-SQL) article
SQL browser service not enabled
This health check rule determines if the SQL Server browser service is currently enabled and running.
The SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. The best practice here would be to enable the SQL Server Browser service to avoid communication errors:
-
Refer to the following SQL Server Browser Service article for more information
Symmetric keys encrypted by certificate
This high-level analysis rule checks if symmetric keys are not encrypted with a certificate.
Determining the exact way of encrypting data provides the possibility of increasing data security on the SQL Server.
If the rule fails, the proper message is shown listing keys that are not encrypted by a certificate.
It is highly recommended to determine the exact way of encrypting data since this provides the possibility of increasing data security on the SQL Server instance. The best practice in this situation is to encrypt symmetric keys with certificate:
-
Refer to the following Create Identical Symmetric Keys on Two Servers article for more information
Symmetric keys not created on system databases
This rule checks if symmetric keys used for encryption exist on system databases.
Determining whether there is a system key used for encryption in the system databases provides an opportunity to increase data security on the SQL Server.
If this health check rule fails, the proper message is shown listing databases with user-created symmetric keys.
Determining whether there is a system key used for encryption in the system databases provides an opportunity to increase data security on the SQL Server instance. The best practice is to avoid creating symmetric keys in the system databases:
-
Refer to the following Symmetric Keys on System Databases article for more information
CLR should be disabled
This rule checks if CLR, The Common Language Runtime, is disabled on the machine running the SQL manage instance tool.
If the rule fails, then an appropriate fix SQL script is available that can be used to disable the CLR.
The best practice here is to disable CLR on production environments:
Common Criteria Compliance
This rule checks if the Common Criteria Compliance is enabled.
The Common Criteria Compliance is an internationally recognized set of guidelines for security for information technology products. This set of guidelines applies to the operating systems, Databases, Network Devices, Smart cards… Products that are certified by the Common criteria have been rigorously evaluated by accredited third party security labs.
The best practice is to enable Common Criteria Compliance:
-
Refer to the following Common Criteria Compliance Enabled Server Configuration article for more information
Max number of concurrent sessions
This rule checks if the maximum number of concurrent sessions is different from the default value on the SQL Server instance.
The number of user connections that are allowed on A SQL Server instance depends on the version of SQL Server that is being used, and the limits of the application or applications and hardware. The maximum of 32,767 user connections is allowed by SQL Server.
The best practice is to leave the default value for the max number of concurrent sessions. If the default value conflicts with environment policies, make sure to carefully change this value as there may be problems connecting to SQL Server:
-
Refer to the following Configure the user connections Server Configuration Option article for more information
Allow Updates to System Tables is disabled
Data loss scenarios can occur if system tables can be updated on a SQL Server instance. In worst-case scenarios, stored procedures created while this option was enabled can update system tables and cause a total shut down of the SQL instance even after updates to system tables option is disabled. The best practice on production systems is to disable this option.
This rule checks if Allow Updates to System tables is disabled:
Default Trace enabled
This high-level analysis rule checks if the default trace option is enabled.
Use the default trace enabled option to enable or disable the default trace log files. The default trace functionality provides a rich, persistent log of activity and changes primarily related to the configuration options.
It is highly recommended to enable Default Trace:
-
Refer to the following Default trace enabled Server Configuration Option article for more information
Error log files maximum number
This rule checks if the error log files number is set to less than 11.
A large number of error log files can cause performance issues on the SQL Server instance. The main reason for the performance hit is because the SQL instance takes a lot of time to read the numerous files. The default value is 6 files. The best practice here would be to increase the number of log files based on the official Microsoft advice, which is between 6 and 11.
The rule will be declared a pass if the maximum number of log files is set to less than 11:
-
Refer to the following SCM Services – Configure SQL Server Error Logs article for more information
Asymmetric Key Size
This rule checks if the asymmetric key is below 2048 bits.
Best practice in this situation is to use asymmetric keys with the length above 2048 bits in order to prevent malicious attempts where a potential attacker can duplicate less difficult keys. This rule checks if the asymmetric key is below 2048 bits:
Full-Text search should not be installed
SQL Server is vulnerable if Full-Text Search is installed and running.
This rule checks if the Full-Text search is installed. When this health check rule fails, the message: “Full-Text search should not be installed” Is displayed.
Full-text search can induce heavy performance issue when it is used to insert large datasets in a table:
Hide SQL Instance
This rule checks if SQL Server instances are hidden from the SQL Server Browser Service.
The best practice for production SQL Server instances is to hide them from the SQL Server Browser Service. When an instance is hidden, it is not displayed when browsing for SQL instances; thus, a potential attacker will not be able to easily detect production servers. This rule checks if SQL Server instances are hidden from the SQL Server Browser Service:
Replication component installed
This rule checks if the replication component is installed.
Replication is a set of technologies widely used by DBAs to further secure their environments and a good disaster recovery measure. The main purpose is to copy and distribute data and database objects from one database to another. By synchronizing the data between databases, consistency is maintained, and an exact replica is always available. This rule checks if the replication component is installed. If the rule fails, the Message box will show which SQL Server instances do not have a replication component.
-
Refer to the following SQL Server Replication article for more information
Transparent data encryption enabled for databases
This rule checks if transparent data encryption is enabled.
Transparent data encryption is used to secure database data in case a malicious party is able to obtain the physical media drives or backup files. The best practice is to encrypt sensitive data in a database and use a certificate to protect the keys that encrypt the data. This will prevent anyone without the keys from using the data. This rule checks if transparent data encryption is enabled. If the rule fails, the Message box will show which databases have no encryption:
The server does not contain orphaned users
When a database is created, specific logins are assigned to it. Attaching or restoring the database to a different location will result in a scenario where previously created and configured logins in that database do not provide the required access. The best practice is to remove the previous users and logins to avoid security permission issues.
This rule will show a list of current orphan users and a FixSQL to remove them:
Conclusion
The above were the SQL Server instance health check rules that are aiming to examine weaknesses and possible breaches in one’s production environment security. Follow its recommendations and best practices to ensure your production environment is safe.
