Pseudonymization

• Problem statement. Personal data must be transformed in a way that the resulting data cannot be attributed to a data subject without additional information.

• Proposed solution. This can be implemented through encryption. We cover this in detail later in this chapter in the section “Introduction to security principles and protocols,” including various methods to ensure that sensitive data is not made available to privileged users and administrators.

  Note

  Anonymizing data is different from encrypting or masking data.

Right of access

• Problem statement. A data subject may request access to their personal data.

• Proposed solution. This can be achieved through standard Transact-SQL (T-SQL) queries, assuming you have taken measures to appropriately handle encryption of the data and authentication of the data subject. Chapter 2, “Introduction to database server components,” covers authentication and authorization.

Right to erasure

• Problem statement. A data subject may request erasure of their personal data.

• Proposed solution. To erase personal data, you must identify it. This can become arduous with more than a few databases. Organizations may not appreciate the numerous environments in which they store personal data, including scaled-out applications, data warehouses, data marts, data lakes, and email. Fortunately, you can catalog and tag sensitive data in SQL Server and Azure SQL Database.

Hashing

A cryptographic hash function (algorithm) takes variable-length data (usually a password) and applies a mathematical formula to convert it to a fixed size, or hash value.

This is the recommended method of securing passwords. When a password has been hashed correctly, it cannot be decrypted into its original form. When used with a random salt (a random string applied along with the hash function), the result is a password that is impossible to reconstruct—even if the same password is used by different people.

To validate a password, it must be hashed using the same hash function again, with the same salt, and compared against the stored hash value.

Because hash values have a fixed size (the length depends on the algorithm used), there is a chance that two sets of data (two different passwords) can result in the same hash value. This is called a hash collision, and it is more likely to occur with shorter hash value lengths. This is why longer hashes are better.

Encryption

Data encryption is the process of converting human-readable data, or plain text, into an encrypted form by applying a cryptographic algorithm called a key (cipher) to the data. This process makes the encrypted data (ciphertext) unreadable without the appropriate key to unlock it. Encryption facilitates both the secure transmission and storage of data.

Over the years, many ciphers have been created and subsequently defeated (cracked). In many cases, this is because both central processing units (CPUs) and graphics processing units (GPUs) have become faster and more powerful, reducing the length of time it takes to perform brute-force and other attacks. In other cases, the implementation of the cryptographic function was flawed, and attacks on the implementation itself have been successful.

The Internet Protocol suite

To discuss security on a network, you must understand cryptographic protocols. To discuss the network itself, you must discuss the biggest network of them all: the Internet.

The Internet is a network of networks (it literally means “between networks”) that transmits data using a suite of protocols. These include TCP, which sits on top of another protocol, called Internet Protocol (IP). Together, they are called TCP/IP. TCP/IP is the most common network protocol stack in use today. Most of the services on the Internet, as well as local networks, rely on TCP/IP.

IP is a connectionless protocol, meaning each individual unit of transfer, also known as a network packet or datagram, contains a payload (the data itself) and a header that indicates where it came from and where it needs to go (the routing information).

IP network packets might be delivered out of order, with no delivery guarantee at all. This low overhead makes the protocol fast and allows packets to be sent to several recipients at once (multicast or broadcast). TCP, however, provides the necessary instructions for reliability, sequencing (the order of packets), and data integrity. If a packet is not received by the recipient, or a packet is received out of order, TCP can resubmit the data again using IP as its delivery mechanism.

Versions of IP in use today

Internet Protocol Version 4 (IPv4) has a 32-bit address space, which provides nearly 4.3 billion addresses (2³², or approximately 4.3 × 10⁹). Unfortunately, when this version was first proposed in September 1981, very few people could have imagined the Internet would be as large and important as it is today. With billions of humans online, and billions of devices connected, the available IPv4 address space is all but depleted.

• You can read the IPV4 specification, known as Internet Engineering Task Force Request For Comments #791, at https://tools.ietf.org/html/rfc791.

Techniques like Network Address Translation (NAT), which uses private IP addresses behind a router with a single valid public IP address representing that entire network, have held off the depletion over the years, but time and address space have run out.

Internet Protocol Version 6 (IPv6) has an address space of 128 bits, which provides more than 340 undecillion (340 trillion trillion) addresses (2¹²⁸, or approximately 3.4 × 10³⁸). This number is so staggeringly huge that, even with networks and devices being added every minute, including the upward trend in the use of the Internet of Things (IoT), each of these devices can have its own unique address on the Internet without ever running out of addresses.

• You can read the IPV6 Specification, known as Internet Engineering Task Force Request For Comments #8200, at https://tools.ietf.org/html/rfc8200.

• For more information about Azure SQL Edge, visit https://azure.microsoft.com/products/azure-sql/edge/.

Deconstruct an IP address

An IP address is displayed in a human-readable notation but is binary under the hood:

• IPv4. The address is broken into four subclasses of decimal numbers, each ranging from 0 to 255, and separated by a decimal point. For example, 52.178.167.109 is a valid IPv4 address.

• IPv6. The address is broken into eight subclasses of hexadecimal numerals, each being four digits wide, and separated by a colon. If a subclass contains all zeros, it can be omitted. For example, 2001:d74f:e211:9840:0000:0000:0000:0000 is a valid IPv6 address that can be simplified to 2001:d74f:e211:9840:: with the zeros omitted (note the double-colon at the end to indicate the omission).

  • You can read more about IPv6 address representation at http://www.ciscopress.com/articles/article.asp?p=2803866.

  Note

  Hexadecimal is a counting system that uses all the decimal numbers plus the first six letters of the Latin alphabet to represent the 16 values between 0 and 15 (10 = A, 11 = B, 12 = C, 13 = D, 14 = E, 15 = F). Using hex is a convenient way to describe binary values that would otherwise take up a lot of space to display.

Adoption of IPv6 across the Internet is taking decades, so a hybrid solution is currently in place, by which IPv4 and IPv6 traffic is shared across compatible devices. If that doesn’t sound like enough of a headache, let’s add routing into the mix.

Find your way around the Internet

Routing between networks on the Internet is performed by the Border Gateway Protocol (BGP), which sits on top of TCP/IP. BGP is necessary because there is no map of the Internet. Devices and entire networks appear and disappear all the time.

BGP routes billions of network packets through millions of routers based on a best-guess scenario. Packets are routed based on trust: routers provide information to one another about the networks they control, and BGP implicitly trusts that information.

BGP is therefore not secure, because it was designed solely to address the scalability of the Internet, which was (and still is) growing exponentially. It was a “quick fix” that became part of the fabric of the infrastructure long before security was a concern.

Efforts to secure BGP have been slow. It is therefore critical to assume that your own Internet traffic will be hijacked at some point. When this happens, proper cryptography can prevent third parties from reading your data.

A brief overview of the World Wide Web

The World Wide Web (the web) is a single component of the greater Internet, along with email and other services that are still very much in use today, such as File Transfer Protocol (FTP) and Voice over IP (VoIP).

The web uses the Hypertext Transfer Protocol (HTTP), which sits on top of TCP/IP. A web server provides mixed media content (text, graphics, video, and other media) in Hypertext Markup Language (HTML) format, which is transmitted using HTTP and then interpreted and rendered by a web browser. Modern web browsers include Microsoft Edge, Google Chrome, Mozilla Firefox, and Apple Safari.

How does protocol encryption fit into this?

The explosive adoption of the web in the 1990s prompted public-facing organizations to start moving their sales online for electronic commerce (e-commerce) ventures, which created the need for secure transactions. Consumers wanted to use their credit cards safely and securely so they could shop and purchase goods online.

Remember that the Internet is built on IP, which is stateless and has routing information in the header of every single packet. This means anyone can place a hardware device (or software) in the packet stream, do something with the packet, and then pass it on (modified or not) to the destination without the sender or recipient having any knowledge of this interaction. Because this is a fundamental building block of a packet-switching network, it’s very difficult to secure properly.

As discussed, encryption transforms your data into an unreadable format. Now, if someone connected to the same network were to intercept encrypted packets, that person couldn’t see what you’re doing. The payload of each packet would appear garbled and unreadable unless this person had the key to decrypt it.

Netscape Communications created a secure version of HTTP in 1994 called HTTPS, which stands for HTTP Secure or HTTP over Secure Sockets Layer (SSL). Over the years, the moniker of HTTPS has remained, but as standards improved, it came to be known as HTTP over Transport Layer Security (TLS).

Understand symmetric and asymmetric encryption

When we talk about data moving over the network, that usually means TCP/IP is involved, and we must transmit that data securely, through encryption. You can encrypt data in two ways: symmetric and asymmetric. Each has its advantages and disadvantages.

Symmetric encryption (shared secret)

A secret key—usually a password, passphrase, or random string of characters—is used to encrypt data with a particular cryptographic algorithm. This secret key is shared between the sender and the recipient, and both parties can encrypt and decrypt all content using this secret key.

If the key is accidentally leaked to a third party, the encrypted data could be intercepted, decrypted, modified, and re-encrypted—again, without either the sender or recipient being aware of this. This type of attack is known as a man-in-the-middle attack.

Asymmetric encryption (public key)

Also known as public key encryption (PKE), asymmetric encryption generates a key-pair composed of a public key (used to encrypt data) and a private key (used to decrypt that data). The public key can be widely distributed.

The private key never needs to be shared. Therefore, this method is far more secure, because only you can use your private key to decrypt the data. Unfortunately, however, asymmetric encryption requires a lot more processing power, and both parties need their own key-pairs.

How TLS works

With TLS protection, before two parties can exchange information, they must mutually agree on which encryption key and cryptographic algorithm to use. This is called a key exchange or handshake. TLS works with both symmetric and asymmetric encryption, which means the encryption key could be a shared secret or a public key (usually with a certificate).

After the key exchange, the handshake is complete, and a secured communication channel allows traffic between the two parties to flow. This is how data in motion is protected from external attacks.

A brief history of TLS

Just as earlier cryptographic protocols have been defeated or considered weak enough that they will eventually be defeated, so too have SSL and TLS had their challenges:

• The prohibition of SSL 2.0 is covered at https://tools.ietf.org/html/rfc6176.

• Known attacks on TLS are available at https://tools.ietf.org/html/rfc7457.

TLS 1.2 was defined in 2008 and is the most widely available public version.

Like its predecessors, TLS 1.2 is vulnerable to certain attacks, but as long as you don’t use older encryption algorithms (for instance 3DES, RC4, and IDEA), it is good enough while we wait for TLS 1.3 to propagate. You should use TLS 1.2 or TLS 1.3 wherever possible. Once support is available in client libraries, however, you should switch to TLS 1.3. SQL Server ships with older versions of TLS, so you must disable 1.0 and 1.1 at the OS level to ensure you use at least TLS 1.2.

• You can see how to disable older versions of TLS in the Microsoft Knowledge Base article at https://support.microsoft.com/help/3135244.

• You can read more about how TDS and TLS interact at https://learn.microsoft.com/sql/relational-databases/security/networking/tds-8-and-tls-1-3.

Cloud security with Azure Key Vault

You can use Azure Key Vault in addition to, or as a drop-in replacement for, a traditional HSM/EKM device. For this to work, your SQL Server instance (whether on-premises or on a VM in the cloud) requires Internet access to the Key Vault.

Key Vault is implemented as an EKM provider inside SQL Server, using the SQL Server Connector (a standalone Windows application) as a bridge between Key Vault and the SQL Server instance. To use Key Vault, you must create the vault and associate it with a valid Azure Active Directory (Azure AD).

Begin by registering the SQL Server service principal name in Azure AD. Then you install the SQL Server Connector and enable EKM in SQL Server.

• You can read more about service principal names and Kerberos in Chapter 2.

You must then create a login for SQL Server to use to access Key Vault, and map that login to a new credential that contains the Key Vault authentication information.

• A step-by-step guide for this process is available at https://learn.microsoft.com/sql/relational-databases/security/encryption/setup-steps-for-extensible-key-management-using-the-azure-key-vault.

The SMK

In SQL Server, the SMK is at the top of the encryption hierarchy. It is automatically generated the first time the SQL Server instance starts, and it is encrypted by the DPAPI in combination with the local machine key (which itself is created when Windows Server is installed). The key is based on the Windows credentials of the SQL Server service account and the computer credentials. (On Linux, the local machine key is part of the PAL used by SQL Server.)

If you need to restore or regenerate an SMK, you first must decrypt the entire SQL Server encryption hierarchy, which is a resource-intensive operation. You should perform this activity only in a scheduled maintenance window. If the key has been compromised, however, you shouldn’t wait for the maintenance window.

To back up the SMK, you can use the following T-SQL script, but be sure to choose a randomly generated password. The password will be required for restoring or regenerating the key at a later stage. Keep the password separate from the SMK backup file so they cannot be used together if your secure backup location is compromised. Ensure that the folder on the drive is adequately secured. You can also use an Azure Blob Storage URL instead of a local file path. After you back up the key, transfer and store it securely in an off-premises location.

Click here to view code image

BACKUP SERVICE MASTER KEY TO FILE = 'c:SecureLocationservice_master_key'
   ENCRYPTION BY PASSWORD = '<UseAReallyStrongPassword>';
GO

The DMK

The DMK is used to protect asymmetric keys and private keys for digital certificates stored in the database. A copy of the DMK is stored in the database for which it is used as well as in the master database. The copy is automatically updated by default if the DMK changes. This allows SQL Server to automatically decrypt information as required. A DMK is required for each user database that will use TDE. Refer to Figure 13-1 to see how the DMK is protected by the SMK.

It is considered a security best practice to periodically regenerate the DMK to protect the server from brute-force attacks. The idea is to ensure that it takes longer for a brute-force attack to break the key than the length of time for which the key is in use. For example, suppose you encrypt your database with a DMK in January and regenerate it in July, causing all keys for digital certificates to be re-encrypted with the new key. If anyone has begun a brute-force attack on data encrypted with the previous DMK, all results from that attack will be rendered useless by the new DMK.

You can back up the DMK using the following T-SQL script. The same rules apply as with backing up the SMK (choose a random password, store the file off-premises, and keep the password and backup file separately). You can also use an Azure Blob Storage URL instead of a local file path. This script assumes the master key exists.

Click here to view code image

USE WideWorldImporters;
GO
BACKUP MASTER KEY TO FILE = 'c:SecureLocationwwi_database_master_key'
    ENCRYPTION BY PASSWORD = '<UseAReallyStrongPassword>';
GO

• You can read more about the SMK and DMK at https://learn.microsoft.com/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine.

Configure TDE on a user database

To use TDE on SQL Server, you must create a DMK if you don’t have one already. Verify that it is safely backed up and securely stored off-premises. If you have never backed up the DMK, the Database Engine will warn you after you use it that the DMK has not yet been backed up. If you don’t know where that backup is, back it up again. This is a crucial detail to using TDE (or any encryption technology).

Next, you create a digital certificate or use one that you have acquired from a CA. In the next example, the certificate is created on the server directly. Then, you create the DEK, which is signed by the certificate and encrypted using a cryptographic algorithm of your choice.

• To read more about managing database encryption keys in SQL Server, visit https://learn.microsoft.com/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine.

Although you do have a choice of algorithm, we recommend AES over 3DES for performance and security reasons. Additionally, you can choose from three AES key sizes: 128, 192, or 256 bits. Remember that larger keys are more secure, but will add additional CPU overhead when encrypting data. If you plan to rotate your keys every few months, you can safely use 128-bit AES encryption because no brute-force attack (using current computing power) can compromise a 128-bit key in the months between key rotations.

After you create the DEK, you enable encryption on the database. The command completes immediately, but the process takes place in the background because each page in the database must be read into the buffer pool, encrypted, and persisted to the drive.

Verify whether TDE is enabled for a database

To determine which databases are encrypted with TDE, issue the following T-SQL query:

Click here to view code image

SELECT name, is_encrypted FROM sys.databases;

If a user database is encrypted, the is_encrypted column value for that database is set to 1. tempdb will also show a value of 1 in this column.

Manage and monitor the TDE scan

Enabling TDE on a database requires each data page to be read into the buffer pool before being encrypted and written back out to the drive. If the database instance is under a heavy workload, you can pause the scan and resume it at a later stage.

To pause the scan on the WideWorldImporters sample database, issue the following T-SQL command:

Click here to view code image

ALTER DATABASE WideWorldImporters SET ENCRYPTION SUSPEND;

To resume the scan on the same database, issue the following T-SQL command:

Click here to view code image

ALTER DATABASE WideWorldImporters SET ENCRYPTION RESUME;

You can also check the progress of the TDE scan using the encryption_scan_state column in the sys.dm_database_encryption_keys DMV. To see when the state was last modified, refer to the encryption_scan_modify_date column in the same DMV.

Client application providers that support Always Encrypted

The following providers currently support Always Encrypted:

• Microsoft .NET Data Provider for SQL Server

• Microsoft JDBC Driver 6.0 or higher

• ODBC Driver 13.1 for SQL Server or higher

• Microsoft Drivers 5.2 for PHP for SQL Server or higher

The connection between the Database Engine and application is made using a client-side encrypted connection. Each provider has its own appropriate method to control this setting:

• .NET. Set ColumnEncryptionSetting in the connection string to Enabled or configure the SqlConnectionStringBuilder.ColumnEncryptionSetting property as SqlConnectionColumnEncryptionSetting.Enabled.

• JDBC. Set columnEncryptionSetting to Enabled in the connection string or configure the SQLServerDataSource() object with the setColumnEncryptionSetting("Enabled") property.

• ODBC. Set the ColumnEncryption connection string keyword to Enabled and use the SQL_COPT_SS_COLUMN_ENCRYPTION pre-connection attribute or set it using the Data Source Name (DSN) with the SQL_COLUMN_ENCRYPTION_ENABLE setting.

• PHP. Set the ColumnEncryption connection string keyword to Enabled. Note that PHP drivers use ODBC drivers for encryption.

Additionally, the application must have the VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION database permissions to view the column master key and column encryption key (see next section).

The column master key and column encryption key

The column master key (CMK) protects one or more column encryption keys (CEKs). The CEK is encrypted using AES encryption and is used to encrypt the actual column data. You can use the same CEK to encrypt multiple columns, or you can create a CEK for each column that needs to be encrypted.

• You can read more about ADR in Chapter 3.

Metadata about the keys (but not the keys themselves) is stored in the database’s system catalog views:

• sys.column_master_keys

• sys.column_encryption_keys

This metadata includes the type of encryption and location of the keys, plus their encrypted values. Even if a database is compromised, the data in the protected columns cannot be read without access to the secure key store.

• To read more about considerations for key management, visit https://learn.microsoft.com/sql/relational-databases/security/encryption/overview-of-key-management-for-always-encrypted.

Use the Always Encrypted Wizard

The easiest way to configure Always Encrypted is to use the Always Encrypted Wizard in SSMS. As noted, you must have the following permissions before you begin:

• VIEW ANY COLUMN MASTER KEY DEFINITION

• VIEW ANY COLUMN ENCRYPTION KEY

If you plan to create new keys, you also need the following permissions:

• ALTER ANY COLUMN MASTER KEY

• ALTER ANY COLUMN ENCRYPTION KEY

In SSMS, in Object Explorer, right-click the name of the database that you want to configure, select Tasks, and choose Encrypt Columns. This opens the Always Encrypted wizard.

On the Column Selection page, choose the column you want to encrypt, and then select the encryption type (deterministic or randomized). If you want to decrypt a previously encrypted column, you can choose Plaintext.

On the Master Key Configuration page, you can create a new key using the local OS certificate store or using a centralized store like Azure Key Vault or an HSM/EKM device. If you already have a CMK in your database, you can use it instead. Also choose the master key source—either Current User or Local Machine.

Limitations in Always Encrypted

Certain column types are not supported by Always Encrypted, including:

• image, (n)text, xml, sql_variant, timestamp or rowversion data types

• string columns with non-BIN2 collations (reduced features are available with non-BIN2 collations, but for best results, use a BIN2 collation)

• FILESTREAM columns

• Columns with IDENTITY or ROWGUIDCOL properties

• Columns with default constraints or referenced by check constraints

  • You can read the full list of limitations and find out more about Always Encrypted at https://learn.microsoft.com/sql/relational-databases/security/encryption/always-encrypted-database-engine.

Configure Always Encrypted with secure enclaves

Always Encrypted with secure enclaves requires a complex environment to guarantee the security it offers. This includes the following:

• Host Guardian Service (HGS). Install HGS in a Windows Server 2022 failover cluster with three computers in its own AD forest. These computers must not be connected to an existing AD, and none of them can be used for the SQL Server installation. Microsoft suggests that this cluster be isolated from the rest of your network, and that different administrators manage this environment. The only access to HGS will be through HTTP (TCP port 80) and HTTPS (TCP port 443).

• SQL Server 2022. Install SQL Server 2022 on Windows Server 2022, on its own physical hardware. VMs do not support the recommended Trusted Platform Module (TPM) enclave attestation, which is hardware-based.

  • You can read more about TPM attestation at https://learn.microsoft.com/windows-server/identity/ad-ds/manage/component-updates/tpm-key-attestation.

• Tools for client and development. Install the requisite tools on your client machine:

  • Microsoft .NET Data Provider for SQL Server 2.1.0 or later

  • SSMS (SSMS) 19.0 or later

  • SQL Server PowerShell module version 21.1 or later

  • Visual Studio 2017 or later

  • Developer pack (SDK) for .NET Standard 2.1 or later

  • Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders NuGet package

  • Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider NuGet package version 2.2.0 or later, if you plan to use Azure Key Vault for storing your column master keys

• Configured enclave. From the client/development machine, you use SSMS to enable the enclave. Rich computations are disabled by default for performance reasons. If you want to use this feature, you must enable it after every SQL Server instance restart using Trace Flag 127. Make sure you test the performance impact on your environment before enabling this feature in production.

• Enclave-enabled keys. This works in a similar way to regular Always Encrypted keys, except the CMKs provisioned through HGS are marked as enclave-enabled. You can use SSMS or PowerShell to provision these keys, and store them either in the Windows Certificate Store or Azure Key Vault.

• Encrypted sensitive columns. You use the same methods to encrypt data as before.

  • A step-by-step walkthrough of this process is available at https://learn.microsoft.com/sql/relational-databases/security/encryption/configure-always-encrypted-enclaves.

Filter predicates for read operations

You can silently filter rows that are available through read operations. The application then has no knowledge of the other data that is filtered out.

Filter predicates affect all read operations. This list is taken directly from the official documentation at https://learn.microsoft.com/sql/relational-databases/security/row-level-security:

• SELECT. Cannot view rows that are filtered.

• DELETE. Cannot delete rows that are filtered.

• UPDATE. Cannot update rows that are filtered. It is possible to update rows that will be subsequently filtered. (The next section covers ways to prevent this.)

• INSERT. No effect (inserting is not a read operation). Note, however, that a trigger could cause unexpected side effects in this case.

Block predicates for write operations

These predicates block access to write (or modification) operations that violate the predicate. Block predicates affect all write operations:

• AFTER INSERT. Prevents the insertion of rows with values that violate the predicate. Also applies to bulk insert operations.

• AFTER UPDATE. Prevents the updating of rows to values that violate the predicate. Does not run if no columns in the predicate were changed.

• BEFORE UPDATE. Prevents the updating of rows that currently violate the predicate.

• BEFORE DELETE. Blocks DELETE operations if the row violates the predicate.

Limitations with masking data

Dynamic data masking has some limitations. It does not work on Always Encrypted columns, nor on FILESTREAM or COLUMN_SET column types. Computed columns are also excluded, but if the computed column depends on a masked column, the computed column inherits that mask and returns masked data.

If a column has dependencies, you cannot perform dynamic data masking on it without removing the dependency first, adding the dynamic data mask, and then re-creating the dependency. Finally, a masked column cannot be a used as a FULLTEXT index key.

Vulnerability assessment

You can enable vulnerability assessments on your Azure SQL Database and Azure SQL Managed Instance subscriptions for a monthly fee. This provides a basic monthly overview of your Azure SQL environment and includes steps to mitigate any vulnerabilities it finds.

• For more information about vulnerability assessments and how to subscribe to the service, visit https://learn.microsoft.com/azure/azure-sql/database/sql-vulnerability-assessment.

Azure SQL Advanced Threat Protection (ATP)

The risks of having a publicly accessible database in the cloud are numerous. To help protect against attacks, you can activate SQL Advanced Threat Protection (ATP), which runs 24 hours a day on each of your Azure SQL databases, for a monthly fee. This service notifies you by email whenever it detects atypical behavior.

Some interesting threats include SQL injection attacks, potential vulnerabilities, and unfamiliar database access patterns, including unfamiliar logins or access from unusual locations. Each notification includes possible causes and recommendations to deal with the event. ATP ties into the Azure SQL audit log (discussed later in this chapter); thus, you can review events in a single place and decide whether each one was expected or malicious.

Although this does not prevent malicious attacks (over and above your existing protections), you are given the necessary tools to mitigate and defend against future events. Given how prevalent attacks like SQL injection are, this feature is very useful in letting you know if that type of event has been detected. You can enable ATP using the Azure portal, PowerShell, or the Azure CLI.

• To read an overview on Azure SQL ATP, visit https://learn.microsoft.com/azure/azure-sql/database/threat-detection-overview.

Built-in firewall protection

Azure SQL is secure by default. All connections to your database environment pass through a firewall. No connections to the database are possible until you add a rule to the firewall to allow access.

To provide access to all databases on an Azure SQL Database server, you must add a server-level firewall rule through the Azure portal (or PowerShell or Azure CLI) with your IP address as a range. This does not apply to Azure SQL Managed Instance, because you access the instance through a private IP address inside the VNet.

• To read more about protecting your Azure SQL Database, see Chapter 17, “Provision Azure SQL Database.”

Append-only ledger table

As the name implies, you cannot delete rows from append-only ledger tables. This type of table is useful for security event information and system logs. Because it only allows INSERT operations, an append-only ledger table doesn’t have a history table.

The system-generated names for the GENERATED ALWAYS columns are as follows, and are used for transaction information about when the row version was generated:

• ledger_start_transaction_id (bigint)

• ledger_start_sequence_number (bigint)

Updateable ledger table

Updateable ledger tables work in much the same way as temporal tables, providing a history of changes to the table, and have the same schema as the ledger table. As noted in the “Data storage requirements” section, you can store the history in a different filegroup with its own storage if you choose to define the history table yourself.

The system-generated names for the GENERATED ALWAYS columns are as follows, and are used for transaction information about when the row version was generated or modified.

• ledger_start_transaction_id (bigint)

• ledger_end_transaction_id (bigint)

• ledger_start_sequence_number (bigint)

• ledger_end_sequence_number (bigint)

Although similar in some respects, temporal tables and ledger tables provide different querying features, so you can create an updateable ledger table that is also a temporal table. Take care with your storage requirements if you choose this combination, however, because the history must be retained in two separate history tables.

• For more information about updateable ledger tables, see https://learn.microsoft.com/sql/relational-databases/security/ledger/ledger-updatable-ledger-tables.

The following CREATE TABLE script demonstrates the creation of an updateable ledger table with an anonymous (system-generated) history table. The use of LEDGER = ON is for illustration purposes because each table in a ledger table is updateable by default.

Click here to view code image

CREATE TABLE dbo.Products (
    ProductId INT NOT NULL PRIMARY KEY CLUSTERED
    , ProductName VARCHAR(50) NOT NULL
    , CategoryId INT NOT NULL
    , SalesPrice MONEY NOT NULL
)
    WITH (
    SYSTEM_VERSIONING = ON,
    LEDGER = ON
);

Ledger view

For every table on which you enable the ledger feature, the database creates a ledger view, which shows all the rows that were created and/or modified and the sequence in which these changes took place. You can customize the names of the columns in the ledger view if you want, but if you don’t, these are the system-generated names.

• ledger_transaction_id (bigint). This column displays the ID of the transaction for the creation or modification of a row in the ledger table, and references the ledger_start_transaction_id and ledger_end_transaction_id values.

• ledger_sequence_number (bigint). This column displays the sequence number in which the creation and/or modification took place. This references the ledger_start_sequence_number and ledger_end_sequence_number values in the ledger table.

• ledger_operation_type (tinyint). This column displays 1 for insert operations or 2 for delete operations. Modifying a row in the ledger table generates a delete operation followed by an insert operation.

• ledger_operation_type_desc (nvarchar(128)). This column displays a text value for the ledger_operation_type, which will either be INSERT or DELETE.

The ledger_operation_type and ledger_operation_type_desc columns have limited use in append-only ledger tables, and are there for consistency with updateable ledger table views.

Find the names of system-generated ledger objects

If you don’t choose custom values for the names of the ledger history table and ledger view, you can use the following query to return the names of the system-generated objects:

Click here to view code image

SELECT ts.[name] + '.' + t.[name] AS [ledger_table]
    , hs.[name] + '.' + h.[name] AS [history_table]
    , vs.[name] + '.' + v.[name] AS [ledger_view]
FROM sys.tables AS t
INNER JOIN sys.tables AS h ON h.object_id = t.history_table_id
INNER JOIN sys.views v ON v.object_id = t.ledger_view_id
INNER JOIN sys.schemas ts ON ts.schema_id = t.schema_id
INNER JOIN sys.schemas hs ON hs.schema_id = h.schema_id
INNER JOIN sys.schemas vs ON vs.schema_id = v.schema_id;
GO

Requirements for creating an audit

To keep track of events (called actions), you must define a collection, or audit. The actions you want to track are collected according to an audit specification. Recording those actions is done by the target (destination). The following list explains these terms in more depth:

• Audit. The SQL Server audit object is a collection of server or database actions (which might also be grouped together). Defining an audit creates it in the off state. After it is enabled, the destination receives the data from the audit.

• Server audit specification. This audit object defines the actions to collect at the instance level or database level (for all databases on the instance). You can have multiple server audits per instance.

• Database audit specification. You can monitor audit events and audit action groups. Only one database audit can be created per database per audit. Server-scoped objects must not be monitored in a database audit specification.

• Target. You can send audit results to the Windows Security event log, the Windows Application event log, or an audit file on the file system. You must ensure there is always sufficient space for the target. If you are using the Windows Application event log, keep in mind that the permissions required to read the Windows Application event log are lower than those required to read the Windows Security event log.

An audit specification can be created only if an audit already exists.

• To read more about audit action groups and audit actions, visit https://learn.microsoft.com/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions.

Creating a server audit in SQL Server Management Studio (SSMS)

Verify that you are connected to the correct instance in SSMS. Then, in Object Explorer, expand the Security folder. Next, right-click the Audits folder and select New Audit on the shortcut menu that opens.

The Create Audit dialog box opens. (See Figure 13-2.) You can configure the settings to your requirements or leave the defaults as is. Just be sure to enter a valid file path if you select File in the Audit Destination list box. We also recommend that you choose an appropriate name to enter into the Audit Name box (the default name is based on the current date and time).

Remember to enable the audit after it is created. It will appear in the Audit folder, which is inside the Security folder in Object Explorer. To do so, right-click the newly created audit and select Enable Audit on the shortcut menu.

Create a server audit using T-SQL

The server audit creation process can be quite complex, depending on the destination, file options, audit options, and predicates. As demonstrated in the preceding section, you can use SSMS to configure a new audit and create a script of the settings before selecting OK to produce a T-SQL script. You can also do this manually.

• To read more about creating a server audit in T-SQL, visit https://learn.microsoft.com/sql/t-sql/statements/create-server-audit-transact-sql.

To create a server audit in T-SQL, verify that you are connected to the appropriate instance. Then run the following code sample, changing the audit name and file path as needed. Note that this code also sets the audit state to ON; it is created in the OFF state by default. Also, this audit will not have any effect until an audit specification and target are also created.

Click here to view code image

USE master;
GO
-- Create the server audit.
CREATE SERVER AUDIT Sales_Security_Audit
     TO FILE (FILEPATH = 'C:SalesAudit');
GO
-- Enable the server audit.
ALTER SERVER AUDIT Sales_Security_Audit
    WITH (STATE = ON);
GO

Create a server audit specification in SSMS

In Object Explorer, expand the Security folder. Then, right-click the Server Audit Specification folder and select New Server Audit Specification on the shortcut menu.

In the Create Server Audit Specification dialog box (see Figure 13-3), in the Name box, type a name of your choosing for the audit specification. Then, in the Audit list box, select the previously created server audit. If you type a different value in the Audit box, a new audit will be created by that name. Now you can choose one or more audit actions or audit action groups. Remember to use the context menu to enable the server audit specification after you create it.

Remember to turn on the server audit specification after you create it by using the context menu.

• A full list of audit actions and audit action groups is available at https://learn.microsoft.com/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions.

Create a server audit specification using T-SQL

In much the same way you create the audit itself, you can create a script of the configuration from a dialog box in SSMS or you can do it manually, as shown in the following script. Note that the server audit specification refers to a previously created audit.

Click here to view code image

USE [master];
GO
-- Create the server audit specification.
CREATE SERVER AUDIT SPECIFICATION Server_Audit
FOR SERVER AUDIT Sales_Security_Audit
     ADD (SERVER_OPERATION_GROUP),
     ADD (LOGOUT_GROUP),
     ADD (DATABASE_OPERATION_GROUP)
WITH (STATE = ON);
GO

Creating a database audit specification with SSMS

As you would expect, the location of the database audit specification is under the database security context.

In Object Explorer, expand the database on which you want to perform auditing, and then expand the Security folder. Then right-click the Database Audit Specifications folder and select New Database Audit Specification in the shortcut menu. Remember again to use the context menu to turn it on. Figure 13-4 shows an example of capturing SELECT and INSERT operations on the Sales.CustomerTransactions table by the dbo user.

Creating a database audit specification with T-SQL

Again, verify that you are in the correct database context. Then create the database audit specification by referring to the server audit that was previously created. Next, specify which database actions you want to monitor, as demonstrated in the following code. The destination is already specified in the server audit, so as soon as this is enabled, the destination will begin logging the events as expected.

Click here to view code image

USE WideWorldImporters;
GO
-- Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION Sales_Tables
    FOR SERVER AUDIT Sales_Security_Audit
    ADD (SELECT, INSERT ON Sales.CustomerTransactions BY dbo)
    WITH (STATE = ON);
GO

Viewing an audit log

You can view audit logs in SSMS or in the Security Log in the Windows Event Viewer. This section describes how to do it using SSMS.

In Object Explorer, expand the Security folder, and then expand the Audits folder. Then, right-click the audit log you want to view and select View Audit Logs on the shortcut menu.

Figure 13-5 shows two audit events that have been logged, with the Event Time showing the most recent events first. In the first event (second row), the audit itself has been changed (it was enabled). The second event is a SELECT statement that was run against the table specified in the database audit specification example presented earlier. Note that the Event Time is in UTC format. This is to avoid issues regarding time zones and daylight saving time.

There are many columns in the audit that you cannot see in Figure 13-5. Notable among them are Server Principal ID (SPID), Session Server Principal Name (the logged-in user), and Statement (the command that was run). The point here is that you can capture a wealth of information.

VNet service endpoints

Service endpoints make it possible for you to restrict access to certain Azure services that were traditionally open to the public Internet so they are available only to your Azure VNet. (See Figure 13-7.)

Configurable through the Azure portal, PowerShell, or the Azure CLI, you can block public Internet access to your Azure Storage and Azure SQL Database resources. Additional service endpoints are available, and more will be introduced in the future.

• To read more about VNet service endpoints, visit https://learn.microsoft.com/azure/virtual-network/virtual-network-service-endpoints-overview.

Distributed-denial-of-service protection

Azure provides protection against distributed-denial-of-service (DDoS) attacks for VNets. This is helpful, given that attacks against publicly accessible resources are increasing in number and complexity.

The basic service included in your subscription provides real-time protection using the scale and capacity of the Azure infrastructure to mitigate attacks (see Figure 13-8). For an additional cost, you can take advantage of built-in machine learning algorithms to protect against targeted attacks, with added configuration, alerting, and telemetry.

You can also use the Azure Application Gateway web application firewall to protect against more sophisticated attacks. Combined with Azure SQL Database auditing and NSGs, these features provide a comprehensive suite of protection from the latest threats.

• To read more about Azure DDoS protection, see https://azure.microsoft.com/services/ddos-protection.