Enabling TDE on Azure SQL Using Python

Question

I am new to Python and I am trying to write some small scripts top remediate my Azure environment. I am trying to test enabling TDE on Azure SQL databases, but I can't see any sample code to help me out.

Am I going on the right way using the TransparentDataEncryptionStatus command? I am not really sure where to go from here.

This is for an Azure Function

mssql_client = SqlManagementClient(credentials, sql_subscription)
database_settings = mssql_client.databases.create_or_update(sql_resource_group, sql_server, sql_db, TransparentDataEncryptionStatus

What goes next?

score 0 · Answer 1 · answered Sep 12 '19 at 03:22

You can use Transact-SQLto Manage transparent data encryption in Python:

 ALTER DATABASE [databasename] SET ENCRYPTION ON

Here's the example code:

import pyodbc
server = '<server>.database.windows.net'
database = '<database>'
username = '<username>'
password = '<password>'
driver= '{ODBC Driver 17 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute('ALTER DATABASE [databasename] SET ENCRYPTION ON')
cnxn .commit()

Hope this helps.

Anish K · Answer 2 · 2019-09-16T13:56:25.067

0

Am I going on the right way using the TransparentDataEncryptionStatus command?

Yes! You are on the right track.

create_or_update method creates or updates a database's transparent data encryption configuration.

The only change i see in your code is properly passing the TDE, which should be as below:

database_settings = mssql_client.transparent_data_encryptions.create_or_update(sql_resource_group, sql_server, sql_db, status=TransparentDataEncryptionStatus.Enabled)

or

 database_settings = mssql_client.transparent_data_encryptions.create_or_update(sql_resource_group, sql_server, sql_db, status='Enabled')

Please find the documentation here

edited Sep 16 '19 at 13:56

answered Sep 12 '19 at 07:53

Anish K

798
4
13

Thanks for the responses! I'm now getting the error "TypeError: create_or_update() missing 1 required positional argument: 'parameters'", this is my code mssql_client = SqlManagementClient(credentials, sql_subscription) db_id = mssql_client.databases.get database_settings = mssql_client.databases.create_or_update( sql_resource_group, sql_server, sql_db, status='Enabled' ) – ChrisB Sep 16 '19 at 13:32
I have updated the answer. The problem here was that in order to enable TDE, we need to use **transparent_data_encryptions** and not **databases** property of SqlManagementClient class. Would you please validate it? – Anish K Sep 16 '19 at 13:57
1

Sorry for the delay Anish! I only just got back to working on this. The answer is **database_settings = mssql_client.transparent_data_encryptions.create_or_update(group_name, server_name, database_name, status=TransparentDataEncryptionStatus.enabled)** – ChrisB Jun 24 '20 at 08:45

score 0 · Answer 3 · answered Jun 06 '23 at 21:14

I've created a blog post that shows the steps needed to setup BYOK TDE on Azure SQL DB which basically made of two steps. a. set up the server key b. set the key as the encryption protector

more about it in my post here: https://techcommunity.microsoft.com/t5/azure-database-support-blog/enabling-tde-on-azure-sql-db-server-using-python/ba-p/3840472

the core if it is here (partial code):

 tde = ServerKey(
    server_key_type=KeyType,
    uri=KeyURI
    )

server_key = sql_client.server_keys.begin_create_or_update(
    GROUP_NAME,
    SERVER,
    SERVER_KEY,
   tde
 ).result()

print("Attempt to apply the server key as encryption protector... ")
sql_client.encryption_protectors.begin_create_or_update(
    GROUP_NAME,
    SERVER,
    "current",
    {
        "server_key_name":SERVER_KEY,
        "server_key_type":KeyType
    }
)

score -1 · Answer 4 · edited Jun 24 '20 at 10:19

-1

database_settings = mssql_client.transparent_data_encryptions.create_or_update(
    group_name, server_name, database_name,
    status=TransparentDataEncryptionStatus.enabled)

edited Jun 24 '20 at 10:19

tuomastik

4,559
5
36
48

answered Jun 24 '20 at 08:46

ChrisB

1

Enabling TDE on Azure SQL Using Python

4 Answers4