Always Encrypted is a feature introduced in SQL Server 2016 and Azure SQL Database, designed to protect sensitive data at rest and in motion. It is a security technology that protects SQL Server data in a database, on the wire, and even while it’s being processed, making it an excellent tool for safeguarding sensitive data. For those studying for the DP-300 Administering Microsoft Azure SQL Solutions exam, understanding Always Encrypted technology is vital.
What is Always Encrypted?
Always Encrypted is a feature that guarantees data security by ensuring that SQL Server never sees the unencrypted version of the protected data. It uses a pair of keys for encryption – one public (used to encrypt data) and one private (used to decrypt data).
The SQL Server only handles the encrypted data and the encryption keys are kept safe in a trusted client application. By ensuring that the SQL Server cannot access the encryption keys, even DBAs, administrators, or attackers have no access to the raw data, only its encrypted form.
Components of Always Encrypted
Always Encrypted comprises two types of keys: Column Master Keys (CMK) and Column Encryption Keys (CEK).
- The CMK is a key-protecting key used to encrypt one or more CEKs. It is stored in an external trusted key store, such as Azure Key Vault or Windows Certificate Store.
- The CEK is used to encrypt the data in the database columns. It is stored in the database in an encrypted form.
Steps to Implement Always Encrypted
- Set Up a Column Master Key:
- Set Up a Column Encryption Key:
- Encrypt your database columns:
The first step in implementing Always Encrypted is to create a Column Master Key. In an Azure SQL Database, this can be done as follows:
CREATE COLUMN MASTER KEY MyCMK
WITH (
KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',
KEY_PATH = 'https://myvault.vault.azure.net:443/keys/MyKey/bf33054d4bf1465bbf7eb1e1b84b2cfe'
) ;
This encrypted key is stored in your database. Here’s an example of how to create a CEK:
CREATE COLUMN ENCRYPTION KEY MyCEK
WITH VALUES
(
COLUMN_MASTER_KEY = MyCMK
, ALGORITHM = 'RSA_OAEP'
, ENCRYPTED_VALUE = 0x01700000016C00....
);
Once the keys are set up, you can encrypt the database columns:
ALTER TABLE [dbo].[MyTable] ALTER COLUMN [MyColumn] ADD
ENCRYPTED WITH (
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = MyCEK
) NULL;
The encryption type can be DETERMINISTIC or RANDOMIZED, depending on what level of security versus functionality is required.
Use of Always Encrypted with client drivers
To use Always Encrypted, client drivers must be able to interface with the key store and handle encryption and decryption of data. Microsoft provides several such drivers which can be used with Always Encrypted:
- .NET Framework Data Provider for SQL Server
- JDBC Driver for SQL Server
- ODBC Driver for SQL Server
- ADO.NET Core
It’s important to note that enabling Always Encrypted alters the output of queries, potentially breaking applications that are not expecting encrypted columns to be returned. Therefore, client applications need to be aware of which columns are encrypted.
For those preparing for the DP-300 Administering Microsoft Azure SQL Solutions exam, understanding and implementing Always Encrypted is a paramount skill. It helps DBAs secure sensitive data, ensuring it remains encrypted at rest and in transit, minimizing the surface area susceptible to attacks.
Practice Test
True or False? Always Encrypted allows SQL Server to perform operations on encrypted data.
- True
- False
Answer: False
Explanation: Always Encrypted is a feature designed to assure that SQL Server never sees unencrypted data. The concern is the client, never the server.
What does Always Encrypted in Azure SQL Database ensure?
- A. Encryption of data at rest only
- B. Encryption of data in transit only
- C. Encryption of data at rest and in transit
- D. None of the Above
Answer: C. Encryption of data at rest and in transit
Explanation: Always Encrypted is a feature that secures your data at rest and during transmission to cloud components.
True or False? Always Encrypted supports two types of encryption: deterministic and randomized.
- True
- False
Answer: True
Explanation: Always Encrypted supports two types of encryption- deterministic encryption and randomized encryption.
In an Azure SQL Database, what does deterministic encryption do?
- A. Guarantees uniqueness of encrypted values
- B. Guarantees randomness of encrypted values
- C. Allows SQL Server to perform operations on encrypted data
- D. None of the Above
Answer: A. Guarantees uniqueness of encrypted values
Explanation: Deterministic encryption always generates the same encrypted value for any given clear text value.
True or False? By using Always Encrypted, you can help prevent the instances of database and help prevent theft of sensitive data.
- True
- False
Answer: True
Explanation: Always Encrypted secures customer data, so even if the data is leaked it remains secure and readable only if the client has the encryption key.
Which of the following tasks can be performed on Always Encrypted columns without decrypting the database encryption key?
- A. Equality comparison
- B. LIKE pattern matching
- C. Arithmetic operations
- D. All of the Above
Answer: A. Equality comparison
Explanation: You can perform equality comparisons on columns that are encrypted using deterministic encryption, others are not supported without decryption.
True or False? Always Encrypted does not support offset functions.
- True
- False
Answer: True
Explanation: Always Encrypted currently does not support offset functions as it requires SQL Server to process the data, which is not allowed in Always Encrypted.
True or False? Always Encrypted keys are managed at the server-side.
- True
- False
Answer: False
Explanation: The management of the keys is done on the client-side, not the server-side to ensure data security.
What are the prerequisites for configuring Always Encrypted?
- A. SQL Server Management Studio
- B. PowerShell
- C. Microsoft .NET Framework 6 or later
- D. All of the above
Answer: D. All of the above
Explanation: All options: SQL Server Management Studio, PowerShell and Microsoft .NET Framework 6 or later are prerequisites for configuring Always Encrypted.
True or False? Always Encrypted allows Full-Text indexing.
- True
- False
Answer: False
Explanation: Full-text indexes are not supported on columns that have deterministic encryption.
Interview Questions
What is Always Encrypted in Microsoft Azure SQL?
Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers, stored in Azure SQL Database or SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine.
Can you name two types of encryption supported by Always Encrypted?
Always Encrypted supports two types of encryption – Deterministic encryption and Randomized encryption.
What is deterministic encryption?
Deterministic encryption generates the same ciphertext for a given plaintext value and encryption key every time. These are more appropriate for equality-based comparisons like joins and group by operations.
What is randomized encryption?
Randomized encryption generates a different ciphertext for the same plaintext value and encryption key. It is less perceptible to pattern-matching attacks but can’t be used for equality-based operations.
Which key does Always Encrypted use for encrypting the data?
Always Encrypted uses two types of key: Column Encryption Key (CEK) for encrypting the data and Column Master Key (CMK) for securing the CEK.
Where are the encryption keys stored in Always Encrypted?
The Column Encryption Key (CEK) is stored in the database’s system catalog views, while the Column Master Key (CMK) is stored in a trusted key store which can be Azure Key Vault or Windows Certificate Store.
What ensures that the SQL Server can’t access the plaintext data in Always Encrypted?
SQL Server can’t access the plaintext data as it doesn’t have access to the Column Encryption Key. The CEK is encrypted with the Column Master Key, which is not stored in the database.
Can a SQL Server administer unencrypt the data without access to encryption keys?
No, a SQL Server administer cannot unencrypt the data without having access to the actual encryption keys.
How does Always Encrypted affect query performance?
Because Always Encrypted uses encryption inside the client application, not in the database, the performance impact on the database server is negligible. However, it may slightly increase the workload of the client application.
Can Always Encrypted be used with in-memory OLTP?
No, Always Encrypted is not compatible with In-Memory OLTP.
What is parameterization for Always Encrypted?
Parameterization for Always Encrypted is a feature in SQL Server Management Studio (SSMS) that transparently converts literal values into parameter values in Transact-SQL queries to support encrypted columns.
Can you run range queries or use LIKE with an Always Encrypted feature?
No, range queries and LIKE predicates are not supported with the Always Encrypted feature.
Can third-party applications access data encrypted by Always Encrypted?
Yes, as long as they use a driver that is compatible with Always Encrypted, such as ADO.NET and SqlClient.
How can existing data be encrypted using Always Encrypted?
Existing data can be encrypted using the Always Encrypted Wizard in SQL Server Management Studio or PowerShell cmdlets.
Does Always Encrypted support compression enabled tables and indexes?
No, Always Encrypted does not support tables and indexes with data compression.
extutorials.com