Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32

From version < 11.1 >

edited by Nikhil Singh
on 2026/07/03 08:30

To version < 8.1 >

edited by Nikhil Singh
on 2025/02/28 12:42

Change comment: There is no comment for this version

Raw
Rendered

Summary

Page properties (1 modified, 0 added, 0 removed)
Attachments (0 modified, 0 added, 1 removed)
- image-20250305152543-1.png

Details

Page properties

Content

@@ -1,33 +1,19 @@
--= **1. Steps for Implementing Transparent Data Encryption (TDE) FROM SQL Server** =
++= TDE(backup from smss to azure) =
--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.
++1 Create master key in master database (set master key)
--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.
++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)
--[[image:image-20250305152543-1.png]]
++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
--**~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.
++6 Create credential with SAS token
--**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.
--
  {{code language="sql"}}
  drop credential [https://zagpebslab.blob.core.windows.net/sql-backups]
  CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
@@ -38,106 +38,95 @@
  {{/code}}
--**~ 6. Use a Stored Procedure to Back Up to Azure Storage Account**
++7
  {{code language="sql"}}
  EXECUTE dba.dbo.DatabaseBackup
--@Databases = 'dba',
--@URL = 'https://zagpebslab.blob.core.windows.net/sql-backups',
--@BackupType = 'Full',
--@CopyOnly = 'Y',
--@Compress = 'Y',
--@Verify = 'N'
++        @Databases = @DatabaseName,
++        @URL = @BackupContainerURL,
++        @BackupType = 'Full',
++        @CopyOnly = 'Y',
++        @Compress = 'Y',
++        @Verify = 'N';
  {{/code}}
--===  ===
++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)
--=== Conclusion: Backup to Azure Storage Account with TDE Encrypted Databases ===
++We have unencrypted a database and backed it up from ssms to azure blob successfully
--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**.
++Conclusion: Can be done with and unencrypted DB but not with an Encrypted one>
--= **2. Enabling Transparent Data Encryption (TDE) Using Azure Key Vault** =
++= **Using TDE directly from azure portal** =
--**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 Go to azure key vault ( DemoTestRudi) to generate a key =====
--===== **1. Generate a Key in Azure Key Vault** =====
++- Go to keys and click generate
++- Name your key (mysqlmikey)
--**- Navigate to Azure Key Vault:**
++- Choose a key type (RSA)
--* Go to the **Azure Portal** and select **Key Vault** (DemoRudiTest).
++-Choose RSA key size (2048-bit)
--**- Generate a New Key:**
++Note:
--* Go to the **Keys** section and click **Generate** to create a new key.
++* 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.
--**- Configure Key Settings:**
++- Click create
--* **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:
++[[image:image-20250228120622-1.png||height="236" width="542"]]
--* 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:**
++===== 2 Enable TDE =====
--* Click **Create** to generate the key.
--===== **2. Enable TDE on the SQL Managed Instance** =====
++- Go to your Managed instance ( sqlmi-ebs-lab)
--=====  =====
++- Click on security and choose Transparent data encryption
--**- Navigate to Your Managed Instance:**
++- Select the type of managed key ( Customer-managed key0
--* Go to your **SQL Managed Instance** (sqlmi-ebs-lab).
++- Select the key from the key vault we generated in the key vault( mysqlmikey)
--**- Enable Transparent Data Encryption (TDE):**
++- Make the key the default TDE protector
--* Under **Security**, select **Transparent Data Encryption**.
++-Click save
--**- 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).
++[[image:image-20250228122106-2.png||height="19" width="241"]]
--**- Set the Key as Default TDE Protector:**
--* Make the key the **default TDE protector** for your instance.
++- TDE has now been enabled on the Managed instance
--**- Save Configuration:**
--* Click **Save** to apply the changes.
++Conclusion: All the databases have been encrypted by an asymmetric key. The key is the same for each database ( same encryption thumbprint).
--=== **Conclusion** ===
++We are now able to backup databases from SSMS to Azure storage.
--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.
++=== Backup specific databases using SQL server agent jobs ===
--=== **1. 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.
--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.
++The backup will be done daily at 3am. The backups will be backed up in the Azure storage account.
------
--==== **1. Create the Backup Script** ====
++1 Create the script
--====== **- Declaring Variables** ======
++=== **1. Declaring Variables** ===
  {{code language="sql"}}
  DECLARE @DatabaseName NVARCHAR(128)
@@ -151,7 +151,7 @@
  ----
--====== **2. Setting the Azure Blob Storage URL** ======
++=== **2. Setting the Azure Blob Storage URL** ===
  {{code language="sql"}}
  SET @BackupContainerURL = 'https://<your_storage_account>.blob.core.windows.net/sql-backups/'
@@ -160,7 +160,7 @@
  ----
--====== **3. Declaring the Cursor** ======
++=== **3. Declaring the Cursor** ===
  {{code language="sql"}}
@@ -177,7 +177,7 @@
  ----
--====== **4. Opening the Cursor** ======
++=== **4. Opening the Cursor** ===
  {{code language="sql"}}
  OPEN db_cursor
@@ -191,7 +191,7 @@
  ----
--====== **5. Looping Through Databases** ======
++=== **5. Looping Through Databases** ===
  {{code language="sql"}}
  -- Loop through each database and execute the stored procedure
@@ -229,7 +229,7 @@
  ----
--====== **6. Fetch the Next Database** ======
++=== **6. Fetch the Next Database** ===
  {{code language="sql"}}
      -- Fetch the next database in the cursor
@@ -243,7 +243,7 @@
  ----
--====== **7. Closing and Deallocating the Cursor** ======
++=== **7. Closing and Deallocating the Cursor** ===
  {{code language="sql"}}
  -- Close and deallocate the cursor to clean up resources
@@ -256,9 +256,8 @@
  * **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** ======
++===== 8. Full Script =====
  {{code language="sql"}}
@@ -297,21 +297,349 @@
  {{/code}}
++=== 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'>>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'>>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'>>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
++
++
++
++
++
++
++
++
++
++
++
++
++
++
++
++
++
++
++
++
++{{code language="sql"}}
++USE master;
++GO
++CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
++GO
++
++CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
++GO
++
++BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
++WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
++ENCRYPTION BY PASSWORD='*****')
++
++USE Everest_TDE_Master;
++GO
++CREATE DATABASE ENCRYPTION KEY
++WITH ALGORITHM = AES_256
++ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
++GO
++ALTER DATABASE Everest_TDE_Master
++SET ENCRYPTION ON;
++GO
++
++USE Everest_TDE_Master_Documents;
++GO
++CREATE DATABASE ENCRYPTION KEY
++WITH ALGORITHM = AES_256
++ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
++GO
++ALTER DATABASE Everest_TDE_Master_Documents
++SET ENCRYPTION ON;
++GO
++{{/code}}
++
++
++
++
++
++
++
++
++
++
++
++
++
++{{code language="sql"}}
++USE master;
++GO
++CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
++GO
++
++CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
++GO
++
++BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
++WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
++ENCRYPTION BY PASSWORD='*****')
++
++USE Everest_TDE_Master;
++GO
++CREATE DATABASE ENCRYPTION KEY
++WITH ALGORITHM = AES_256
++ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
++GO
++ALTER DATABASE Everest_TDE_Master
++SET ENCRYPTION ON;
++GO
++
++USE Everest_TDE_Master_Documents;
++GO
++CREATE DATABASE ENCRYPTION KEY
++WITH ALGORITHM = AES_256
++ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
++GO
++ALTER DATABASE Everest_TDE_Master_Documents
++SET ENCRYPTION ON;
++GO
++{{/code}}
++
++
++

image-20250305152543-1.png

Author

...	...	@@ -1,1 +1,0 @@
1		-XWiki.rudim

Size

...	...	@@ -1,1 +1,0 @@
1		-19.6 KB

Content

Applications

More applications

Need help?

If you need help with XWiki you can contact:

XWiki 14.10.21

Changes for page sql-tde

Summary

Details

Applications

Navigation

Need help?