DEV Community

Ayaka Hara
Ayaka Hara

Posted on • Edited on

How to configure Azure SQL Always Encrypted for Mac users

#azure #sql #security

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.

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

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:

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)

Image description

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,

Image description

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

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"
Enter fullscreen mode Exit fullscreen mode

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.
Image description

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

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
Image description

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

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'
);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

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........
);
Enter fullscreen mode Exit fullscreen mode

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
Image description

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

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
)
Enter fullscreen mode Exit fullscreen mode

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.
Image description

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,
Image description

and select connection.
Image description

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

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

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.

Image description

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'
Enter fullscreen mode Exit fullscreen mode

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.
Image description

Click Advanced Properties on the lower right,
Image description

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

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.
Image description
Use the search box and set Enable Parameterization for Always Encrypted.
Image description

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]
Enter fullscreen mode Exit fullscreen mode

Image description

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.
Image description

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

Step 1 : Select Extract a data-tier application.
Image description

Step 2 : Select extract DACPAC settings.
Image description

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

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

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

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

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

Top comments (0)

Read next

epigene profile image

TIL: joining with LIKE

Augusts Bautra -

ziizium profile image

Security news weekly round-up - 1st November 2024

Habdul Hazeez -

khai-jt profile image

Move hardcoded secrets to a Secrets Manager

Khai J. Thani -

esproc_spl profile image

Getting members according to primary key values #eg63

Judy -