Changes for page sql-tde

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

From version < 8.1 >

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

To version < 11.1 >

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

Change comment: There is no comment for this version

Raw
Rendered

Summary

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

Details

Page properties

Content

@@ -1,19 +1,33 @@
--= TDE(backup from smss to azure) =
++= **1. Steps for Implementing Transparent Data Encryption (TDE) FROM SQL Server** =
--1 Create master key in master database (set master key)
++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.
--2 Create TDE certificate (encrypted by MK)
++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.
--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
++[[image:image-20250305152543-1.png]]
--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]
@@ -24,95 +24,106 @@
  {{/code}}
--7
++**~ 6. Use a Stored Procedure to Back Up to Azure Storage Account**
  {{code language="sql"}}
  EXECUTE dba.dbo.DatabaseBackup
--        @Databases = @DatabaseName,
--        @URL = @BackupContainerURL,
--        @BackupType = 'Full',
--        @CopyOnly = 'Y',
--        @Compress = 'Y',
--        @Verify = 'N';
++@Databases = 'dba',
++@URL = 'https://zagpebslab.blob.core.windows.net/sql-backups',
++@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)
++===  ===
--We have unencrypted a database and backed it up from ssms to azure blob successfully
++=== 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**.
--Conclusion: Can be done with and unencrypted DB but not with an Encrypted one>
++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**.
--= **Using TDE directly from azure portal** =
++= **2. Enabling Transparent Data Encryption (TDE) Using Azure Key Vault** =
--===== 1 Go to azure key vault ( DemoTestRudi) to generate a key =====
++**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.
--- Go to keys and click generate
++===== **1. Generate a Key in Azure Key Vault** =====
--- Name your key (mysqlmikey)
--- Choose a key type (RSA)
++**- Navigate to Azure Key Vault:**
---Choose RSA key size (2048-bit)
++* Go to the **Azure Portal** and select **Key Vault** (DemoRudiTest).
--Note:
++**- Generate 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.
++* Go to the **Keys** section and click **Generate** to create a new key.
--- Click create
++**- 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)
--[[image:image-20250228120622-1.png||height="236" width="542"]]
++ 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.
--===== 2 Enable TDE =====
++**- Create the Key:**
++* Click **Create** to generate the key.
--- Go to your Managed instance ( sqlmi-ebs-lab)
++===== **2. Enable TDE on the SQL Managed Instance** =====
--- Click on security and choose Transparent data encryption
++=====  =====
--- Select the type of managed key ( Customer-managed key0
++**- Navigate to Your Managed Instance:**
--- Select the key from the key vault we generated in the key vault( mysqlmikey)
++* Go to your **SQL Managed Instance** (sqlmi-ebs-lab).
--- Make the key the default TDE protector
++**- Enable Transparent Data Encryption (TDE):**
---Click save
++* Under **Security**, select **Transparent Data Encryption**.
++**- Configure TDE with a Customer-Managed Key (CMK):**
--[[image:image-20250228122106-2.png||height="19" width="241"]]
++* 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:**
--- TDE has now been enabled on the Managed instance
++* Make the key the **default TDE protector** for your instance.
++**- Save Configuration:**
--Conclusion: All the databases have been encrypted by an asymmetric key. The key is the same for each database ( same encryption thumbprint).
++* Click **Save** to apply the changes.
--We are now able to backup databases from SSMS to Azure storage.
++=== **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.
--=== 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.
++=== **1. Backup Specific Databases Using SQL Server Agent Jobs** ===
--The backup will be done daily at 3am. The backups will be backed up in the Azure storage account.
++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 script
++==== **1. Create the Backup Script** ====
--=== **1. Declaring Variables** ===
++====== **- Declaring Variables** ======
  {{code language="sql"}}
  DECLARE @DatabaseName NVARCHAR(128)
@@ -126,7 +126,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/'
@@ -135,7 +135,7 @@
  ----
--=== **3. Declaring the Cursor** ===
++====== **3. Declaring the Cursor** ======
  {{code language="sql"}}
@@ -152,7 +152,7 @@
  ----
--=== **4. Opening the Cursor** ===
++====== **4. Opening the Cursor** ======
  {{code language="sql"}}
  OPEN db_cursor
@@ -166,7 +166,7 @@
  ----
--=== **5. Looping Through Databases** ===
++====== **5. Looping Through Databases** ======
  {{code language="sql"}}
  -- Loop through each database and execute the stored procedure
@@ -204,7 +204,7 @@
  ----
--=== **6. Fetch the Next Database** ===
++====== **6. Fetch the Next Database** ======
  {{code language="sql"}}
      -- Fetch the next database in the cursor
@@ -218,7 +218,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
@@ -231,8 +231,9 @@
  * **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"}}
@@ -271,349 +271,21 @@
  {{/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,0 +1,1 @@
	1	+XWiki.rudim

Size

...	...	@@ -1,0 +1,1 @@
	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?