SQL MI Encryption is not automatic

Question

SQL MI Encryption is not automatic

JoelP 366

Hi,

Can you advise if this is normal? Create new SQL MI, TDE is enabled, we restored user databases and noticed that the default encryption for the restored db are switch off.

I had to run script to set ENCRYPTION ON.

SSingh-MSFT 16,371 Reputation points Moderator

2023-05-02T06:43:49.8933333+00:00

Hi
Joel Prescilla •,

Welcome to Microsoft Q&A forum and thanks for using Azure services.

As I understand, you have noticed that for restored user databases in Azure SQL Managed Instance, default encryption was switched off.

TDE encrypts the storage of an entire database by using a symmetric key called the Database Encryption Key (DEK). DEK is protected by the TDE protector. This DEK is protected by the TDE protector which is either a service managed key or the customer managed key in Azure Key Vault

For Azure SQL Managed Instance, TDE is on by default for the instance, which means all databases created for the instance are enabled by TDE.

By default, Azure SQL Managed Instance uses a service managed key, which means Azure SQL manages a certificate for the key (rotates the key and protects it with a root key within Azure).

Another option is to use TDE with Customer managed Keys. TDE with Customer managed Keys uses Azure Key Vault, which provides highly available and scalable secure storage for RSA cryptographic keys backed by FIPS 140-2 Level 2 validated hardware security modules (HSMs), while Managed HSM offering provides the Level 3. Azure Key Vault streamlines the key management process and enables customers to maintain full control of encryption keys, including managing and auditing key access.

Could you please check if TDE is SMK or CMK?

If CMK, then "Make This Key as Default TDE Protector" setting is enabled or not?

Reference Link: https://techcommunity.microsoft.com/t5/azure-sql-blog/how-to-restore-across-different-sql-managed-instances-when-using/ba-p/3270090

Let us know your inputs so that we can further assist. Thank you.
JoelP 366 Reputation points

2023-05-02T20:35:55.0333333+00:00

HI @SSingh-MSFT ,

Thank you for your response. The TDE is enabled using SMK when I restored the databases 2 weeks ago. Then when I reviewed Defender for Cloud today, it was telling me that the databases are not encrypted. When I ran the query, the is_encrypted column is 0. As I said, I had to run a script to switch on ENCRYPTION for every single database.

I would have expected this to be be switched on every time a database is restored in the instance with TDE switched on.
SSingh-MSFT 16,371 Reputation points Moderator

2023-05-05T08:44:25.53+00:00

Hi
Joel Prescilla •,

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

1 answer

Your answer

SSingh-MSFT 16,371 Reputation points Moderator

2023-05-02T06:43:49.8933333+00:00

Hi
Joel Prescilla •,

Welcome to Microsoft Q&A forum and thanks for using Azure services.

As I understand, you have noticed that for restored user databases in Azure SQL Managed Instance, default encryption was switched off.

TDE encrypts the storage of an entire database by using a symmetric key called the Database Encryption Key (DEK). DEK is protected by the TDE protector. This DEK is protected by the TDE protector which is either a service managed key or the customer managed key in Azure Key Vault

For Azure SQL Managed Instance, TDE is on by default for the instance, which means all databases created for the instance are enabled by TDE.

By default, Azure SQL Managed Instance uses a service managed key, which means Azure SQL manages a certificate for the key (rotates the key and protects it with a root key within Azure).

Another option is to use TDE with Customer managed Keys. TDE with Customer managed Keys uses Azure Key Vault, which provides highly available and scalable secure storage for RSA cryptographic keys backed by FIPS 140-2 Level 2 validated hardware security modules (HSMs), while Managed HSM offering provides the Level 3. Azure Key Vault streamlines the key management process and enables customers to maintain full control of encryption keys, including managing and auditing key access.

Could you please check if TDE is SMK or CMK?

If CMK, then "Make This Key as Default TDE Protector" setting is enabled or not?

Reference Link: https://techcommunity.microsoft.com/t5/azure-sql-blog/how-to-restore-across-different-sql-managed-instances-when-using/ba-p/3270090

Let us know your inputs so that we can further assist. Thank you.
JoelP 366 Reputation points

2023-05-02T20:35:55.0333333+00:00

HI @SSingh-MSFT ,

Thank you for your response. The TDE is enabled using SMK when I restored the databases 2 weeks ago. Then when I reviewed Defender for Cloud today, it was telling me that the databases are not encrypted. When I ran the query, the is_encrypted column is 0. As I said, I had to run a script to switch on ENCRYPTION for every single database.

I would have expected this to be be switched on every time a database is restored in the instance with TDE switched on.
SSingh-MSFT 16,371 Reputation points Moderator

2023-05-05T08:44:25.53+00:00

Hi
Joel Prescilla •,

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

Answer 1

Hi
Joel Prescilla •,

Thanks for the detailed response.

There is a Note in Microsoft documentation here which states that:

All newly created databases in SQL Database are encrypted by default by using service-managed transparent data encryption. Existing SQL databases created before May 2017 and SQL databases created through restore, geo-replication, and database copy are not encrypted by default. Existing SQL Managed Instance databases created before February 2019 are not encrypted by default. SQL Managed Instance databases created through restore inherit encryption status from the source. To restore an existing TDE-encrypted database, the required TDE certificate must first be imported into the SQL Managed Instance.

User's image

Reference Link: Transparent data encryption for SQL Database, SQL Managed Instance, and Azure Synapse Analytics

Hope this helps. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

Thank you.

Share via

SQL MI Encryption is not automatic

1 answer

Your answer