TDE(backup from smss to azure)

1 Create master key in master database (set master key)

2 Create TDE certificate (encrypted by MK)

3 Backup the Certificate and private key, By encryption with a password ( did not do it in this case due to storage blog problems)

4 Choose DB to create the DEK ,Create database encryption key (DEK) with algorithm ( AES= 256) and encryption by certificate 

5 Set encryption on for the database 

 6 Create a new credential with the SAS token
CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sp=racwdli&st=2025-02-25T13:28:42Z&se=2026-02-25T21:28:42Z&spr=https&sv=2022-11-02&sr=c&sig=kBFwlj5S5eqBEE32LM1EFebBY0W94uEFiwOXo3R0yt4%3D';
Go

7 

The backup was unable to be done from smss to azure ( the MI does not support encrypted DBs to be backed up from smss to azure)

We have unencrypted a database and backed it up from ssms to azure blob successfully 

Conclusion: Can be done with and unencrypted DB but not with an Encrypted one>

Using TDE directly from azure portal

1 Go to azure key vault ( DemoTestRudi) to generate a key

- Go to keys and click generate 

- Name your key (mysqlmikey)

- Choose a key type (RSA)

-Choose RSA key size (2048-bit) 

Note:

• switching from 2048-bit RSA to 4096-bit RSA for TDE will affect performance, but the actual impact might be minor, especially on modern hardware and typical workloads.
• It will likely affect CPU usage more than disk I/O, and the impact might be more noticeable during key management operations (key generation, encryption, etc.).
• If your database is not under heavy load and your hardware can handle the extra processing, the trade-off for better security may be worth it.

- Click create 

2 Enable TDE

- Go to your Managed instance ( sqlmi-ebs-lab)

- Click on security and choose Transparent data encryption 

- Select the type of managed key ( Customer-managed key0

- Select the key from the key vault we generated in the key vault( mysqlmikey)

- Make the key the default TDE protector

-Click save

- TDE has now been enabled on the Managed instance 

Conclusion: All the databases have been encrypted by an asymmetric key. The key is the same for each database ( same encryption thumbprint).

We are now able to backup databases from SSMS to Azure storage. 

1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
GO

CREATE CERTIFICATE TDE_Certificate                    
WITH SUBJECT = 'TDE Certificate';
GO

2 USE master;
GO

SELECT
    cert.name AS Certificate_Name,
    cert.subject AS Certificate_Subject,
    cert.issuer_name AS Issuer_Name,
    cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
FROM
    sys.certificates cert
WHERE
    cert.name LIKE 'TDE%';

3 BACKUP CERTIFICATE TDE_Certificate
TO FILE = https://zagpebslab.blob.core.windows.net/sql-backups?sp=racwl&st=2025-02-25T13:28:42Z&se=2026-02-25T21:28:42Z&spr=https&sv=2022-11-02&sr=c&sig=eQKxB%2F0bg5cX71PmhUTlGHLCnIwSbe5CLOWVVkaDR1g%3D\TDE_Certificate.cer'
WITH PRIVATE KEY (
    FILE = https://zagpebslab.blob.core.windows.net/sql-backups?sp=racwl&st=2025-02-25T13:28:42Z&se=2026-02-25T21:28:42Z&spr=https&sv=2022-11-02&sr=c&sig=eQKxB%2F0bg5cX71PmhUTlGHLCnIwSbe5CLOWVVkaDR1g%3D\TDE_PrivateKey.pvk',
    ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'
);
GO

USE Normal;
GO

 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
GO

USE [dba]
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
GO

USE [dba1]
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
GO

USE [dba2]
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
GO

USE [dba3]
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
GO

USE [xwiki]
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
GO

 7. Enable Transparent Data Encryption (TDE) on the database
ALTER DATABASE dba
SET ENCRYPTION ON;
GO

select name, database_id, state_desc
from sys.databases

SELECT
    database_id,
    key_algorithm,
    key_length,
    encryption_state_desc
 encryptor_type

FROM
  sys.dm_database_encryption_keys;

  Select * from sys.dm_database_encryption_keys

  BACKUP DATABASE [dba2]
TO URL = 'https://zagpebslab.blob.core.windows.net/sql-backups?sp=racwl&st=2025-02-25T13:28:42Z&se=2026-02-25T21:28:42Z&spr=https&sv=2022-11-02&sr=c&sig=eQKxB%2F0bg5cX71PmhUTlGHLCnIwSbe5CLOWVVkaDR1g%3D'
With copy_only
GO

BACKUP DATABASE [dba2]
TO URL = 'https://zagpebslab.blob.core.windows.net/sql-backups?sp=racwl&st=2025-02-25T13:28:42Z&se=2026-02-25T21:28:42Z&spr=https&sv=2022-11-02&sr=c&sig=eQKxB%2F0bg5cX71PmhUTlGHLCnIwSbe5CLOWVVkaDR1g%3D',

  COPY_ONLY,   Ensures the backup does not affect the regular backup chain
     COMPRESSION,   Optional: Compresses the backup to save storage space
     STATS = 10   Optional: Provides backup progress status
GO

 Step 1: Drop the existing credential (if needed)
DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
GO

 Step 2: Create a new credential with the SAS token
CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sp=racwdli&st=2025-02-25T13:28:42Z&se=2026-02-25T21:28:42Z&spr=https&sv=2022-11-02&sr=c&sig=kBFwlj5S5eqBEE32LM1EFebBY0W94uEFiwOXo3R0yt4%3D';
GO

 Step 3: Perform the database backup with the provided parameters
EXECUTE dba.dbo.DatabaseBackup
    @Databases = 'dba',                           Replace with your database name
    @URL = 'https://zagpebslab.blob.core.windows.net/sql-backups',   Azure Blob Storage URL
    @BackupType = 'Full',                         Full backup
    @CopyOnly = 'Y',                              Copy-only backup to avoid breaking backup chain
    @Compress = 'Y',                              Compress the backup
    @Verify = 'N';                                No verification of backup
GO

select name, database_id, state_desc
from sys.databases

SELECT
    database_id,
    key_algorithm,
    key_length,
    encryption_state_desc
 encryptor_type

FROM
 select * from  sys.dm_database_encryption_keys;

 select name, is_encrypted from sys.databases

 SELECT
    cert.name AS Certificate_Name,
    cert.subject AS Certificate_Subject,
    cert.issuer_name AS Issuer_Name,
    cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
FROM
    sys.certificates cert
WHERE
    cert.name LIKE 'TDE%';

 ALTER DATABASE dba1
SET ENCRYPTION off;
GO

Use dba1;
DROP DATABASE ENCRYPTION KEY;

USE [dba1]
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
GO

ALTER DATABASE dba1
SET ENCRYPTION ON