How to configure Azure SQL Always Encrypted for Mac users

Always Encrypted is a feature included in Azure SQL Server. Data is encrypted all the time, not only at rest but also in motion. Furthermore, the encryption keys which are essential for both encrypting and decrypting are not stored in the database.
For more information on Always Encrypted, please refer to the official documentation.

There are multiple ways to configure Always Encrypted.

• Configure Always Encrypted using PowerShell
• Configure Always Encrypted using SQL Server Management Studio (SSMS)
• Configure Always Encrypted using Visual Studio Database Project

Of the methods listed above, SSMS and Visual Studio Database Project are only available for Windows.
If you need to run on macOS or Linux, Azure Data Studio or Visual Studio Code is for you.
In this article, I'm going to explain how to configure Always Encrypted with Azure Data Studio on macOS.

TOC

• Pre-requisites
• 1. Install the SQL Database Projects extension
• 2. Create new database project
• 3. Create keys
  • 3.1 Create Column Master Key (CMK)
  • 3.2 Create Column Encryption Key (CEK)
• 4. Create table with encrypted columns
• 5. Build/Publish with Data Studio
• 6. Confirm the result
• How DACPAC looks like?
• Conclusion
• References

Pre-requisites

There are several combination patterns, but for this example we will use SQL database and Key Vault to store a master key. The following Azure resources will be used here.

• SQL Server
• SQL Database
• Key Vault

In addition, if you haven't installed yet:

• Download and install Azure Data Studio
• Installing PowerShell on macOS

1. Install the SQL Database Projects extension

Firstly, install the SQL Database Projects extension in Azure Data Studio.
It is an Azure Data Studio and VS Code extension for developing SQL databases including for SQL Server, Azure SQL Database, and Azure SQL Managed Instance in a project-based development environment.
*This extension is still in preview. (as of 18th Aug 2022)

2. Create new database project

Now, let's create a database project using the extensions we have just installed.
Click Create new in Database Projects pane,

then select Azure SQL Database as type and give it a project name such as DB.

3. Create keys

Two keys are required for Always Encrypted.

• Column Master Key (Store in Key Vault)
• Column Encryption Key (Store in SQL database)

3.1 Create Column Master Key (CMK)

3.1.1 Create Column Master Key in Azure Key Vault

We can manually create CMK in Azure Key Vault via Azure portal or by running the following PowerShell script. (Ref: Azure Key Vault without Role Separation (Example))

# Create a column master key in Azure Key Vault.
Import-Module Az
Connect-AzAccount
$SubscriptionId = "<Azure SubscriptionId>"
$resourceGroup = "rg-ayhara-playground"
$azureLocation = "japaneast"
$akvName = "kv-ayhara-sample"
$akvKeyName = "CMKAuto1"
$azureCtx = Set-AzConteXt -SubscriptionId $SubscriptionId #Sets the context for the below cmdlets to the specified subscription.
New-AzResourceGroup -Name $resourceGroup -Location $azureLocation # Creates a new resource group - skip, if your desired group already exists.
New-AzKeyVault -VaultName $akvName -ResourceGroupName $resourceGroup -Location $azureLocation # Creates a new key vault - skip if your vault already exists.
Set-AzKeyVaultAccessPolicy -VaultName $akvName -ResourceGroupName $resourceGroup -PermissionsToKeys get, create, delete, list, wrapKey,unwrapKey, sign, verify -UserPrincipalName $azureCtx.Account
$akvKey = Add-AzKeyVaultKey -VaultName $akvName -Name $akvKeyName -Destination "Software"

Please confirm that the CMK has been created in the Key Vault as expected and copy the Key Identifier for use in the next step.

We need to make sure that the required permissions as well, i.e. get, create, delete, list, wrapKey,unwrapKey, sign, verify, are granted.

3.1.2 Set Key Vault information to master key

Let's go back to Data Studio.
Since the CMK and CEK templates in Visual Studio are not available in Data Studio unfortunately, we require to add script as item.

Click Add new item

Then put ColumnMasterKey in the field and press Enter to confirm.

The following Transact-SQL should be added.

CREATE COLUMN MASTER KEY [CMK_Auto1]
WITH (
     KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
     KEY_PATH = N'https://kv-ayhara-sample-ado.vault.azure.net/keys/CMKAuto1/ecffa3fdcb2f432b9b0b8474770ade38'
);

3.2 Create Column Encryption Key (CEK)

Next, Column Encryption Key (CEK).
Let's creates the encrypted value of a column encryption key with New-SqlColumnEncryptionKeyEncryptedValue.
(Unfortunately, The New-SqlColumnEncryptionKeyEncryptedValue cmdlet is only available in PowerShell 5 which is available in Windows only. Please run the following commands to create the encrypted value of a column encryption key by using PowerShell 5 in Windows. - As of Aug 2022)

$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyUrl "https://kv-ayhara-sample-ado.vault.azure.net/keys/CMKAuto1/ecffa3fdcb2f432b9b0b8474770ade38"
$encryptedValue = New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMasterKeySettings $cmkSettings 
$encryptedValue | Set-Clipboard

As with CMK, we need to add script as item.

Then use the following Transact-SQL with the encrypted value which we just copied above.

CREATE COLUMN ENCRYPTION KEY [CEK_Auto1]
WITH VALUES
(
     COLUMN_MASTER_KEY = [CMK_Auto1],
     ALGORITHM = 'RSA_OAEP',
     ENCRYPTED_VALUE = 0x01B6000001........
);

4. Create table with encrypted columns

Let's say we creates a table named User, with columns named Name and Password, and that the password column is encrypted.

Click Add table

Then put User in the field and press Enter to confirm.

Paste the following Transact-SQL.

CREATE TABLE [dbo].[User]
(
    [Name] NVARCHAR(50) NOT NULL PRIMARY KEY,
    [Password] NVARCHAR(50) 
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_Auto1,  
        ENCRYPTION_TYPE = RANDOMIZED,  
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
)

5. Build/Publish with Data Studio

After creating the Data Project, the first step is to build it.
Right-click on DB and select Build.

Once build succeeded now we're ready to publish it to database.
If the build fails, saving all files once and reopening the sqlproj file again in data studio may help.

Click Publish,

and select connection.

We need to put all required connection details here and click Connect.

If Server and Database are set as expected, then publish it.

Please confirm that 'Deploy dacpac succeeded' is displayed in the Window.

6. Confirm the result

Let's see if the database is configured as expected.
Go to Connection pane in Data Studio and click new query.

We can confirm it by running the following Transact-SQL.

SELECT * FROM sys.column_master_keys
SELECT * FROM sys.column_master_key_definitions
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
SELECT encryption_type_desc FROM sys.all_columns WHERE name = 'Password'

Of course, we can also add data and retrieve plaintext values stored in encrypted columns.
Firstly, enabling Always Encrypted for a database connection.
Right-click on the server name and select Edit Connection.

Click Advanced Properties on the lower right,

Change Always Encrypted in the Security section to Enabled and press OK.

Next, enable Parameterization for Always Encrypted. (Ref - Parameterization for Always Encrypted)Parameterization for Always Encrypted is disabled by default.
Click on the Manage icon and open Settings.

Use the search box and set Enable Parameterization for Always Encrypted.

Finally, let's add the actual data and see the result. For instance, add sample data as follows.

DECLARE @password NVARCHAR(50) = 'password'
INSERT INTO [User] (Name, Password) VALUES ('Ayaka', @password)

SELECT * FROM [User]

How DACPAC looks like?

A data-tier application (DAC) is a logical database entity that defines all of the SQL Server objects - such as tables, views, and instance objects, including logins - associated with a user's database. A DAC is a self-contained unit of the entire database model and is portable in an artifact known as a DAC package, or .dacpac. Please refer to official document for more details.

We can export DACPAC with Data Studio.
Firstlly, SQL Server dacpac extension should be installed.

Go to Connections tab and click Data-tier Application Wizard.

Step 1 : Select Extract a data-tier application.

Step 2 : Select extract DACPAC settings.

Step 3 : If the contents of the Summary are as expected, click Extract.

When the dacpac file is created at the specified location, compress it by appending .zip after .dacpac.

Extract the zip file and open it in Editor such as Visual Studio Code. We can see that DACPAC consists of four files:

• [Content_Types].xml
• DacMetadata.xml
• model.xml
• Origin.xml

model.xml contains information on CMK, CEK, and columns which we just configured.

Conclusion

Always Encrypted is a great feature when Azure SQL Server is used. As I introduced above it can be configured by the Data Studio extension, which is currently in preview, even if we are using MacOS.

References

• Download and install Azure Data Studio
• Feature comparison with SQL Server Management Studio (SSMS)
• Query columns using Always Encrypted with Azure Data Studio
• SQL Database Projects extension (Preview)
• SQL Server dacpac extension
• Data-tier applications (DAC)