1. Steps for Implementing Transparent Data Encryption (TDE) FROM SQL Server

This document outlines the process of encrypting SQL Server databases using Transparent Data Encryption (TDE) and backing them up to Azure Storage. TDE ensures data at rest is encrypted, leveraging a Master Key (MK), TDE Certificate, and Database Encryption Key (DEK) for encryption.

The process also includes creating a credential for secure backup to Azure Blob Storage, providing a scalable and secure solution for storing encrypted databases in the cloud.

1. Create a Master Key in the master Database
The first step is to create a Master Key in the master database. This key will be used to encrypt other cryptographic objects, such as certificates and symmetric keys, within SQL Server.

2. Create a TDE Certificate (Encrypted by the Master Key)
Next, generate a TDE certificate that will be used to encrypt the Database Encryption Key (DEK). This certificate is encrypted by the Master Key created in step 1, providing an additional layer of security.

3. Choose the Database to Create the Database Encryption Key (DEK)
For the selected database, create the Database Encryption Key (DEK). The DEK will be encrypted by the TDE certificate and will use the AES-256 encryption algorithm to ensure data is securely encrypted at rest.

4. Enable Encryption for the Database
Once the DEK has been created, enable TDE for the database. This ensures that all data written to the database is automatically encrypted at rest, providing full protection for sensitive information.

5. Create a Credential with a SAS Token
Finally, create a credential that allows SQL Server to access Azure Blob Storage. This credential is created using a Shared Access Signature (SAS) token, which ensures secure and authenticated access to the storage account for backup purposes.

 6. Use a Stored Procedure to Back Up to Azure Storage Account

Conclusion: Backup to Azure Storage Account with TDE Encrypted Databases

The attempt to back up an encrypted database (with Transparent Data Encryption - TDE) from SQL Server Management Studio (SSMS) to an Azure Storage Account was unsuccessful. This issue arises because Azure does not permit TDE-encrypted databases to be backed up directly to Azure Storage using SSMS.

However, after decrypting the database, we were able to successfully back it up to the Azure Storage Account via SSMS. This confirms that backups can be performed on an unencrypted database, but not on an encrypted database.

2. Enabling Transparent Data Encryption (TDE) Using Azure Key Vault

Introduction: This document outlines the process of enabling Transparent Data Encryption (TDE) on an Azure SQL Managed Instance (SQL MI) using a Customer-Managed Key (CMK) stored in Azure Key Vault. The encryption is managed using an asymmetric key from Azure Key Vault, ensuring that all data within the SQL Managed Instance is encrypted using a strong encryption algorithm.

1. Generate a Key in Azure Key Vault

- Navigate to Azure Key Vault:

• Go to the Azure Portal and select Key Vault (DemoRudiTest).

- Generate a New Key:

• Go to the Keys section and click Generate to create a new key.

- Configure Key Settings:

• Name the Key: Choose a name for your key, e.g., mysqlmikey.
• Key Type: Select RSA as the key type.
• RSA Key Size: Choose an RSA key size of 2048-bit. (optional)

 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.

- Create the Key:

• Click Create to generate the key.

2. Enable TDE on the SQL Managed Instance

- Navigate to Your Managed Instance:

• Go to your SQL Managed Instance (sqlmi-ebs-lab).

- Enable Transparent Data Encryption (TDE):

• Under Security, select Transparent Data Encryption.

- Configure TDE with a Customer-Managed Key (CMK):

• Select Customer-managed key as the encryption type.
• Choose the key you created earlier from Azure Key Vault (mysqlmikey).

- Set the Key as Default TDE Protector:

• Make the key the default TDE protector for your instance.

- Save Configuration:

• Click Save to apply the changes.

Conclusion

After following these steps, TDE has been successfully enabled on your SQL Managed Instance using an asymmetric key stored in Azure Key Vault. All databases within the instance are now encrypted using the same encryption key (identified by the same encryption thumbprint). You can now securely back up these encrypted databases from SSMS to Azure Storage.

This process ensures that your data is protected both at rest and during backup, offering enhanced security for your managed databases in the cloud.

1. Backup Specific Databases Using SQL Server Agent Jobs

This document describes the process of creating an automated SQL Server Agent Job to back up only the DBA databases in a SQL Server instance. The backups will occur daily at 3:00 AM and will be stored in an Azure Storage Account for secure and reliable cloud storage.

1. Create the Backup Script

- 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 in SQL Server Agent

To automate the backup process of the DBA databases, follow the steps below to create a new job in SQL Server Agent.

Step 1: Create a New Job

1. Open SQL Server Management Studio (SSMS):
   • In the Object Explorer, expand the SQL Server Agent node.
   • Right-click on Jobs and select New Job.

Step 2: Define Job Properties

In the New Job window, configure the job properties:

• General Tab:
  • Job Name: Enter a descriptive name for the job, such as DBA Databases Backup.
  • Owner: Specify the job owner (ebssqladmin).
  • Category: Select Database Maintenance as the category for the job.
  • Description: Provide a brief description of the job (Automated backup of DBA databases to Azure Storage").

Step 3: Create the Job Steps

The job will perform the backup through Transact-SQL (T-SQL) commands. Set up the following steps:

1. Step Name: Enter a descriptive step name, such as Backup Only DBA Databases.
2. Type: Select Transact-SQL Script (T-SQL).
3. Database: Choose the master database, as the script will be run in the context of the master database.
4. Command: Paste the backup script you created earlier, which automates the backup of the DBA databases.

Step 4: Set the Job Schedule

Now, configure the schedule for the job to run daily at 3:00 AM:

1. Schedule Name: Name the schedule (DBA Database Backup).
2. Schedule Type: Choose Recurring for a regular schedule.

3. Frequency:

   • Occurs: Select Daily.
   • Recurs Every: Set it to 1 day to run the job every day at the same time.

4. Daily Frequency:

   • Set the time for the backup to start, such as 3:00 AM.

Conclusion

The SQL Server Agent job is now configured to automatically back up the DBA databases daily to an Azure Storage Account. The job will only back up databases that start with 'dba', and it handles encrypted databases with Transparent Data Encryption (TDE). This ensures secure, encrypted backups are stored in the cloud without requiring manual intervention.

Note: We could use the job activity monitor to see if the job executed successfully.

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