Unable to setup TDE via Azure SDK for python

Question

I'm trying to setup transparent data encryption on my Azure SQL server with the help of a customer managed key I have stored on my key vault. I'm doing all of this via Azure SDK for python. I'm providing code for the same.

def enable_transparent_data_encryption(server_name: str):
    key_client = KeyClient(vault_url=KEY_VAULT_URL, credential=credentials)
    key_name = create_sql_cmk(server_name)
    key = key_client.get_key(name=key_name)
    key_version = key.properties.version
    server_key_name = f'{KEY_VAULT_URL.split("//")[-1].split(".")[0]}_{key_name}_{key_version}'
    tde = ServerKey(
        auto_rotation_enabled=True,
        server_key_type=ServerKeyType.AZURE_KEY_VAULT,
        uri=f'{KEY_VAULT_URL}keys/{key_name}/{key_version}'
    )

    sql_client.server_keys.begin_create_or_update(RESOURCE_GROUP_NAME, server_name, server_key_name, tde).wait()
    print(f'Enabled - Transparent Data Encryption using CMK on `{server_name}` SQL server.')

I have tried my code but it doesn't enable transparent data encryption on my SQL server.

score 0 · Answer 1 · answered May 25 '23 at 13:16

0

I tried the below code and the Transparent Data Encryption with CMK was enabled in Azure SQL from Key vault successfully.

Code:-

from azure.identity import AzureCliCredential
from azure.keyvault.keys import KeyClient
from azure.mgmt.sql import SqlManagementClient
from azure.mgmt.sql.models import ServerKeyType, ServerKey

credential = AzureCliCredential()

key_vault_url = "https://kamalival.vault.azure.net"
key_client = KeyClient(vault_url=key_vault_url, credential=credential)

key_name = "kamalikeyvault"
key_version = "ba4979e3ef934a3e919e392942c0b42a"
key = key_client.get_key(name=key_name, version=key_version)

sql_client = SqlManagementClient(credential=credential, subscription_id="b83c1ed3-c5b6-44fb-b5ba-2b83a074c23f")

server_name = "kamaserver"
server_key_name = "kamalival_kamalikeyvault_ba4979e3ef934a3e919e392942c0b42a"
tde = ServerKey(
    auto_rotation_enabled=True,
    server_key_type=ServerKeyType.AZURE_KEY_VAULT,
    uri=key.id
)
sql_client.server_keys.begin_create_or_update(
    "Vyshu", server_name, server_key_name, tde).wait()
print(f"Enabled Transparent Data Encryption using CMK on '{server_name}' SQL server.")

Make sure your server_key_name is in this format -KeyVaultName_KeyVaultKeyName_KeyVaultKeyVersion You can get the information of your Key vault name, Key name and Key version from Key ID in your portal like below:-

enter image description here

I have used AzureCliCredential for authenticating with my Azure account. By logging into my VS Code terminal by running the commands below:-

Commands Reference1, Reference2

az login
az account set --subscription "azure-subscription-name"

Make sure the user account you’re signing in while logging to Azure Portal has sufficient roles assigned to access Key vault Keys and SQL Server.

Output:-

enter image description here

Portal TDE enabled:-

enter image description here

answered May 25 '23 at 13:16

Dasari Kamali

811
2
2
6

I'm trying with the same code as yours. I think there might be some permissions related issue. Do you what permissions you gave? – Swapnil91color Jun 06 '23 at 10:28
@Swapnil91color Yes, you need to assign the user with whom you're logging into Azure and using in your terminal at least an owner or contributor role at the SQL server level. – Dasari Kamali Jun 06 '23 at 11:00
Well my user has contributor roles over sql server. Plus the fact that my server has get wrap and unwrap key permissions. Also purge protection and soft deletion are enabled on key vault. Both the key vault and database belong to the same AD tenant. Still when I execute the code it executes however it still uses service managed key..? – Swapnil91color Jun 06 '23 at 11:18
My server_key_name is also accurate. Could it be because my key vault only Allow public access from specific virtual networks and IP addresses? – Swapnil91color Jun 06 '23 at 11:21
I have Allowed trusted Microsoft services to bypass this firewall as checked – Swapnil91color Jun 06 '23 at 11:21
@Swapnil91color Yes, Try allowing your Client machines IP in the SQL. – Dasari Kamali Jun 06 '23 at 11:34
any ideas @Dasari Kamali – Swapnil91color Jun 06 '23 at 11:43
My client ip is allowed on sql server networking. – Swapnil91color Jun 06 '23 at 11:52
@Swapnil91color Ok, Can you share your error code? – Dasari Kamali Jun 06 '23 at 11:56
There is no error code! It executes normally, hell it even prints it has enabled TDE for my sql server name, however when I go to portal to check it actually isn't enabled. – Swapnil91color Jun 06 '23 at 11:59

Unable to setup TDE via Azure SDK for python

1 Answers1