Maintaining the security of dedicated SQL Server instances and its environment is the most important duty of any DBA. Various problems, including the unwanted external breaches, may arise that could heavily impact the safety of a SQL instance and could potentially harm the company as well.
With security always in mind, a good DBA is working hard to secure SQL instance data and its integrity at all costs.
This article will cover SQL instance security health check rules. The insight will be given into the following rules:
- SQL Server Services must have separate accounts
- SQL Server password policy should be turned on
- SQL Server Trustworthy bit must be turned off
- SQL Server passwords should not be plane
- SQL Server instance permissions should not be granted to Public role
- On all databases disable guest user (except on system and temp)
- Appropriate cryptographic modules have been used to encrypt data
- Native Backup Encryption is set
- Server Authentication Type
- Non-native Encryption is set
- DAC Remote Access
To execute the mentioned set of rules, head over to the Health tab within the ApexSQL Manage and click the Run button. In the Servers tab of the Run health check window check desired SQL instance to run high-level analysis against:
Under the Rules tab, check the above-listed rules from the Security category and click the OK button:
Once the health check analysis is executed, the Result summary window with health check statistics of the SQL instance is presented:
By clicking the Close button, the executed rules are now displayed in the main grid of the Health tab:
Now, let us briefly explain every executed SQL instance health check security rule.
SQL Server Services must have separate accounts
It goes without saying that to a DBA, it is extremely important to ensure that SQL Server instance is tightly secured and not exposed to users that should not have access to the mentioned instance.
It is highly advised to always run SQL instance Services by using the lowest possible rights, i.e., the specific low-privilege user account or domain account instead of a one shared account.
ApexSQL Manage is giving specific advice and best practice to follow in this situation. The recommendation is to use separate accounts for different SQL Server services. Running different services under the same account is considered a security risk:
SQL Server password policy should be turned on
SQL Server password mechanism is a set of complex and expiration policies that applies to a login that uses SQL Server instance authentication.
Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. Once this policy is enforced, new passwords must follow the below guidelines:
- The password must not include the user’s account name
- The password needs to be at least eight characters long
- The password needs to contain characters from the following categories:
- Uppercase Latin letters (A through Z)
- Lowercase Latin letters (a through z)
- 0 through 9 digits
- The characters such as exclamation point (!), dollar sign ($), the number sign (#), percent (%) that are part of the non-alphanumeric category
Additionally, the SQL Server instance password expiration policies are used to manage the lifespan of a password, and when this policy is enforced, users are reminded to change the old passwords. Otherwise, the accounts with expired passwords will be disabled.
To enforce the mentioned policies, the SQL manage instance tool is executing FixSQL script that enables both, SQL Server password and password expiration policies:
SQL Server Trustworthy bit must be turned off
The SQL Server TRUSTWORTHY database property is used to indicate whether or not the SQL Server instance trusts the database and its content. By default, this option is set to OFF, and for a good reason since if this option is enabled, a privileged database user can elevate the permission level to the sysadmin role. This, in turn, leaves the door open for security breaches and unsafe assemblies that can compromise the system.
The best practice for this bit is to ensure the SQL Server TRUSTWORTHY property is disabled, which the SQL manage instance software is ensuring by running the appropriate FixSQL script:
SQL Server passwords should not be plane
When it comes to the SQL Server authentication type, any SQL Server instance user, besides providing a valid login account with the appropriate permissions and privileges, must also provide a good and strong password for the mentioned account.
To some, this task may be a bit tedious, but in the long run, its proven to be a wise decision, since SQL Servers are prone to attack and an extent a bit vulnerable.
This health check rule is advising a new SQL instance password should follow next guideline:
- The password is not blank
- The password does not equal to the account name of the user
- The password does not equal to some of the most common passwords (password, sa, dev, test, admin)
SQL Server permissions should not be granted to Public role
Having into consideration that every SQL Server instance login belongs to the public server role and that the user inherits the permissions granted to the public on an object, the best recommendation here would be to assign public permissions ONLY when there is a valid need for that object to be available to all users.
The public server role is granted with VIEW ANY DATABASE permission and the CONNECT permission on the default endpoints, and only these default permissions should be granted to the public role:
Disable guest user on all databases (except on system and temp)
Each SQL Server instance database includes a guest user and permissions granted to this user are inherited by users that have access to the database but don’t have a user account in this SQL database.
This special user, a guest, exists to permit access to a database for logins that are not mapped to a specific database user. Because any login can use the database through this user, it is suggested that the guest user should not be enabled.
The best strategy here would be to revoke the guest user permission to access the database if it is not required:
Appropriate cryptographic modules have been used to encrypt data
This health check rule allows users to check if databases are secured with appropriate cryptographic modules.
Strong encryption modules and algorithms employed to encrypt the SQL Server data are ensuring strong cryptography and protection of SQL Server instances. It is not advised to use weak or untested encryption algorithms since this undermines the very purpose of its existence.
That’s why it is crucial that a DBA or a system administrator needs to ensure that SQL Server must implement necessary cryptographic protections by using the appropriate cryptographic modules.
Since no single algorithm is ideal for all situations, the proper guidance in acquiring those algorithms is highly recommended:
Native Backup Encryption is set
Encrypting SQL Server instance backups is an essential database protection practice since it provides personal control over personal information. The way encryption works is that it translates the file’s data into a secret language, the language not available to those without the right kind of permissions.
It is a level of protection that ensures the strong security of SQL Server data.
This SQL instance health check rule allows users to check if databases are using native encryption and, in turn, are secured with appropriate cryptographic modules. If there are more database backups, all will be checked for encryption:
Server Authentication Type
SQL Server instance authentication supports two authentication modes:
- Windows authentication mode – set by default, providing integrated security since this security model is tightly integrated with Windows. This allows specific Windows users to be trusted when asking for permission to log in to SQL instance
- Mixed authentication mode – this model supports both Windows and SQL Server authentication modes. It goes without saying that when using SQL Server authentication proper username and password need to be provided
It is highly recommended to use Windows authentication whenever it is possible because it provides the advanced layer of security by using the encrypted messages to authenticate users in SQL instance. This is opposed to the SQL logins since login names and encrypted passwords are passed across the network, which makes them less secure.
This health check rule allows users to check whether the SQL Server instance authentication type set to Windows or Mixed mode:
Non-native Encryption is set
This health check rule is directly related to the Native Backup Encryption rule. To be more precise, it allows users to check if some 3rd party tools were used for backup and encryption.
Using 3rd party tools for backup encryption, among many other benefits, can give more control over the level of compression and encryption but are not free and in order to use them one must buy specific licenses:
DAC Remote Access
SQL Server provides a special diagnostic connection for administrators when standard connections to the server are not possible. This diagnostic connection allows an administrator to access the SQL Server instance to execute diagnostic queries and troubleshoot problems even when the instance is not responding to standard connection requests. For more information on Dedicated Admin Connection (DAC), please visit the SQL Server Dedicated Admin Connection (DAC) – how to enable, connect and use article.
This health check rule allows user to determine whether the Dedicated Administrator Connection (DAC) is available remotely on a specific server:
Conclusion
Executing security health check rules on a SQL Server instance can provide some advanced layer of protection. This way, SQL instances are less vulnerable since this additional security is lowering the risk of external attacks.
August 20, 2020
