SQL Vulnerability Assessment

While we're thinking about a sound security strategy for SQL Server, it is important to address current security issues that exist within your database estate. Where should you start? What technical work is required to address the issues found? SQL Vulnerability Assessment is the tool for this task. It will allow you to improve your internal processes and harden your security across a dynamic and ever-changing database environment.

Note

Vulnerability Assessment is supported for SQL Server 2012 and later and requires SSMS 17.4+.

This feature carries out a scan against the database(s) using a pre-built knowledge base of rules that will flag security concerns such as elevated accounts and security misconfigurations. To start this assessment, you will need to right-click on the database and click on Vulnerability Assessment (as shown in the following screenshot) and start a scan:

Figure 2.6: Accessing the vulnerabilities scan from the Tasks menu

There is a requirement to state a location to save the assessment to. This will be the location where you can open and view historical reports:

Figure 2.7: The scan dialog box

Note

The scan is lightweight and read-only. It will not cause performance degradation.

Figure 2.8: Vulnerability Assessment Results

As you can see, a wide range of different checks is carried out. The ones that fail will need special attention, especially if they are flagged as High Risk. You can think of this as your own personal security dashboard.

As you review your assessment results, you can mark specific results as being an acceptable baseline in your environment:

Figure 2.9: Assessment results

This is simply a way of approving a check so that it will be classed as a pass in future scans:

Figure 2.10: Baseline approval dialog box

To address issues flagged by this feature, there is no need for you to be a security expert or even research the T-SQL scripts needed to further investigate and fix the issues. This is all provided by the tool. As you can see in the following screenshot, the VA2108 check, relating to the authentication and authorization of a specific account, failed. We purposely implemented this rogue account to see how the tool picks this up.

Figure 2.11: The VA2108 check

If you click the blue box in the preceding screenshot, it will show the code the scan used to deduce its conclusions:

SELECT user_name(sr.member_principal_id) as [Principal], user_name(sr.role_principal_id) as [Role], type_desc as [Principal Type]
FROM sys.database_role_members sr, sys.database_principals sp
WHERE sp.principal_id = sr.member_principal_id
AND sr.role_principal_id IN (user_id('bulkadmin'),
                             user_id('db_accessadmin'),
                             user_id('db_securityadmin'),
                             user_id('db_ddladmin'),
                             user_id('db_backupoperator'))
ORDER BY sp.name

This gives the following result:

Figure 2.12: Assigned role is db_securityadmin

Clearly this is an issue. Having an SQL login granted the db_securityadmin role is bad practice. To resolve this, you then view the following remediation script, as shown in the red box in Figure 2.11:

ALTER ROLE [db_securityadmin] DROP MEMBER [SQLadmin]

Transparent Data Encryption

Transparent Data Encryption (TDE) is also known as "encryption at rest" and uses Advanced Encryption Standard (AES) encryption algorithms using keys sized at 128, 192, and 256 bits (AES_128, AES_192, and AES_256). This feature performs real-time I/O encryption and decryption of database files, and as a side effect, it also encrypts backups. The purpose of TDE is to prevent stolen copies of database files (or backups) from being attached/restored and queried. This feature is also important when running SQL Server in a hosted environment due to the risk that someone is trying to read the file system directly. This feature is available in both Standard and Enterprise edition of SQL Server 2019, and is on by default when using Azure SQL Database and Azure SQL Database Managed Instance.

A common approach to implementing TDE is the traditional encryption hierarchy shown in Figure 2.13:

Figure 2.13: Transparent database encryption architecture

Setup

Following this hierarchy when setting up TDE in SQL Server 2019 is straightforward. This snippet shows the T-SQL code required to create the MASTER KEY, CERTIFICATE, and DATABASE ENCRYPTION KEY.

USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO 
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
GO
USE [MicrosoftDB];  
GO  
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_256  
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;  
GO

Note

The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

A warning appears from SQL Server asking us to back up the CERTIFICATE and PRIVATE KEY, which is important to do for recovery purposes. Use this code to do so:

USE master;
GO
BACKUP CERTIFICATE MyServerCert
TO FILE = 'C:\SQLSERVER\MyServerCert.cer'
WITH PRIVATE KEY
(FILE = 'C:\SQLSERVER\certificate_Cert.pvk',
ENCRYPTION BY PASSWORD = '!£$Strongpasswordherewelikesqlver#')
ALTER DATABASE  [MicrosoftDB]
SET ENCRYPTION ON;  
GO  

Confirmation of successfully encrypting the database can be found by running the following query:

SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
         WHEN '0' THEN 'No database encryption key present, no encryption'
         WHEN '1' THEN 'Unencrypted'
         WHEN '2' THEN 'Encryption in progress'
         WHEN '3' THEN 'Encrypted'
 WHEN '4' THEN 'Key change in progress'
 WHEN '5' THEN 'Decryption in progress'
 WHEN '6' THEN 'Protection change in progress '
         ELSE 'No Status'
      END,
percent_complete, create_date, key_algorithm, key_length, encryptor_type,encryption_scan_modify_date
  FROM sys.dm_database_encryption_keys

Figure 2.2 shows the encrypted state of both the user database and tempdb:

Figure 2.14: Encrypting databases

New features – suspend and resume

When configuring TDE for a database, SQL Server must perform an initial encryption scan. This can sometimes be problematic with a large and highly transactional database. With SQL Server 2019, you can now suspend and resume this scan to fit your needs during specific maintenance windows. Prior to SQL Server 2019, the only way to stop the encryption scan was with Trace Flag 5004.

The T-SQL command that suspends the encryption scan is as follows:

ALTER DATABASE [AdventureDB] SET ENCRYPTION SUSPEND;

If you check the error log in Figure 2.15, you will see that the scan has been paused.

Figure 2.15: Error log

To resume the scan, you then issue the RESUME command shown in the following snippet. Checking the state of encryption via the query from the previous section will show the percentage of completion which is the last point it resumed from.

ALTER DATABASE [AdventureDB] SET ENCRYPTION RESUME;

Figure 2.16: Completed scan percentage

The error log confirms that the scan is complete:

Figure 2.17: Confirmation that scan is complete

You will also notice a new column within the table called encryption_scan_modify_date. This is stored within the sys.dm_database_encryption_keys dynamic management view. It holds the date and time of the last encryption scan state change, which is based on when the scan was last suspended or resumed. Suspending and resuming a scan also applies to the decryption process when encryption is turned off for TDE.

If you restart SQL Server while the encryption scan is in a suspended state, a message will be written to the error log to highlight this fact. It will also show you the RESUME command needed to complete the encryption scan:

Figure 2.18: Error log with the RESUME command

Extensible Key Management

When configuring TDE, you can follow the steps we've looked at so far to implement a traditional key hierarchy strategy. However, you can also use Azure Key Vault as an Extensible Key Management (EKM) provider, which uses an asymmetric key that is outside SQL Server, rather than a certificate within the master database. As you can imagine, this adds another layer of security, which is usually the preferred strategy for many organizations.

For further information on how to implement EKM using Azure Key Vault, please see the following guide: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/setup-steps-for-extensible-key-management-using-the-azure-key-vault?view=sql-server-ver15.