Azure encrypted storage account and data access from Snowflake

The toboggan hill in RNUP Toronto ON, Dec 15 2024

Data Encryption means two things here:

• data in transit: using TLS (PKI handshake + symmetric key on plain data).
• data at rest: using symmetric key.

In brief, encryption in transit has two stages in brief:

1> client and server exchange symmetric encryption key via handshake implemented using PKI.

2> client and server exchange data encrypted by the symmetric encryption key.

In this post, encryption at rest is describe to use three types of keys to encrypt plain data

1> Azure managed key

Azure storage account default — using Azure managed symmetric key called data encryption key(DEK) to encrypt plain data and vise versa.

2> Customer-managed key

Use an additional client/us managed “key encryption key” (KEK) to encrypt the DEK. This is to hide DEK that even Azure can’t use it to potentially access client data.

3> Customer-provided key

Use client/us provided symmetric encryption key (CPK): CPK is provided in data access requests between client and storage account. CPK used to encrypt/decrypt blob data to replaced DEK.

For example, here the “client” can be Snowflake external stage with ENCRYPTION/master_key parameter. “master_key is the client provided key.

1 Azure managed key

By default Azure managed key is used to encrypt all data in storage account. we can see “server encrypted” property is “true” of a file. This is transparent for us.

2 Customer-managed key

In enterprise setup, we want to use our key to encrypt data in stead of using Azure default. In Azure, it’s implemented by introducing an Key Encryption Key (KEK).

First, we create a RSA key in Key Vault which is our KEK. This key is NOT used to encrypt our data directly. Instead, it’s used to wrap/unwrap an Azure managed Data Encryption Key (DEK, like in above item 1).

Next, the DEK is used to encrypt/decrypt our data.

Case 0, when configuring storage account to use customer-managed key, we tell where to find our KEK, in KV for example. Storage account uses the KEK to wrap Azure managed encryption key(DEK).

Case 1, Client uploads file to storage account. Storage account 1) goes to key vault to get our KEK, 2) uses the KEK to unwrap to get Azure managed encryption key DEK, 3) uses DEK to encrypt the new file and saves it.

Case 2, Client downloads file from storage account. Storage account 1) goes to key vault to get our KEK, 2) uses the KEK to unwrap to get Azure managed encryption key DEK, 3) uses DEK to decrypt the file and responses to download.

Note, KEK is actually a PKI key pair. So it’s used to wrap/unwrap(encrypt/decrypt) small data — a key (DEK) in this case. DEK is symmetric encryption key.

3 Customer-provided key

This customer provided key (symmetric) will be used by storage account to encrypt file when file uploading and decrypt file before file downloading.

For example, when Snowflake writes files to Azure storage account via a external stage. Snowflake can provide data encryption key we want to use in external storage definition for this purpose. The file will have encryption key info in file metadata properties. Azure knows to use this key for data decryption in future.

Storage account file which is encrypted by customer provided key

4 Step by step

This diagram shows how KEK is configured in Azure. Snowflake uses standard storage integration/external stage to access Azure storage account. All KEK encryptions happen in Azure, Snowflake accesses Azure the same way as without using KEK.

4.1 Create an user managed identity

Create an user managed identity called user-managed-id-feng. Storage account will use this identity to access key vault for customer-managed key to encrypt blob data.

4.2 Create and configure key vault

4.2.1 Create key vault called and assign my current user as “key vault admin” role in role assignment.

Then create a 2048 bit RSA key — this is a PKI key pair. And download public key which will be used by Snowflake to connect to this encrypted storage.

Download public key

4.2.2 Assign role “key vault administrator” to the user managed identity in this key vault.

4.3 Set storage account encryption using customer managed key stored in key vault

4.4 Configure Snowflake

4.4.1 Create a storage integration that provides connection to this storage account

use role accountadmin;
use database mydb;

create storage integration azure_storage_integration
type = external_stage
storage_provider = azure
enabled = true
azure_tenant_id = '8fxxxxde'
storage_allowed_locations = ('azure://sasnowflake12345.blob.core.windows.net/sacontainer12345');

desc integration azure_storage_integration;

Use consent url (from above desc results) to allow creation of Azure service principal which will be used by Snowflake to access Azure.

Identify the service principal in Enterprise Applications

Now configure storage account role assignment: assign Storage Blog Data Reader role to above service principal. (“Storage Blob Data Contributor” role is needed if we want Snowflake to write file to Azure blob)

4.4.2 Create external stage pointing to blob data using above storage integration

create or replace stage azure_stage
url='azure://sasnowflake12345.blob.core.windows.net/sacontainer12345'
storage_integration = azure_storage_integration
;
list @azure_stage;

create file format ff_avro type = 'avro';
select $1 from @azure_stage/teamx-monitoring-data.avro
(file_format => 'ff_avro');

4.4.3 Create external stage with a customer-provided key

-- make up a symmetric encryption key:
-- plain test  = test1234test1234
-- echo -n 'test1234test1234' | openssl base64
-- output: dGVzdDEyMzR0ZXN0MTIzNA==

create or replace stage encrypted_customer_key_stage
url='azure://sasnowflake12345.blob.core.windows.net/sacontainer12345'
storage_integration = azure_storage_integration
encryption=(TYPE = 'AZURE_CSE', MASTER_KEY='dGVzdDEyMzR0ZXN0MTIzNA==')
;

list @encrypted_customer_key_stage;

Now, we write a file to this stage and check what we have from Azure storage account console.

create table mytab (id int, name varchar);
insert into mytab values (1, 'John');

create file format ff_csv type = 'csv';

copy into @encrypted_customer_key_stage/sf_cpk_file.csv
from (select * from mytab) 
file_format = (format_name = 'ff_csv' compression=none)
overwrite = true header = true single = true;

This file has a metadata property indicating what customer-provided key (CPK)is used encrypting this file. Snowflake write file request has sent file data and the CPK over to Azure. Azure has encrypted this file, wrapped the CPK and knows how to use it to decrypt in future.

Storage account file which is encrypted by customer provided key

The metadata details:

Azure attaches a hash of the customer provided key (CPK) from Snowflake to the file. Azure validates the hash and the incoming CPK in the request each time when Snowflake reads this file in future. Azure does NOT keep the CPK.

{
  "EncryptionMode": "FullBlob",
  "WrappedContentKey": {
    "KeyId": "symmKey1",
    "EncryptedKey": "4vCyxxxxsUZQ==",
    "Algorithm": "AES_CBC_128"
  },
  "EncryptionAgent": {
    "Protocol": "1.0",
    "EncryptionAlgorithm": "AES_CBC_256"
  },
  "ContentEncryptionIV": "AAxxxx==",
  "KeyWrappingMetadata": {
    "EncryptionLibrary": "Java 5.3.0"
  }
}

Note, between Snowflake and Azure (data in transit), TLS is used to encrypt above data file and CPK when passing them to Azure.

So we have discussed encryption at rest in Azure storage account (aka server side). There is one more called “Infrastructure encryption” — Azure uses it add one more layer of encryption when creating storage account or encryption scope. Encryption key of “Infrastructure encryption” is managed by Azure. It works the same way like item 1 in this post — it’s transparent to client/us by just enable it.

Download validation:

1> Download from Azure storage account console

File is encrypted by customer provided key (via Snowflake external stage). So Download from Azure storage account console ended up with an encrypted file.

2> Download/read from Snowflake external stage

Snowflake reads the file via the external stage where customer provided key (CPK)is provided. Azure received this CPK, decrypts the file and passes it back to Snowflake (TLS encrypted in transit).

Useful links:

Streaming Azure storage data to Snowflake

More discussions about streaming Azure data to Snowflake

Azure Encryption key management

Customer provided key

All we talked about in this post is server side encryption that happens at Azure side.

For client side encryption, please start from this Azure doc.

Happy Learning!