at the moment I am working with the Azure Cloud. I want to set up an AzureSQL database and use AlwaysEncrypted to ensure that the data is 'always encrypted' ;-). Furthermore I would like to set up AzureFunctions which are able to connect to the Database as well as write records in.
I already set up an AzureSQL Database but I do not know how to work with it. I started two attempts:
CREATE COLUMN MASTER KEY CMK_test_1
WITH (
KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',
KEY_PATH = '<PATH_TO_AZURE_KEY_VAULT>'
)
CREATE COLUMN ENCRYPTION KEY CEK_test_1
WITH VALUES
(
COLUMN_MASTER_KEY = CMK_test_1,
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = <VALUE>
)
Create Table dbo.AlwaysEncryptedTest
(
ID int identity(1,1) PRIMARY KEY
, FirstName varchar(25) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK_test_1) not NULL
, LastName varchar(25) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK_test_1) not NULL
, City varchar(25) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK_test_1) not NULL
, StreetName varchar(25) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK_test_1) not NULL
)
I know that I have to use an application to put records in the database but I could not find a tutorial or something else that helps me to do so. I found some C# explenation on the Microsoft website but this did not help me to do the job. In best case I would write the connection in python.
Any help is appreciated.
Best P
If you want to connect Azure SQL server which enables always encrypt
with Azure key vault in python application, we can use ODBC driver to implement it.
Regarding how to implement it, we need to add ColumnEncryption=Enabled
into connection string to tell odbc application always encrypt has been enabled. Besides, since we use Azure key vault store, we also need to add KeyStoreAuthentication
KeyStorePrincipalId
and KeyStoreSecret
to make ODBC application connect Azure key vault, get encryption key.
For more details, please refer to here and here
For example
az login
az ad sp create-for-rbac --skip-assignment --sdk-auth
az keyvault set-policy --name $vaultName --key-permissions get, list, sign, unwrapKey, verify, wrapKey --resource-group $resourceGroupName --spn <clientId-of-your-service-principal>
server = '<>.database.windows.net'
database = ''
username = ''
password = ''
driver= '{ODBC Driver 17 for SQL Server}'
KeyStoreAuthentication='KeyVaultClientSecret'
KeyStorePrincipalId='<clientId-of-your-service-principal>'
KeyStoreSecret='<clientSecret-of-your-service-principal>'
conn_str=f'DRIVER={driver};SERVER={server};PORT=1443;DATABASE={database};UID={username};PWD={password};ColumnEncryption=Enabled;KeyStoreAuthentication={KeyStoreAuthentication};KeyStorePrincipalId={KeyStorePrincipalId};KeyStoreSecret={KeyStoreSecret}'
with pyodbc.connect(conn_str) as conn:
with conn.cursor() as cursor:
cursor.execute("SELECT * FROM [dbo].[Patients]")
row = cursor.fetchone()
while row:
print (row)
row = cursor.fetchone()