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 < 9.1 >

edited by Nikhil Singh
on 2025/02/28 13:57

Change comment: There is no comment for this version

Raw
Rendered

Summary

Page properties (1 modified, 0 added, 0 removed)

Details

Page properties

Content

@@ -1,19 +1,30 @@
--= 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
++**~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.
--5 Set encryption on for the database
++**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.
--6 Create credential with SAS token
++**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,96 +24,120 @@
  {{/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:**
++
++* 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.
--- Click create
++**- Create the Key:**
--[[image:image-20250228120622-1.png||height="236" width="542"]]
++* Click **Create** to generate the key.
--===== 2 Enable TDE =====
--- 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
---Click save
++**- Enable Transparent Data Encryption (TDE):**
++* Under **Security**, select **Transparent Data Encryption**.
--[[image:image-20250228122106-2.png||height="19" width="241"]]
++**- Configure TDE with a Customer-Managed Key (CMK):**
--- TDE has now been enabled on the Managed instance
++* Select **Customer-managed key** as the encryption type.
++* Choose the key you created earlier from **Azure Key Vault** (mysqlmikey).
--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.
++**- Set the Key as Default TDE Protector:**
++* Make the key the **default TDE protector** for your instance.
--=== Backup specific databases using SQL server agent jobs ===
++**- Save Configuration:**
++* Click **Save** to apply the changes.
--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.
++=== **Conclusion** ===
--1 Create the script
++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. Declaring Variables** ===
++
++=== **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** ======
++
  {{code language="sql"}}
  DECLARE @DatabaseName NVARCHAR(128)
  DECLARE @BackupContainerURL NVARCHAR(512)
@@ -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,10 @@
  * **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.
++(% class="wikigeneratedid" %)
++======  ======
--===== 8. Full Script =====
++====== **8. Full Script** ======
  {{code language="sql"}}
@@ -271,54 +271,85 @@
  {{/code}}
--=== 2. Set up a new job  ===
--- Go to SSMS and click on SQL Server Agent
++=== **2. Set Up a New Job in SQL Server Agent** ===
--  Drop down menu and left click jobs and select new job
++To automate the backup process of the **DBA databases**, follow the steps below to create a new job in SQL Server Agent.
++----
--======  General: ======
++====== **Step 1: Create a New Job** ======
--- Enter Job name (DBA databases backup)
++1. **Open SQL Server Management Studio (SSMS)**:
++1*. In the **Object Explorer**, expand the **SQL Server Agent** node.
++1*. Right-click on **Jobs** and select **New Job**.
---  Owner (ebssqladmin)
++----
---  Category (Database maintenance)
++====== **Step 2: Define Job Properties** ======
-- - Description (Description of the job)
++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").
--======  Steps: Create the steps for the job to follow  ======
++----
-- - Step name (Backup only DBA database)
++====== **Step 3: Create the Job Steps** ======
-- - Type (Transact-SQL script)
++The job will perform the backup through **Transact-SQL** (T-SQL) commands. Set up the following steps:
-- - Database (master)
++1. **Step Name**: Enter a descriptive step name, such as Backup Only DBA Databases.
++1. **Type**: Select Transact-SQL Script (T-SQL).
++1. **Database**: Choose the master database, as the script will be run in the context of the master database.
++1. **Command**: Paste the backup script you created earlier, which automates the backup of the DBA databases.
-- - Command (paste the script we created)
++----
++====== **Step 4: Set the Job Schedule** ======
-- Schedule: Create a schedule for the job to run
++Now, configure the schedule for the job to run daily at 3:00 AM:
--  - Name (DBA database backup)
++1. **Schedule Name**: Name the schedule (DBA Database Backup).
++1. **Schedule Type**: Choose Recurring for a regular schedule.
++1. (((
++**Frequency**:
--  - Schedule Type (recurring)
++* **Occurs**: Select Daily.
++* **Recurs Every**: Set it to **1 day** to run the job every day at the same time.
++)))
++1. (((
++**Daily Frequency**:
--  - Frequency (Occurs: Daily)
++* Set the **time** for the backup to start, such as **3:00 AM**.
++)))
--                      (Recurs every: 1 day(s))
++----
++=== **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.
++(% class="wikigeneratedid" %)
++===  ===
++===== Note: We could use the job activity monitor to see if the job executed successfully. =====
++
++
++
++
++
++
++
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
  GO

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?