The Data Discovery and Classification feature enables you to identify, classify, and label data held across your SQL Server estate. The sheer volume of data now held within databases makes this a challenging process, coupled with the fact that regulatory mandates such as GDPR, SOX, and PCI demand that businesses protect sensitive data. So you can see how this feature will help. Before you can develop a security strategy for your SQL Server databases, it makes logical sense to know what data you hold, and from this you can then classify and label the more sensitive data and implement the relevant security controls, therefore minimizing potential sensitive data leaks.

Key components for this feature include two metadata attributes, labels and information types. Labels are used to define the sensitivity of data. Information types are used to provide additional granularity into the types of data stored in a column. As you can see in Figure 2.1, email addresses and phone numbers have been classified as contact information under the GDPR label.

Figure 2.1: Classification confirmation

To start the classification process, you will need to right-click on the database and find the Data Discovery and Classification option (Figure 2.2).

Figure 2.2: Accessing the Classify Data... option from the menu

While you are connected to the database via SSMS (SQL Server Management Studio), you can issue the following query to get a really good summary of the classification that has just taken place:

SELECT
    schema_name(O.schema_id) AS schema_name,
    O.NAME AS table_name,
    C.NAME AS column_name,
    information_type,
    sensitivity_label 
FROM
    (
        SELECT
            IT.major_id,
            IT.minor_id,
            IT.information_type,
            L.sensitivity_label 
        FROM
        (
            SELECT
                major_id,
                minor_id,
                value AS information_type 
            FROM sys.extended_properties 
            WHERE NAME = 'sys_information_type_name'
        ) IT 
        FULL OUTER JOIN
        (
            SELECT
                major_id,
                minor_id,
                value AS sensitivity_label 
            FROM sys.extended_properties 
            WHERE NAME = 'sys_sensitivity_label_name'
        ) L 
        ON IT.major_id = L.major_id AND IT.minor_id = L.minor_id
    ) EP
    JOIN sys.objects O
    ON  EP.major_id = O.object_id 
    JOIN sys.columns C 
    ON  EP.major_id = C.object_id AND EP.minor_id = C.column_id

Figure 2.3: Successfully connected to the database

You can delegate this to SQL Server and let it carry out a review of the data and an automatic implementation of the classification process.

Figure 2.4: Classification changes been implemeted

Note

With SQL Server 2019, is it not possible to use T-SQL to add metadata about the sensitivity classification, such as the following:

ADD SENSITIVITY CLASSIFICATION TO

    <object_name> [, ...n ]

    WITH ( <sensitivity_label_option> [, ...n ]

This is only possible with Azure SQL Database.

Another advantage of this feature is the visibility of the classification states in the form of a report, which you can then export to different formats as required. This will benefit you regarding compliance and auditing. The following screenshot shows a copy of a report in Excel format:

Figure 2.5: SQL Data Classification Report

Once you understand your data via the classification processes, you can then leverage different features from SQL Server 2019, such as Always Encrypted or Data Masking, to protect these sensitive columns.

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.

SQL Server 2019 includes Always Encrypted, an encryption technology first introduced in SQL Server 2016 which allows clients to encrypt sensitive data inside client applications with the key benefit of never revealing the encryption keys to the database engine.

When using Always Encrypted, data never appears in plain text when querying it, and it is not even exposed in plain text in the memory of the SQL Server process. Only client applications that have access to the relevant keys can see the data. This feature is ideal for protecting data from even highly privileged users such as database administrators and system administrators. It does not prevent them from administrating the servers, but it does prevent them from viewing highly sensitive data such as bank account details.

Algorithm types

Always Encrypted uses the AEAD_AES_256_CBC_HMAC_SHA_256 algorithm. There are two variations: deterministic and randomized. The deterministic encryption always generates the same encrypted value of a given input value. With this encryption type it is possible for your application to perform point lookups, equality joins, indexing and grouping on the encrypted column. The only potential issue of using this encryption type is if the encrypted column contains few values or if the statistics about plaintext data distribution is publicly known – in such cases, an attacker might be able to guess the underlaying plaintext values.

The randomized variation is far less predictable hence more secure but this means that it does not allow such operations mentioned earlier on potential encrypted columns. The different encryption types raise interesting choices for application developers. For example, if you know that your applications must issue group or join-based queries on encrypted columns, then you will have no choice but to use the deterministic algorithm. With the introduction of secure enclaves in SQL Server 2019 support for richer functionality on encrypted columns is now possible, which will be discussed later in the chapter.

Setup

Setting up Always Encrypted is straightforward. For a complete tutorial on how to do this please see the following link: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-wizard?view=sql-server-ver15.

As mentioned earlier, the main two challenges with Always Encrypted are the reduced query functionality and making it necessary to move data out of database for cryptographic operations, such as initial encryption or key rotation. To address this, Microsoft leverages cutting-edge secure enclave technology to allow rich computations and cryptographic operations to take place inside the database engine.

The enclave is a special, isolated, and protected region of memory. There is no way to view the data or the code inside the enclave from the outside, even with a debugger. You can think of it as a black box. This means that an enclave is the perfect place to process highly sensitive information and decrypt it, if necessary. While there are several enclave technologies available, SQL Server 2019 supports Virtualization Based Security (VBS) secure memory enclaves in Windows Server 2019. The Windows hypervisor ensures the isolation of VBS enclaves. The below screen shows what you would see if you try to access the memory of a VBS enclave with a debugger.

Figure 2.19: When trying to access the memory of an enclave with a debugger

References:

• https://aka.ms/AlwaysEncryptedWithSecureEnclaves
• https://www.microsoft.com/security/blog/2018/06/05/virtualization-based-security-vbs-memory-enclaves-data-protection-through-isolation/

How does this benefit us from a database perspective? It now means that new types of computations, such as pattern matching (LIKE%) and comparisons (including range comparison operators, such as > and <), are now supported on encrypted database columns; this was not possible before. SQL Server delegates these computations to the enclave via the client driver over a secure channel. The data is then safely decrypted and processed in the enclave. Another advantage of this new feature is that it allows you to perform cryptographic operations, such as encrypting a column or re-encrypting it to change (rotate) a column encryption key, inside the enclave without moving the data outside of the database, thus boosting the performance of such tasks, especially for larger tables.

How is trust in the enclave established? There is an attestation service, which is used to verify that the enclave is trustworthy before the client attempts to use it:

Figure 2.20: The architecture of Always Encrypted with secure enclaves

SQL Server 2019 supports attesting VBS enclaves using Host Guardian Service (HGS), which is a role in Windows Server 2019. HGS can be configured to use one of two attestation modes:

• Host key attestation authorizes a host by proving it possesses a known and trusted private key. Host key attestation does not allow a client application to verify the Windows hypervisor on the machine hosting SQL Server has not been compromised. Therefore, this attestation mode, which is easy to configure and supported in a broad range of environments, can be only recommended for testing and development.
• TPM attestation validates hardware measurements to make sure a host runs only the correct binaries and security policies. It provides a SQL client application with a proof that the code running inside the enclave is a genuine Microsoft SQL Server enclave library and that the Windows hypervisor on the machine hosting SQL Server has not been compromised. That is why the TPM attestation is recommended for production environments. TPM attestation requires SQL Server runs on a machine supporting TPM 2.0.

For a step-by-step tutorial on how to get started with Always Encrypted using enclaves, see: https://aka.ms/AlwaysEncryptedEnclavesTutorial.

SQL Server 2019 provides dynamic data masking (DDM), which limits sensitive data exposure by masking it to non-privileged users. This is not really a form of encryption at disk but nevertheless is useful in certain scenarios, such as if you want to hide sections of a credit card number from support staff personnel. Traditionally, this logic would have been implemented at the application layer; however, this is not the case now because it is controlled within SQL Server.

Note

A masking rule cannot be applied on a column that is Always Encrypted.  

Types

You can choose from four different masks where selection usually depends on your data types:

• DEFAULT: Full masking according to the data types of the designated fields
• EMAIL: A masking method that exposes the first letter of an email address, such as aXXX@XXXX.com
• RANDOM: A random masking function for use on any numeric type to mask the original value with a random value within a specified range
• CUSTOM: Exposes the first and last letters and adds a custom padding string in the middle

Implementing DDM

The following example creates a table with different masking rules applied to the columns. FirstName will only expose the first letter, Phone will use the default masking rule, and for Email, we will apply the email masking rule:

CREATE TABLE dbo.Users 
  (UserID INT IDENTITY PRIMARY KEY,  
   FirstName VARCHAR(150) MASKED WITH (FUNCTION = 'partial(1,"XXXXX",0)') NULL,  
   LastName VARCHAR(150) NOT NULL,  
   Phone VARCHAR(20) MASKED WITH (FUNCTION = 'default()') NULL,  
   Email VARCHAR(150) MASKED WITH (FUNCTION = 'email()') NULL);  
   GO
  
INSERT dbo.Users
(FirstName, LastName, Phone, Email) 
VALUES   
('Arun', 'Sirpal', '777-232-232', 'Asirpal@company.com'),  
('Tony', 'Mulsti', '111-778-555', 'TMulsti@company.com'),  
('Pedro', 'Lee', '890-097-866', 'PLee@company.com') ,
('Bob', 'Lee', '787-097-866', 'Blee@company.com');  
GO

Figure 2.21: Table with masking rules applied

To confirm your masking rules, the following query should be executed:

SELECT OBJECT_NAME(object_id) TableName, 
    name ColumnName, 
    masking_function MaskFunction
  FROM sys.masked_columns
  ORDER BY TableName, ColumnName;

Figure 2.22: Table with query executed

If you connect to the database as a SQL login that has only read access (as indicated by the following code), you will see the masked data. In other words, the login does not have the right to see the true value of the data, as demonstrated in the following code.

EXECUTE AS USER = 'support'
SELECT SUSER_NAME(), USER_NAME();  
SELECT * FROM  dbo.Users

Figure 2.23: Table with data partially masked

If you decide to allow the user to see the data in its native form, you can issue the GRANT UNMASK command as shown here:

GRANT UNMASK TO support;
 GO
 EXECUTE AS USER = 'support'
SELECT SUSER_NAME(), USER_NAME();  
SELECT * FROM  dbo.Users

Figure 2.24: Table with data unmasked

Issue the REVOKE command to remove this capability:

REVOKE UNMASK TO support;
EXECUTE AS USER = 'support'
SELECT SUSER_NAME(), USER_NAME();  
SELECT * FROM  dbo.Users

Figure 2.25: Table with unmask revoked

As you can see, implementing this feature requires no changes to application code and can be controlled via permissions within SQL Server by deducing who has and has not got the ability to view the data. Even though this is not a true form of encryption at disk level, this feature is only a small element of the wider security strategy for your SQL servers and is best used in conjunction with other features discussed so far to provide broader defense.

Row-level security (RLS) gives database administrators and developers the ability to allow fine-grained access control over rows within tables. Rows can be filtered based on the execution context of a query. Central to this feature is the concept of a security policy where, via an inline table-valued function, you would write your filtering logic to control access with complete transparency to the application. Real-world examples include situations in which you would like to prevent unauthorized access to certain rows for specific logins, for example, only giving access to a super-user to view all rows within a sensitive table and allowing other users to see rows that only the super-user should see. The following example shows how simple it is to implement RLS via T-SQL. At a high level, access to a specific table called rls.All_Patient is defined by a column called GroupAccessLevel, which is mapped to two SQL logins called GlobalManager and General. As you can imagine, the General login will not be able to view the data that GlobalManager is authorized to see.

The following code is the T-SQL required to create the table-value function and the security policy with the state set to ON:

CREATE FUNCTION rls.fn_securitypredicate(@GroupAccessLevel AS sysname)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @GroupAccessLevel = USER_NAME() OR USER_NAME() = 'GlobalManager'; 
GO
CREATE SECURITY POLICY UserFilter  
ADD FILTER PREDICATE RLS.fn_securitypredicate(GroupAccessLevel)
ON rls.All_Patient
WITH (STATE = ON);  
GO
GRANT SELECT ON RLS.fn_securitypredicate TO GlobalManager;  
GRANT SELECT ON RLS.fn_securitypredicate TO General  

Running the code as the GlobalManager user will return all rows within the table, in contrast with the General user, who will only see the rows that they are entitled to see:

EXECUTE AS USER = 'GlobalManager';  
  SELECT * FROM rls.All_Patient 
  ORDER BY AreaID
REVERT;

The following screenshot confirms the data that the General user can only see:

Figure 2.26: A table with access set to GlobalManager

Executing the following code switches the execution context to the General user:

EXECUTE AS USER = 'General';  
  SELECT * FROM rls.All_Patient 
  ORDER BY AreaID
REVERT;

Figure 2.27: The same table with access set to General

If you check the properties of the clustered index scan, you will see the predicate being evaluated:

Figure 2.28: Selecting properties of clustered index scan

Figure 2.29: The predicate evaulation dialog box

This type of predicate is called a filter predicate, but you also can create a block predicate to explicitly block write operations (such as AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, and BEFORE DELETE) that violate the predicate.

For administration purposes, you can query the following system tables to see the security policies and security predicates that have been defined:

SELECT * FROM sys.security_policies

Figure 2.30: Output of a security_policies query

SELECT * FROM sys.security_predicates

Figure 2.31: Output of a security_predicates query

To maintain the best performance, it is best practice to not involve many table joins within the predicate function, to avoid type conversions, and to avoid recursion.

If implementing an auditing strategy is paramount to your business to satisfy regulations such as the Health Insurance Portability and Accountability Act (HIPAA), the Sarbanes-Oxley Act (SOX), and the Payment Card Industry Data Security Standard (PCI-DSS), then leveraging SQL Server 2019 to achieve this is possible with SQL Server Audit. With this feature, you will be able to ensure accountability for actions made against your SQL servers and databases, and you can store this log information in local files or the event log for future analysis, all of which are common goals of an auditing strategy.

To implement SQL Server auditing, first the main audit should be created at the server level, which dictates where the files will be located for information to be logged to. From this main audit, you can then create a server-level audit specification. At this level, you will be able to audit actions such as server role changes and whether a database has been created or deleted. Alternatively, you can scope this feature to the database level via a database-level audit specification, where you can audit actions directly on the database schema and schema objects, such as tables, views, stored procedures, and functions (see https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver15 for a full list of the capabilities for both server- and database-level auditing).

The following example shows the code required to audit a specific table, [HumanResources].[EmployeePayHistory], for a DELETE activity using a database AUDIT specification:

USE [master]
GO
CREATE SERVER AUDIT [MainAudit]
TO FILE 
(  FILEPATH = N'D:\AUDIT\'
  ,MAXSIZE = 1024 MB
  ,MAX_FILES = 10
  ,RESERVE_DISK_SPACE = OFF
)
WITH
(  QUEUE_DELAY = 1000
  ,ON_FAILURE = CONTINUE
  ,AUDIT_GUID = 'A164444-d7c8-4258-a842-9f2111f2c755'
)
ALTER SERVER AUDIT [MainAudit] WITH (STATE = ON)
GO
USE [AdventureDB]
GO
CREATE DATABASE AUDIT SPECIFICATION [DeleteAuditHR]
FOR SERVER AUDIT [MainAudit]
ADD (DELETE ON OBJECT::[HumanResources].[EmployeePayHistory] BY [dbo])
GO
DECLARE @files VARCHAR(200) = 'D:\AUDIT\*.sqlaudit';
SELECT * FROM sys.fn_get_audit_file (@files, default, default)

Figure 2.32: The results statement dialog

As you can see, it is very simple to set up auditing, and you can do so with minimal performance overhead.

Service Socket Layer (SSL) and Transport Layer Security (TLS) are cryptographic protocols that provide encryption between two endpoints, such as a calling application and the SQL Server. This is a form of "encryption in transit." This is a very important concept for companies that process payments. They have to adhere to PCI-DSS. SSL is the predecessor to TLS and supports the need to address vulnerabilities found with SSL, thus providing more secure cipher suites and algorithms. Microsoft's recommendation is to use TLS 1.2 encryption, which supports all releases of SQL Server (assuming that the latest service packs are installed) up to and including SQL Server 2019. The ultimate goal of using TLS is to establish a secure connection. This is done by SQL Server sending its TLS certificate to the client. The client must then validate its copy of the Certification Authority (CA) certificate. The CA is a trusted third party that is trusted by both the owner of the certificate and the party relying upon the certificate.

Assuming that you have configured the Microsoft Management Console (MMC) snap-in, you will then need to use it to install the certificate on the server. It can then be used by SQL Server to encrypt connections.

Configuring the MMC snap-in

To open the MMC certificates snap-in, follow these steps:

1. To open the MMC console, click Start, and then click Run. In the Run dialog box, type the following:

   MMC

2. In the Console menu, click Add/Remove Snap-in.
3. Click Add, and then click Certificates. Click Add again.
4. You are prompted to open the snap-in for the current user account, the service account, or for the computer account. Select Computer Account.
5. Select Local computer, and then click Finish.
6. Click Close in the Add Standalone Snap-in dialog box.
7. Click OK in the Add/Remove Snap-in dialog box. Your installed certificates are located in the Certificates folder in the Personal container.

For the complete steps to install a certificate, please see the following link: https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi.

Enabling via SQL Server Configuration Manager

For SQL Server to use the certificate, you will need to select it within SQL Server Configuration Manager and then finally set Force Encryption to Yes:

Figure 2.33: Protocols for RED Properties dialog box

In SQL Server 2019, improvements have been made to SQL Server Configuration Manager, such as optimizations for administration and setting up certificates. First, more information is presented to the administrator regarding expiration dates, a simple but useful addition. More importantly, from an availability group or failover cluster perspective, it is now possible to deploy certificates across multiple machines that form the failover cluster or availability group. This reduces the administration overhead of separately installing and managing certificates across multiple nodes, which can become time-consuming for complex architectures.

Security is absolutely at the forefront of Microsoft's strategy, and this is no different when operating with their cloud services. If you want to run database workloads in Microsoft Azure, you can be assured that Azure SQL Database (the PaaS offering) has all the features mentioned in this chapter so far, and more. For the remainder of this chapter, Azure SQL Database's specific security features will be discussed.

SSL/TLS

SSL/TLS is enforced for all connections. This means that data between the database and client is encrypted in transit (as mentioned in the previous section). For your application connection string, you must ensure that Encrypt=True and TrustServerCertificate=False because doing this will help prevent man-in-the-middle attacks. No manual certificate configuration is needed; this is all done by Microsoft as the default standard.

A typical connection string should look like this:

Server=tcp:yourserver.database.windows.net,1433;Initial Catalog=yourdatabase;
Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;
TrustServerCertificate=False;Connection Timeout=30;

Firewalls

Microsoft implements a "deny all by default" policy for Azure SQL Database. That is, when you create a "logical" SQL server in Azure to host your database you as the administrator will need to make further configuration changes to allow for successful access. This is usually in the form of firewall rules (which can be scoped to the server level or the database level), where you would state which IP addresses are allowed access and Virtual Network (VNet) rules.

VNet rules should be implemented where possible. A VNet contains a subnet address; you can then create a VNet rule that is scoped to the server level, which will allow access to databases on that server for that specific subnet. This means that if you have virtual machines built within a specific subnet bound to the VNet rule, it will have access to Azure SQL Database (assuming that the Microsoft.sql endpoint is enabled). Both firewall rules and VNet rules can be used together if there is a need.

Azure Active Directory (AD) authentication

With Azure AD authentication, you can now centrally manage database users from one central location. This approach is not only much more secure than SQL Server authentication, but also allows for password rotation to occur in a single place. You can control permissions via groups, thus making security management easier. Configuring this feature will also allow you to connect to the database using multi factor authentication (MFA), which includes verification options such as text messages, phone calls, mobile app integration, and smart cards with PINs. This idea of MFA is also built into tools such as SSMS, thus providing an extra layer of security for users that require access to Azure SQL Database. It is a highly recommended approach.

The trust architecture is shown in Figure 2.47 and the setup is simple:

Figure 2.34: The trust architecture

Complete configuration steps can be found at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication. Once configuration is complete, you will be able to issue the following code to create an Azure AD-based database user once you have connected to the "logical" SQL Server as the Azure AD Admin user:

CREATE USER [Anita.Holly@Adventureworks.com]
FROM EXTERNAL PROVIDER;
GRANT CONNECT TO [Anita.Holly@Adventureworks.com]
EXEC sp_addrolemember 'db_datareader', 'Anita.Holly@Adventureworks.com';

Advanced data security

Advanced Data Security (ADS) is a suite of advanced features that you can enable for a small cost. The cost of this is based on Azure Security Center standard tier pricing (it's free for the first 30 days). The cost includes Data Discovery & Classification, Vulnerability Assessment (similar to what we discussed previously for on-premises SQL servers), and Advanced Threat Protection for the server:

Figure 2.35: ADS suite dashboard

To enable this, you will need to navigate to the Security section of the database via the Azure portal:

Figure 2.36: Setting up a security alert on the Azure portal

Once you have selected the Advanced Data Security section, you will be prompted with the cost associated with the feature:

Figure 2.37: Cost prompt dialog

Finally, you will then have the option of enabling the setting as shown here:

Figure 2.38: Advanced Data Security dialog

Advanced threat detection

Threat detection is the only feature from the previous section that is not available with on-premises SQL Server 2019, but it is available with Azure SQL Database. This service detects anomalous activities that indicate unusual and potentially harmful attempts to access or exploit databases such as SQL injection, brute force attacks, and unknown IP address analysis. Microsoft analyzes a vast amount of telemetry regarding cloud network activity and uses advanced machine learning algorithms for this proactive service. It is best practice to enable this setting. There is a cost associated with it, but the benefit outweighs this minimal cost. Cyber attacks are becoming more sophisticated, and this is where threat prevention and detection tools form an important piece of your defense strategy. This setting can be applied to the server or the database.

Figure 2.48 shows a real-time email alert being sent to administrators:

Figure 2.39: Real-time vulnerability alert

You can see the VULNERABLE STATEMENT that was used; a classic SQL injection-style attack was detected.

Hopefully, you can see the vast amount of effort that has gone into Azure SQL Database and SQL Server 2019 regarding security. All the tools and features discussed in this chapter, when put together, will help you create an enterprise-level data platform of trust.