Chapter 7
Securing the server and its data

In recent years, security has become incredibly important to organizations of all sorts, in all industries and government entities, as well. All you need to do is to pay attention to the news to see that the number of leaks and hacks of sensitive information is increasing almost daily.

IT organizations around the world—not just in Europe—should consider the implementation of a European privacy law known as the General Data Protection Regulation (GDPR; effective May 25, 2018) as a wake-up call to review how they handle and manage customer information.

Continuing on from Chapter 6, which focused on authorization, this chapter covers features in SQL Server and the underlying operating system (OS) that help you to secure your server and the databases that reside on it.

We begin with what it means to encrypt data. We then move on to understanding how networks transmit and secure data. We conclude with the different features in SQL Server and Microsoft Azure SQL Database that can help you to achieve a secure environment.

Defense in depth means combining different features and strategies to protect your data as much as possible. We show how this strategy can protect your data during regular operations as  well as minimize the fallout should your data be stolen.

At the OS level, the defensive strategies for Windows and Linux are similar. But because entire books already have been written on securing these platforms, this chapter will look at OS security only from a high level and focus mainly on securing your data with SQL Server 2017 and Azure SQL Database.

SQL Server implements a number of security principles through cryptography and other means, which you can use to build up layers of security to protect your environment.

Computer cryptography is implemented through some intense mathematics that use very large prime numbers. However, even if you’re wary of math, you need not be afraid in this chapter: we don’t delve that deeply into it, although we do cover some terminology that might sound scary.

This section explains various security principles and goes into some detail about encryption. It also covers network protocols and how cryptography works. This will aid your understanding of how SQL Server and network security protects your data.

Defense in depth is about building layers of protection around your data and environment.

Perimiter security should include logical and physical segmentation; for example, keeping sensitive servers and applications on a separate part of the network, perhaps off-premises in a separate datacenter or in the Azure cloud. You would then want to protect these connections; for example, by using a Virtual Private Network (VPN).

You should have a firewall and other network defenses to protect against external network attacks. From a physical aspect, don’t let just anyone plug a laptop into an unattended network point, or allow them to connect to your corporate wireless network and have access to the production environment.

From within the network, you need to implement authentication (who you are) and authorization (what you can do), preferably through Active Directory.

On the servers themselves, you should ensure that the file system is locked down, that SQL Server permissions are set correctly, and that file shares (if any) are secured, and using the latest sharing protocols.

On the application side, you can implement coding practices that protect against things like SQL injection attacks, and you can implement encryption in your database (and backup files).

Data that is converted to an unreadable form that can be converted back to its original form using a cryptographic key is said to be encrypted.

Cryptographic algorithms can be defeated in certain ways, the most common being brute-force and dictionary attacks. Let’s take a quick look at each one:

• Brute-force attack. In a brute-force attack, the attacking code checks every possible combination of a password, passphrase, or encryption key against the hashing or encryption service, until it finally arrives at the correct value. Depending on the type of algorithm and the length of the password, passphrase, or key, this can take a few milliseconds, to as long as millions of years (yes, you read that correctly).

• Dictionary attack. A dictionary attack is a lot faster to perform, so a malicious actor would attempt this first. Dictionary attacks take a list of words from a dictionary (which can include common words, passwords, and phrases) and use these against the hashing or encryption service. Dictionary attacks take advantage of the fact that human beings are bad at remembering passwords and tend to use common words.

As computers become more powerful and parallelized, the length of time to run a brute-force attack continues to decrease. Countermeasures do exist to protect against some of these attacks, and some encryption systems cannot be defeated by a brute-force attack. These countermeasures are beyond the scope of this book, but it is safe to say that sufficiently complex algorithms and long encryption keys will take several years to compromise.

This is the recommended method of securing passwords. When a password has been hashed correctly, it cannot be decrypted into its original form. Used with a random salt (a random string applied along with the hash function), this results in passwords that are 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 possibility 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.

Over the years, many ciphers have been created and subsequently defeated (cracked) because those algorithms were considered weak. In many cases, this is because both CPUs and Graphics Processor 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.

A Transmission Control Protocol (TCP) port is one of 65,535 possible connections to a networked device; in this case, the device is a server running Windows or Linux. It is always associated with an IP address and a protocol.

Official and unofficial standards over the years have resulted in a set of commonly used ports. For instance, TCP ports 1433 and 1434 are reserved for SQL Server, whereas TCP ports 80 and 443 are reserved for HTTP and HTTPS, respectively. TCP port 22 is reserved for Secure Shell (SSH), User Datagram Protocol (UDP) port 53 is used for Domain Name Services (DNS), and so on.

The internet is a network of networks (it literally means “between networks”) which transmits data using a suite of protocols, including TCP, which sits on top of Internet Protocol (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 that each individual unit of transfer, also known as a network packet or datagram, contains the data itself—the payload—and a header that indicates where it came from and where it needs to go (the routing information).

IP network packets are delivered using a “best effort” model, meaning that they 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 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 Version 4 of the Internet Protocol (IPv4) has a 32-bit address space, which provides nearly 4.3 billion addresses (2³², or approximately 4.3 x 10⁹). Unfortunately, when this version was first proposed in September 1981, very few people predicted that 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 Internet Protocol, Version 4 Specification, known as Internet Engineering Task Force Request For Comments #791, at https://tools.ietf.org/html/rfc791.

Tricks 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 has run out.

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

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

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

• IPv4. The address is broken up into four subclasses of decimal numbers, each subclass 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 up into eight subclasses of hexadecimal numerals, each subclass being four digits wide, and separated by a colon. If a subclass contains all zeroes, 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 zeroes omitted (note the double-colon at the end to indicate the omission).

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 IPv6 and IPv4 devices, respectively. If that doesn’t sound like enough of a headache, let’s add routing into the mix.

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 thus not secure, because it was designed solely to fix 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. If this happens, proper cryptography can prevent third parties from reading your data.

The web uses the Hypertext Transport 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.

The web grew quickly for two reasons. First, the internet became commercialized after originally being an academic and military project for several decades. The web itself then became wildly popular because of the introduction of the first graphical web browser, NCSA Mosaic, in the 1990s. The spiritual successors to Mosaic were Netscape Navigator and Microsoft Internet Explorer, during a period of internet history known as the “browser wars.”

You can learn more about the commercial beginnings of the web and the so-called “Internet Era,” by listening to the Internet History Podcast, available at http://www.internethistorypodcast.com.

Modern web browsers include Microsoft Edge, Google Chrome, Mozilla Firefox, and Apple Safari.

Remember that the internet is built on the Internet Protocol, which is stateless and has routing information in the header of every single packet. This means that 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 we discussed earlier, encryption transforms 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 has the key to decrypt it.

A secure version of HTTP was created by Netscape Communications in 1994, which was dubbed HTTPS (HTTP Secure, or HTTP over Secure Sockets Layer [SSL]). Over the years, the moniker of HTTPS has remained, but it has come to be known as HTTP over Transport Layer Security (TLS) as standards improved.

When we talk about data moving over the network, that usually means TCP/IP is involved, and we need to transmit that data securely.

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

The advantage is that the private key never needs to be shared, which makes this method far more secure because only you can use your private key to decrypt the data. Unfortunately, asymmetric encryption does require a lot more processing power, plus both parties need their own key–pairs.

For small-scale communications between two private entities, this might be done by sharing their public keys between each other.

For larger-scale communications with many senders and one recipient (such as a web or database server, for example), a certificate authority can provide the public key through a digital certificate, which the recipient (the website or database administrator) can install on the server directly.

This certificate serves as an electronic signature for the recipient, which includes its public key. The authority, known as a Certification Authority, is trusted by both the sender and the recipient, and the sender can verify that the recipient is indeed who it claims to be.

Digital certificates, also known as Public Key Certificates, are defined by the X.509 standard. Many protocols use this standard, including TLS and its predecessor, SSL.

You can read more about how digital certificates and TLS relate to SQL Server and Azure SQL Database later in this chapter.

The certificate is automatically revoked after it expires, and the CA can revoke any certificate before then.

For the certificate to be trusted, the CA itself must be trustworthy. It is the responsibility of the CA to verify the owner’s identity so that any certificates issued in that owner’s name can be trusted.

In recent months, several CAs have lost their trustworthy status, either because their verification process was flawed or their signing algorithms were weak. Take care when choosing a CA for your digital certificates.

Figure 7-1 shows this hierarchy.

In other words, SQL Server and other applications can make use of this data protection API to have Windows automatically encrypt data on the drive without having to encrypt data through other means.

SQL Server Enterprise edition uses the Data Protection API (DPAPI) for Transparent Data Encryption (TDE).

Each layer in the hierarchy can be accessed by a password at the very least, unless an Extensible Key Management (EKM) module is being used. The EKM module is a standalone device that holds symmetric and asymmetric keys outside of SQL Server.

The Database Master Key (DMK) is protected by the Service Master Key (SMK), and both of these are symmetric keys. The SMK is created when you install SQL Server and is protected by the DPAPI.

If you want to use TDE on your database (see the section “Configuring TDE on a user database” later in this chapter), it requires a symmetric key called the Database Encryption Key (DEK), which is protected by an asymmetric key in the EKM module or by a certificate through the DMK.

This layered approach helps to protect the data from falling into the wrong hands.

There are two considerations when deciding how to secure a SQL Server environment, which you can implement independently.

• Data at rest. In the case of TDE, this is decrypting the data on a drive as it is read into the buffer pool, and encrypting the data as it is flushed to a drive from the buffer pool. (You could also encrypt your storage layer independently from SQL Server, but this does not form part of the encryption hierarchy.)

• Data in motion. Protecting the data during transmission over a network connection. Any network protocols and APIs involved must support encrypting and decrypting the data as it moves in and out of the buffer pool.

Data is in motion from the moment it is read from or written to the buffer pool in SQL Server or Azure SQL Database. Between the buffer pool and the underlying storage, data is considered to be at rest.

SQL Server can make use of these keys for internal use. The HSM/EKM device can be a hardware appliance, a USB device, a smart card, or even software, as long as it implements the Microsoft Cryptographic Application Programming Interface (MCAPI) provider.

EKM is an advanced SQL Server setting and is turned off by default. To use the key or keys from an HSM/EKM device, you need to turn on EKM by using the sp_execute 'EKM provider enabled' command with the appropriate parameter. Then, the device must be registered as an EKM module for use by SQL Server.

After the HSM/EKM device creates a key for use by SQL Server (for TDE, for instance), the device exports it securely into SQL Server via the MCAPI provider.

The module might support different types of authentication (Basic or Other), but only one of these types can be registered with SQL Server for that provider.

If the module supports Basic authentication (a user name and password combination), SQL Server uses a credential to provide transparent authentication to the module.

To read more about EKM in SQL Server, go to https://docs.microsoft.com/sql/relational-databases/security/encryption/extensible-key-management-ekm.

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 make use of Key Vault, you must create the vault, along with a valid Azure Active Directory (Azure AD) first.

Begin by registering the SQL Server service principal name in Azure AD. After the service principal name is registered, you can install the SQL Server Connector and turn on EKM in SQL Server.

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

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

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

Note, however, that when you upgrade from an older version of SQL Server—those that were encrypted using 3DES—you must regenerate both the SMK and DMK to upgrade them to AES.

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 that maintenance window.

To back up the SMK, you can use the T-SQL script shown that follows, 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 that they cannot be used together if your secure backup location is compromised. Ensure that the folder on the drive is adequately secured. 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 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 make use of TDE.

It is considered a security best practice to regenerate the DMK periodically to protect the server from brute-force attacks. The idea is that it will take 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 that you encrypt your database with a DMK in January of this year. In July, you regenerate the DMK, which will cause all keys for digital certificates to be reencrypted with the new key. If anyone had 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 by using the T-SQL script that follows. 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). This script assumes that 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 on Microsoft Docs at https://docs.microsoft.com/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine.

There are third-party providers, including storage vendors, that provide excellent on-disk encryption for your Direct-Attached Storage (DAS) or Storage-Area Network (SAN), as a file system solution or at the physical storage layer. Provided that your data and backups are localized to this particular solution, and no files are copied to machines that are not encrypted at the file-system level, this might be an acceptable solution for you.

However, if you have the Enterprise edition of SQL Server, you can use TDE, which encrypts the data, transaction log, and backup files at the file-system level by using a DEK.

If someone manages to acquire these files via a backup server, Azure Blob Storage archive, or by gaining access to your production environment, that person will not be able to simply attach the files or restore the database without the DEK.

The DEK is a symmetric key (shared secret) that is secured by a certificate stored in the master database. If using HSM/EKM or Key Vault, the DEK is protected by an asymmetric key in the EKM module, instead. The DEK is stored in the boot record of the protected database (page 0 of file 1) so that it is easily available during the recovery process.

In the data file, TDE operates at the page level, because all data files are stored as 8-KB pages. Before being flushed from the buffer pool, the contents of the page are encrypted, the checksum is calculated, and then the page is written to the drive. When reading data, the 8-KB page is read from the drive, decrypted, and then the contents are placed into the buffer pool.

For log files, the contents of the log cache are also encrypted before writing to and reading from the drive.

To read more about checkpoint operations and active virtual log files (VLFs) in the transaction log, refer to Chapter 3.

Backup files are simply the contents of the data file, plus enough transaction log records to ensure that the database restore is consistent (redo and undo records of active transactions when the backup is taken). In other words, the contents of new backup files are encrypted by default after TDE is turned on.

Verify that it is safely backed up and securely stored off-premises. If you have never backed up the DMK, you will be warned by the Database Engine after using it that it 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 will 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.

Although you do have a choice of algorithm, we recommend AES over 3DES for performance and security reasons, and you have a choice of three AES key sizes: 128, 192, or 256 bits. Remember that larger keys are more secure but will add additional 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) should be able to attack a 128-bit key in the months between key rotations.

After you create the DEK, you turn on encryption on the database. The command completes immediately, but the process will take place in the background because each page in the database will need to be read into the buffer pool, encrypted, and flushed to the drive.

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 will be set to 1. TempDB will also show a value of 1 in this column.

The first rule of storing sensitive data is that you should avoid storing it altogether when possible. Credit card information makes sense in a banking system, but not in a sales database, for instance.

If you must store sensitive data, Always Encrypted protects how data is viewed at the column level. It works with applications that use particular connection types (client drivers; see the next section) to interact with SQL Server. These client drivers are protected by a digital certificate so that only specific applications can view the protected data.

Always Encrypted was introduced in SQL Server 2016 and has been available on all editions since SQL Server 2016 Service Pack 1. To use this feature, the database makes use of two types of keys: column encryption keys and column master keys (dicussed shortly).

The encryption used by Always Encrypted is one of two types:

• Deterministic encryption. This is the same as generating a hash value without a salt. The same encrypted value will always be generated for a given plain-text value. This is useful for joins, indexes, searching, and grouping, but it makes it possible for people to guess what the hash values represent.

• Randomized encryption. This is the same as generating a hash value with a salt. No two of the same plain-text values will generate the same encrypted value. Although this does improve security of the data, it does not permit joins, indexes, searching, and grouping for those encrypted columns.

For values that are not expected to participate in joins or searches, you can safely use randomized encryption. Choose deterministic encryption for values like social security numbers and other government-issued values because it helps for searching and grouping.

Because the whole intention of Always Encrypted is to prevent unauthorized persons from viewing data (including database administrators), you should generate the keys elsewhere and store them in a trusted key store (in the the operating system’s key store for the database server and the application server, or an EKM module such as Key Vault), away from the database server. The person who generates the keys should not be the same person who is administering the database.

• .NET Framework 4.6 or higher

• Microsoft JDBC Driver 6.0 or higher

• ODBC Driver 13.1 for SQL Server or higher

It is anticipated that .NET Standard will be supported in the near future.

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

• .NET Framework. Set the Column Encryption Setting in the connection string to enabled, or configure the SqlConnectionStringBuilder.ColumnEncryptionSetting property to SqlConnectionColumnEncryptionSetting.Enabled.

• JDBC. Set the 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, use the SQL_COPT_SS_COLUMN_ENCRYPTION preconnection attribute, or through the Data Source Name (DSN) using the SQL_COLUMN_ENCRYPTION_ENABLE setting.

Additionally, the application must have the VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION database permissions in order to view the Column Master Key and Column Encryption Key.

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.

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, go to https://docs.microsoft.com/sql/relational-databases/security/encryption/overview-of-key-management-for-always-encrypted.

• VIEW ANY COLUMN MASTER KEY DEFINITION

• VIEW ANY COLUMN ENCRYPTION KEY

If you plan on creating new keys, you also need the following permissions:

• ALTER ANY COLUMN MASTER KEY

• ALTER ANY COLUMN ENCRYPTION KEY

In SQL Server Management Studio, in Object Explorer, right-click the name of the database that you want to configure. In the Always Encrypted Wizard, in the pane on the left, click Tasks, and then, on the Tasks page, click Encrypt Columns.

On the Column Selection page, choose the a column in a table that 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 here.

On the Master Key Configuration page, you can create a new key by using the local OS certificate store or by using a centralized store like Key Vault or an HSM/EKM device. If you already have a CMK in your database, you can use it, instead.

You can read more about Always Encrypted on Microsoft Docs at https://docs.microsoft.com/sql/relational-databases/security/encryption/always-encrypted-database-engine.

This section first goes into more detail about network encryption with TLS, which operates on the network itself, and then dives into row-level security and data masking. The latter features do not make use of encryption, but form part of your defense-in-depth strategy to protect data in motion from prying eyes.

Unlike Always Encrypted, which encrypts data at rest and only decrypts it when being read, row-level security and data masking hide or show data depending on who’s asking for it and how it is queried.

So, what is TLS, and how does it affect SQL Server and Azure SQL Database? The name is revealing. TLS is a security layer on top of a transport layer, or in technical terms, a cryptographic protocol. As we pointed out at the beginning of this chapter, most networks use the TCP/IP protocol stack. In other words, TLS is designed to secure the traffic on TCP/IP-based networks.

After the key exchange is done, 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.

• 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 latest public version. It is vulnerable to certain attacks, like its predecessors, but as long as older encryption algorithms are not used (for instance 3DES, RC4, and IDEA), it is good enough for the moment.

Where possible, you should be using TLS 1.2 everywhere. SQL Server ships with TLS 1.0, 1.1, and 1.2 support out of the box, so you will need to turn off 1.0 and 1.1 at the OS level to ensure that you use TLS 1.2.

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

As of this writing, TLS 1.3 is a draft specification.

Row-level security performs at the database level to restrict access through a security policy, based on group membership or execution context. It is functionally equivalent to a WHERE clause.

Access to the rows in a table is protected by an inline table-valued function, which is invoked and enforced by the security policy.

The function checks whether the user is allowed to access a particular row, while the security policy attaches this function to the table. So, when you run a query against a table, the security policy applies the predicate function.

There are two types of security policies supported by row-level security, both of which you can apply simultaneously:

• Filter predicates, which limit the data that can be seen

• Block predicates, which limits the actions a user can take on data

Hence, a user might be able to see rows, but cannot insert, update, or delete rows that look like rows they can see. This concept is covered in more detail in the next section.

Filter predicates affect all read operations (this list is taken directly from the official documentation at https://docs.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.

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

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

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

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

The mask is defined in the column definition of the table, using MASKED WITH (FUNCTION = [type]) syntax (and you can add masking after table creation by using ALTER COLUMN syntax).

There are four types of masks that are available:

• Default. The column is masked according to the data type (not its default value). Strings will use “XXXX” (fewer if the length is less than four characters); numerics will use a zero value; dates will use midnight on January 1st, 1900; and binary will use a single byte binary equivalent of zero.

• Email. Only the first letter and the trailing domain suffix is not masked; for example, “[email protected]”.

• Random. This replaces a numeric data type with a random value between a range you specify.

• Custom String. Only the first and last letters are not masked. There is a custom padding string in the middle, which you specify.

You can read more about dynamic data masking, including samples of how to set it up, at https://docs.microsoft.com/sql/relational-databases/security/dynamic-data-masking.

That’s great if you’re just comparing SQL Server to Azure SQL Database, but there are some features unique to Azure SQL Database that are worth looking at, which we’ll do in the next section. But keep in mind that because Azure features and products are always changing, this is only a brief overview.

Some of the interesting threats include SQL injection attacks and potential vulnerabilities as well as unfamiliar database access patterns, including unfamiliar logins or access from unusual locations. Each notification includes possible causes and recommendations to deal with the event.

Threat Detection ties into the Azure SQL Audit log (discussed in the next section); 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 turn on Threat Detection through the Azure portal, or through PowerShell.

To read more on configuring Azure SQL Database Threat Detection with PowerShell, go to https://docs.microsoft.com/azure/sql-database/scripts/sql-database-auditing-and-threat-detection-powershell.

To provide access to all databases on an Azure SQL server, you must add a server-level firewall rule through the Azure portal or through PowerShell with your IP address as a range.

To read more about protecting your Azure SQL Database, see Chapter 5.

Since SQL Server 2016 Service Pack 1, the Audit feature is available in all editions, as well as in Azure SQL Database. Chapter 5 covers configuring auditing in Azure SQL Database in depth.

You can read more about extended events in Chapter 13.

Audits are logged to event logs or audit files. An event is initiated and logged every time the audit action is encountered, but for performance reasons, the audit target is written to asynchronously.

The permissions required for SQL Server auditing are complex and varied, owing to the different requirements for reading from and writing to the Windows Event Log, the file system, and SQL Server itself.

• Audit. The SQL Server audit object is a collection of server actions or database actions (these actions might also be grouped together). Defining an audit creates it in the off state. After it is turned on, 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 that there is always sufficient space for the target. Keep in mind that the permissions required to read the Windows Application event log are lower than the Windows Security event log, if using the Windows Application event log.

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

To read more about audit action groups and audit actions, go to https://docs.microsoft.com/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions.

In the Create Audit dialog box that opens, configure the settings to your requirements, or you can leave the defaults as is. Just be sure to enter in 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 turn on the audit after it is created. It will appear in the Audit folder, which is within the Security folder in Object Explorer. To do so, right-click the newly created audit, and then, on the shortcut menu, click Enable Audit.

To read more about creating a server audit in T-SQL visit https://docs.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, and then run the code in Listing 7-4. (You’ll need to change the audit name and file path accordingly.) Note that the next example also sets the audit state to ON. It is created in the OFF state by default.

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

In the Create Server Audit Specification dialog box (Figure 7-3), in the Name box, type a name of your choosing for the audit specification. 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.

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

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

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

In Object Explorer, expand the database on which you want to perform auditing, and then expand the Security folder. Right-click the Database Audit Specifications folder, and then, on the shortcut menu, click New Database Audit Specification. Remember again to use the context menu to turn it on.

Figure 7-4 shows an example of capturing SELECT and INSERT operations on the Sales.CustomerTransactions table by the dbo user.

The destination is already specified in the server audit, so as soon as this is turned on, 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

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

Note that the Event Time is in UTC format. This is to avoid issues regarding time zones and daylight savings.

Figure 7-5 shows two audit events that have been logged. In the first, the audit itself has been changed (it was turned on). The second event is a SELECT statement that was run against the table specified in the database audit specification example presented earlier.

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

This helps you to remain compliant with auditing regulations as well as see anomalies (as discussed earlier in the section “Azure SQL Database Threat Detection”) to give you greater insight into your Azure SQL Database environment.

Auditing gives you the ability to retain an audit trail, report on activity in each database, and analyze reports, which includes trend analysis and security-related events. You can define server-level and database-level policies. Server policies automatically cover new and existing databases.

If you turn on server auditing, that policy applies to any databases on the server. Thus, if you also turn on database auditing for a particular database, that database will be audited by both policies. You should avoid this unless retention periods are different or you want to audit for different event types.

You can read more about Azure SQL Database auditing in Chapter 5.

With Azure IaaS, setting up a VM in a resource group is secure by default. If you want to allow connections from outside of your Azure virtual network, you need to allow not only the connection through the OS firewall (which is on by default in Windows Server), but you also can control connections through a Network Security Group.

In addition to that, you can control access through a network appliance, such as a firewall or NAT device. This provides finer-grained control over the flow of network traffic in your virtual network, which is needed to set up Azure ExpressRoute, for example (Chapter 3 covers this in some detail).

In the Azure classic deployment model, an NSG would provide security for an individual virtual machine. With the Azure Resource Manager deployment model, an NSG can provide security for an entire subnet, which affects all the resources in that subnet (see Figure 7-6). If you require more control, you can associate the NSG with an individual network interface card (NIC), thus restricting traffic further.

As with typical firewalls, the NSG has rules for incoming and outgoing traffic. When a packet hits a port on the virtual network or subnet, the NSG intercepts the packet and checks whether it matches one of the rules. If the packet does not qualify for processing, it is discarded (dropped).

Rules are classified according to source address (or range) and destination address (or range). Depending on the direction of traffic, the source address could refer to inside the network or outside on the public internet.

This becomes cumbersome with more complex networks, so to simplify administration and provide flexibility, you can use service tags to define rules by service name instead of IP address. Storage, SQL and Traffic are currently supported, with more to come in the future.

You can also use default categories, namely VirtualNetwork (the IP range of all addresses in the network), AzureLoadBalancer (the Azure infrastructure load balancer), and Internet (IP addresses outside the range of the Azure Virtual Network).

You can read more about Azure Virtual Network security and service tags at https://docs.microsoft.com/azure/virtual-network/security-overview.

You can read more about Virtual Private Networks in Chapters 2 and 3.

In a traditional network, communication across subnets like this requires a gateway to control (route) the traffic. Azure provides these system routes for you automatically.

You might decide that this free-for-all communication is against your network policy and that all traffic from your VMs should first be channeled through a network appliance (such as a firewall or NAT device). Virtual appliances are available in the Azure Marketplace at an additional cost, or you could configure a VM yourself to run as a firewall.

A user-defined route with IP forwarding makes this happen. With a user-defined route, you create a subnet for the virtual appliance and force traffic from your existing subnets or VMs through the virtual appliance.

In Microsoft’s own words:

“[t]o allow a VM to receive traffic addressed to other destinations, you must enable IP Forwarding for the VM. This is an Azure setting, not a setting in the guest operating system.” (https://docs.microsoft.com/azure/virtual-network/virtual-networks-udr-overview)

Until you create a routing table (by user-defined route), subnets in your Virtual Network rely on system routes. A user-defined route adds another entry in the routing table, so a technique called Longest Prefix Match (LPM) kicks in to decide which is the better route to take, by selecting the most specific route (the one with the longest prefix). As seen earlier in Figure 7-6, a /24 prefix is longer than a /16 prefix, and a route entry with a higher prefix takes precedence.

If two entries have the same LPM match, the order of precedence is as follows:

• User-defined route

• BGP route

• System route

Remember BGP? It’s used for ExpressRoute. As we mentioned in Chapter 3, ExpressRoute is a VPN service by which you can connect your Azure Virtual Network to your on-premises network, without going over the public internet. You can specify BGP routes to direct traffic between your network and the Azure Virtual Network.

Configurable through the Azure portal (or PowerShell), you can block public internet access to your Azure Storage and Azure SQL Database accounts. Additional service endpoints will be introduced in the future.

To read more about Virtual Network service endpoints, go to https://docs.microsoft.com/azure/virtual-network/virtual-network-service-endpoints-overview.

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 also can use the Azure Application Gateway web application firewall to help protect against more sophisticated attacks.

Combined with Azure SQL Database auditing and NSGs, these features provide a comprehensive suite of protection against the latest threats.

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