From the course: Microsoft Azure Database Administrator Associate (DP-300) Cert Prep by Microsoft Press

Implement Always Encrypted

From the course: Microsoft Azure Database Administrator Associate (DP-300) Cert Prep by Microsoft Press

Start my 1-month free trial Buy for my team

Implement Always Encrypted

“

- Always Encrypted is another data-at-rest encryption solution. In an earlier video in this lesson, we discussed transparent data encryption or TDE, which also encrypts data at rest. But the difference is that TDE encrypts the data files while Always Encrypted encrypts the data itself at the column level. So let's dig into Always Encrypted. Always Encrypted is a security feature in SQL server designed to protect and safeguard sensitive data and sensitive information at the column level, such as credit card information, credit card numbers, or security numbers. It enables clients to encrypt sensitive data within client applications without exposing the encryption keys. This provides a separation between those who own the data and those who can view the data and those who manage the data but shouldn't view it. It also allows customers securely store their sensitive data in the cloud, reducing the risk of data theft by malicious insiders. And there's a few restrictions that we should cover before we get into implementing Always Encrypted. The first is there's no computations on encrypted columns, no computations across both plain text and encrypted columns. And some clauses are not supported such as for XML and XML data types. Okay, so let's start walking through how to implement Always Encrypted. So implementing Always Encrypted is actually fairly easy because there's a nice wizard that walks you through everything. In SQL Server Management Studio, right click on the table and select encrypted columns. This will open up the column selection page to let you select the columns that you want to encrypt. Once you've done that, select the master key and you can either auto generate a master key or select one that you already have from the Azure Key Vault. And then simply click Go. So here's a before and after. So here's my data before Always Encrypted is enabled, and here it is after. Now some configuration details. When configuring Always Encrypted, there are a couple of decisions that you need to make. The first is the encryption type that you want to use. There are several, and you saw it on the one of the previous screens. There is deterministic and randomized. Deterministic says this is less secure than randomized because the same value is always encrypted into the same string. Randomized is a more secure than deterministic because the same value is never encrypted the same way. And you can set this per column and the master key. Always Encrypted uses two types of keys, a column encryption key that encrypts the data within the selected column and a column master key that encrypts one or more column encryption keys. This step generates the appropriate keys and lets you decide where to store the master key. So you can either store it in the Windows Cert Store, or the Azure Key Vault. And you saw this in the previous slide. When you are generating the master key, where do you want to store that key? In the Windows Certificate Store or the Azure Key Vault?

Contents