How to configure Azure SQL Always Encrypted for Mac users
Tags: azure, sql, security
Published: 2022-08-18 00:26:00

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](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver16).

There are multiple ways to configure Always Encrypted.
- [Configure Always Encrypted using PowerShell](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-powershell?view=azuresqldb-current)
- [Configure Always Encrypted using SQL Server Management Studio (SSMS)](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-sql-server-management-studio?view=azuresqldb-current)
- [Configure Always Encrypted using Visual Studio Database Project](https://dev.to/kenakamu/visual-studio-database-project-and-always-encrypted-n4p)

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](https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver16) 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](#pre-requisites)
- [1. Install the SQL Database Projects extension](#install-the-sql-database-projects-extension)
- [2. Create new database project](#create-new-database-project)
- [3. Create keys](#create-keys)
    - [3.1 Create Column Master Key (CMK)](#create-column-master-key)
    - [3.2 Create Column Encryption Key (CEK)](#create-column-encryption-key)
- [4. Create table with encrypted columns](#create-table with-encrypted-columns)
- [5. Build/Publish with Data Studio](#build-publish-with-data-studio)
- [6. Confirm the result](#confirm-the-result)
- [How DACPAC looks like?](#how-dacpac-looks-like)
- [Conclusion](#conclusion)
- [References](#references)

## Pre-requisites <a name="pre-requisites"></a>
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](https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver16)
- [Installing PowerShell on macOS](https://docs.microsoft.com/en-us/powershell/scripting/install/installing-powershell-on-macos?view=powershell-7.2)

## 1. Install the SQL Database Projects extension<a name="install-the-sql-database-projects-extension"></a>
Firstly, install [the SQL Database Projects extension](https://docs.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-database-project-extension?view=sql-server-ver16) 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mbf5pkfy9to737ts938t.png)

## 2. Create new database project <a name="create-new-database-project"></a>
Now, let's create a database project using the extensions we have just installed.
Click `Create new` in Database Projects pane,

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bhsw816s86pgeh445uhs.png)

then select `Azure SQL Database` as type and give it a project name such as DB.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y6sm8k1oyi9abj1sk3jf.png)

## 3. Create keys<a name="create-keys"></a>
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) <a name="create-column-master-key"></a>
#### 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)](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-keys-using-powershell?view=azuresqldb-current#azure-key-vault-without-role-separation-example))

```powershell
# 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.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pprngtmd3xwpzy893g50.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8iqixowc1zno1mmvzhze.png)

#### 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/939pznn3dvghgpr67og3.png)

Then put `ColumnMasterKey` in the field and press `Enter` to confirm.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mrqr0ye4bjl1x5zfefej.png)

The following Transact-SQL should be added.
```sql
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) <a name="create-column-encryption-key"></a>
Next, Column Encryption Key (CEK).
Let's creates the encrypted value of a column encryption key with [New-SqlColumnEncryptionKeyEncryptedValue](https://docs.microsoft.com/en-us/powershell/module/sqlserver/new-sqlcolumnencryptionkeyencryptedvalue?view=sqlserver-ps).
(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)

```powershell
$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.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nlccul0vffl8nk2b13f2.png)

Then use the following Transact-SQL with the encrypted value which we just copied above.
```sql
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 <a name="create-table with-encrypted-columns"></a>
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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8ms9teg9w72xeqndfg33.png)

Then put `User` in the field and press `Enter` to confirm.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xs383e0n47j4zjqsff9k.png)

Paste the following Transact-SQL.
```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 <a name="build-publish-with-data-studio"></a>
After creating the Data Project, the first step is to build it.
Right-click on `DB` and select `Build`.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ebl43a41q1vuse7gnx67.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wg4k3gfxxnxqqeofgjew.png)

and select connection.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b75ma0dchr90wyv1rdnu.png)

We need to put all required connection details here and click `Connect`.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/znd4peq0bx2tetdv657k.png)

If Server and Database are set as expected, then publish it.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pvwuj5pdfxrf0fppkx6h.png)

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

# 6. Confirm the result<a name="confirm-the-result"></a>
Let's see if the database is configured as expected.
Go to Connection pane in Data Studio and click new query.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4frl83lsz1w7bjxkzwv2.png)

We can confirm it by running the following Transact-SQL.
```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`.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lx1nbkoontjp2g11x7oj.png)

Click `Advanced Properties` on the lower right,
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y08ff02iebpzy10fduqu.png)

Change Always Encrypted in the Security section to `Enabled` and press OK.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hrxnv9sgljk56nqi8s3l.png)

Next, enable Parameterization for Always Encrypted. (Ref - [Parameterization for Always Encrypted](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-query-columns-ads?view=sql-server-ver16#parameterization-for-always-encrypted))Parameterization for Always Encrypted is disabled by default.
Click on the Manage icon and open `Settings`.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/br71xcihfhg8bjjndr0o.png)
Use the search box and set Enable Parameterization for Always Encrypted.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w5pt2foxm5qynuttsii5.png)

Finally, let's add the actual data and see the result. For instance, add sample data as follows.
```sql
DECLARE @password NVARCHAR(50) = 'password'
INSERT INTO [User] (Name, Password) VALUES ('Ayaka', @password)

SELECT * FROM [User]
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3ccixm2hzqcs32do9nkc.png)

## How DACPAC looks like? <a name="how-dacpac-looks-like"></a>
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](https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-ver16) for more details.

We can export DACPAC with Data Studio.
Firstlly, [SQL Server dacpac extension](https://docs.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-server-dacpac-extension?view=sql-server-ver16) should be installed.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ar1ju9xbv3i2vkojt94l.png)

Go to `Connections` tab and click `Data-tier Application Wizard`.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q930zwbs1z680o3d7u29.png)

Step 1 : Select `Extract a data-tier application`.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f8e46ql9r5cxvbugmcjd.png)

Step 2 : Select extract DACPAC settings.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5qdy78ebp1tlgw6tmfuj.png)

Step 3 : If the contents of the Summary are as expected, click `Extract`.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u263sao6npr433snxxcp.png)

When the dacpac file is created at the specified location, compress it by appending .zip after .dacpac.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/710uoo7duqfx8suw9vec.png)

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](https://docs.microsoft.com/en-us/visualstudio/extensibility/the-structure-of-the-content-types-dot-xml-file?view=vs-2022)
- DacMetadata.xml
- model.xml
- Origin.xml

model.xml contains information on CMK, CEK, and columns which we just configured.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bbjpckaotnu1kul0uojv.png)

## Conclusion <a name="conclusion"></a>
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<a name="references"></a>
- [Download and install Azure Data Studio](https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver16)
- [Feature comparison with SQL Server Management Studio (SSMS)](https://docs.microsoft.com/en-us/sql/azure-data-studio/what-is-azure-data-studio?view=sql-server-ver16#feature-comparison-with-sql-server-management-studio-ssms)
- [Query columns using Always Encrypted with Azure Data Studio](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-query-columns-ads?view=sql-server-ver16)
- [SQL Database Projects extension (Preview)](https://docs.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-database-project-extension?view=sql-server-ver16)
- [SQL Server dacpac extension](https://docs.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-server-dacpac-extension?view=sql-server-ver16)
- [Data-tier applications (DAC)](https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-ver16)