‘Always Encrypted’ is a feature in SQL Server and Azure SQL Database that protects sensitive data by allowing SQL Server to process SQL queries over encrypted columns while keeping the encryption keys away from the database system. This ensures that data is encrypted at rest in the database, and in use, creating a separation between those who own the data and the administrators who manage it.
In this context, the challenge within the Microsoft DP-420 Designing and Implementing Microsoft Azure Cosmos DB exam will be to implement this security feature to ensure the protection of sensitive data within your Native Applications that uses Azure Cosmos DB.
Understanding Always Encrypted
The feature ‘Always Encrypted’ offers two types of encryption:
- Deterministic Encryption: This ensures that any value in the database is always encrypted to the same encrypted value. Hence, it allows point lookups, equality joins, grouping and indexing on encrypted columns.
- Randomized Encryption: This encrypts the data in a less predictable manner. Thus, it is more secure but does not allow point lookups, equality joins, grouping or indexing on encrypted columns.
Remember that by employing this feature, Azure Cosmos DB becomes blind to the data, and cannot perform operations like pattern matching, range queries or any function.
Implementing Always Encrypted
To implement Always Encrypted in Azure Cosmos DB, the steps can be simplified as below:
- Enable Always Encrypted on the desired database columns.
- Configure the Application Intent to encrypt parameters targeting encrypted columns and decrypt the results retrieved from these encrypted columns.
The following Python code, for example, can be used to connect to an Azure SQL Database using an encrypted connection string.
# import necessary libraries
import pyodbc
# Database server details
server = '
database = '
username = '
password = '
# define connection string
cnxn_str = ('DRIVER={ODBC Driver 17 for SQL Server};'
'SERVER=' + server + ';'
'DATABASE=' + database + ';'
'UID=' + username + ';'
'PWD=' + password + ';'
'ColumnEncryption=Enabled;'
'KeyStoreAuthentication=KeyVaultClientSecret;'
'KeyStorePrincipalId=
'KeyStoreSecret=
# establish connection
cnxn = pyodbc.connect(cnxn_str)
Primarily, Microsoft uses Azure Key Vault for storing the Column Master Key (CMK). Here, the `KeyVaultClientSecret` is essentially your Active Directory Application Password, while `KeyStorePrincipalId` is the Application ID.
It is essential to remember that once enabled, columns are encrypted and decrypted inside client applications, not in the database system, enhancing data privacy and protection.
Performance Considerations
Although Always Encrypted is a robust security feature, it might affect the performance due to the overhead of encryption and decryption. Hence, it is recommended to analyze and identify the distinct columns that require encryption. Also, regular monitoring and assessments would help in ensuring optimal performance.
In a nutshell, implementing Always Encrypted can significantly upsurge the security posture of your Azure Cosmos DB by providing robust encryption of sensitive data within your Native Applications. Thus, while preparing for your DP-420 Designing and Implementing Microsoft Azure Cosmos DB exam, concentrate on understanding its underlying intricacies and best practices.
Practice Test
True or False: “Always Encrypted” allows you to encrypt sensitive data, such as social security numbers, that is stored in an Azure SQL database.
- True
- False
Answer: True.
Explanation: Always Encrypted is used to secure sensitive data such as credit card numbers or social security numbers by encrypting them in SQL Server.
Which of the following steps are involved in implementing Always Encrypted?
- A. Generating a column master key
- B. Generating a column encryption key
- C. Configuring the Always Encrypted feature for your database
- D. Writing a SQL query to encrypt data
Answer: A, B, C.
Explanation: The process of Always Encrypted includes generating keys (column master key and column encryption key) and configuring the feature on your database. Writing a SQL query to encrypt data isn’t required as encryption is handled by the feature itself.
True or False: “Always Encrypted” does not provide encryption at rest in Azure Cosmos DB.
- True
- False
Answer: True.
Explanation: Cosmos DB offers its own encryption at rest functionality. Always Encrypted is specific to SQL Server databases and not for Cosmos DB.
Always Encrypted feature is by default enabled in Azure Cosmos DB?
- A. Yes
- B. No
Answer: B. No.
Explanation: Azure Cosmos DB by default does not come with Always Encrypted feature. However, it has its own mechanisms for encryption and securing data.
True or False: with Always Encrypted, the data remains encrypted not only at rest but also during network transit.
- True
- False
Answer: True.
Explanation: The Always Encrypted feature ensures that data is encrypted not just when it is stored, but also while it is being transmitted between the server and client.
What kind of keys are used in Always Encrypted?
- A. Symmetric keys
- B. Asymmetric keys
- C. Both A and B
- D. Neither A nor B
Answer: C. Both A and B.
Explanation: Always Encrypted uses two kinds of keys: Column Master Key (CMK), which is an asymmetric key, and Column Encryption Key (CEK), which is a symmetric key.
True or False: Always Encrypted allows separation of those who own the data and manage the data from those who need to perform operations on the data.
- True
- False
Answer: True.
Explanation: Always Encrypted enables separation of duties between database administrators (who can manage the data, but not see plaintext values) and application administrators (who can operate on the data).
In Azure Cosmos DB, which tool can you use for implementing Always Encrypted?
- A. Azure CLI
- B. PowerShell
- C. SQL Server Management Studio
- D. Azure Cosmos DB Data Explorer
Answer: C. SQL Server Management Studio.
Explanation: SQL Server Management Studio (SSMS) is typically used to set up and manage Always Encrypted in SQL Server databases.
True or False: The column master key used in Always Encrypted can be stored in Azure Key Vault.
- True
- False
Answer: True.
Explanation: The column master key for Always Encrypted can indeed be stored securely in Azure Key Vault.
Which of the following can use Always Encrypted data without having to decrypt it first?
- A. Power BI
- B. Azure Machine Learning
- C. SQL Server Analysis Services
- D. None of the above
Answer: D. None of the above.
Explanation: Currently, data that is Always Encrypted must be decrypted before it can be used in Reporting services, Analytics services, or Machine Learning Services.
Interview Questions
What is the main purpose of Always Encrypted in Microsoft Azure?
Always Encrypted is a feature designed to protect data, store confidential data in an encrypted state, and limit access. This effectively helps to protect sensitive data at rest and in transit.
Where is Always Encrypted data decrypted?
Always Encrypted data is decrypted on a client-side, meaning the Azure service never sees the decrypted data.
Can you name the two types of Always Encrypted keys?
The two types of Always Encrypted keys are Column Master Key (CMK) and Column Encryption Key (CEK).
Does Always Encrypted support group by clauses while querying encrypted columns?
No, Always Encrypted does not support group by clauses, joins, or any computation on encrypted columns.
Are there any specific data types that Always Encrypted does not support?
Yes, Always encrypted does not support the following data types: text, ntext, image, XML, timestamp, table, sql_variant, hierarchyid, and geography.
What is the role of the Column Master Key (CMK) in Always Encrypted?
The Column Master Key (CMK) encrypts the Column Encryption Keys (CEKs). It is stored in a trusted key store, such as Azure Key Vault.
What is the role of the Column Encryption Key (CEK) in Always Encrypted?
The Column Encryption Key (CEK) is used to encrypt data in the database columns. It is also stored in the database in the system catalog view.
What is the difference between the two modes of Always Encrypted: Deterministic and Randomized?
Deterministic encryption always generates the same encrypted value for any given clear text value which supports point lookups and equality comparison. Randomized encryption is more secure but prevents searching, grouping, and indexing on encrypted columns.
Can I sort or index the encrypted database columns using Always Encrypted?
No, you cannot sort or index the encrypted database columns when using Always Encrypted.
Are there any specific permissions required to create Column Master Keys (CMK) and Column Encryption Keys (CEK) in Always Encrypted?
Yes, to create a Column Master Key (CMK), you need the CONTROL permission on the database. To create a Column Encryption Key (CEK), you need the ALTER ANY COLUMN ENCRYPTION KEY permission on the database.
Can I switch encryption modes after the initial setup in Always Encrypted?
Yes, it is possible to change the encryption type after initial setup by modifying the schema, decrypting the column data, and re-encrypting it using the new encryption type.
Can you directly insert into the encrypted column in Always Encrypted?
No, direct inserts into the encrypted column are not allowed as Always Encrypted is designed to ensure that Azure does not see unencrypted data.
What impact does Always Encrypted have on database performance?
Always Encrypted can impact database performance due to the additional overhead of encrypting and decrypting data. The impact largely depends on the volume of encrypted data and the number of transactions performed.
Is Always Encrypted compliant with GDPR?
Yes, Always Encrypted can help your application become GDPR compliant by ensuring sensitive data remains encrypted at rest and in transit.
Is Always Encrypted available for all types of databases in Azure?
No, Always Encrypted is currently available only for Azure SQL Database, SQL Server, and Azure Synapse Analytics.