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 credential with SAS token

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. 

Backup specific databases using SQL server agent jobs

we are creating a job to automatically backup only the DBA databases in the instance via the SQL server agent.

The backup will be done daily at 3am. The backups will be backed up in the Azure storage account.

1 Create the script 

1. Declaring Variables

DECLARE @DatabaseName NVARCHAR(128) DECLARE @BackupContainerURL NVARCHAR(512)

• @DatabaseName: A variable to hold the name of the database during each iteration of the loop.
• @BackupContainerURL: A variable to store the URL of the Azure Blob Storage container where the database backups will be stored.

2. Setting the Azure Blob Storage URL

3. Declaring the Cursor

• Cursor Declaration:
  • The cursor db_cursor is declared to loop through the list of databases in the sys.databases system view.
  • The WHERE name LIKE 'dba%' clause ensures that only databases with names starting with dba will be processed. For example, databases like dbaTest, dbaProd, etc., will be included in the loop.
  • sys.databases: A system catalog view that contains information about each database in the SQL Server instance.

4. Opening the Cursor

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DatabaseName

• OPEN db_cursor: This opens the cursor for reading the results.
• FETCH NEXT: The FETCH NEXT statement retrieves the first database name that meets the LIKE 'dba%' condition and stores it in the @DatabaseName variable. This will be used in the next step where the backup procedure is executed.

5. Looping Through Databases

• WHILE @@FETCH_STATUS = 0: The loop will continue as long as the FETCH command successfully retrieves a database name. @@FETCH_STATUS is a system function that returns 0 if the fetch operation is successful.
• PRINT: This line prints the name of the database that is currently being backed up. It helps with logging or debugging purposes, as you can track which database is being processed at any given time.

• EXECUTE dba.dbo.DatabaseBackup:

  • This calls a stored procedure named dba.dbo.DatabaseBackup for each database.
  • The parameters passed to the stored procedure:
    • @Databases = @DatabaseName: Specifies which database to back up (the current database being processed).
    • @URL = @BackupContainerURL: Specifies the destination Azure Blob Storage URL.
    • @BackupType = 'Full': Specifies the backup type, which is a Full backup (this means all data in the database is backed up).
    • @CopyOnly = 'Y': Specifies that this is a copy-only backup. This ensures that the backup does not affect the transaction log chain and does not interfere with regular backups.
    • @Compress = 'Y': Enables compression of the backup, reducing the size of the backup file.
    • @Verify = 'N': Specifies that the backup verification step should be skipped. (Verification can be added if needed for additional safety.)

6. Fetch the Next Database

-- Fetch the next database in the cursor FETCH NEXT FROM db_cursor INTO @DatabaseName

• FETCH NEXT: After executing the backup for the current database, this statement retrieves the next database from the cursor and stores it in the @DatabaseName variable. The loop will continue until all matching databases have been processed.

7. Closing and Deallocating the Cursor

• CLOSE db_cursor: This closes the cursor once the loop finishes processing all databases.
• DEALLOCATE db_cursor: This deallocates the cursor, freeing up any resources used by the cursor. It’s a good practice to always deallocate cursors to avoid resource leaks.

8. Full Script

2. Set up a new job

- Go to SSMS and click on SQL Server Agent 

  Drop down menu and left click jobs and select new job

General:

- Enter Job name (DBA databases backup)

-  Owner (ebssqladmin)

-  Category (Database maintenance)

 - Description (Description of the job)

Steps: Create the steps for the job to follow

 - Step name (Backup only DBA database)

 - Type (Transact-SQL script)

 - Database (master)

 - Command (paste the script we created)

 Schedule: Create a schedule for the job to run 

  - Name (DBA database backup)

  - Schedule Type (recurring)

  - Frequency (Occurs: Daily)

                      (Recurs every: 1 day(s))

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