Changes for page sql-tde

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

From version < 9.1 >

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

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)

Details

Page properties

Content

@@ -1,30 +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)
--**~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.
++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
--**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]
@@ -35,120 +35,96 @@
  {{/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)
++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:**
--* Click **Create** to generate the key.
++[[image:image-20250228120622-1.png||height="236" width="542"]]
++===== 2 Enable TDE =====
--===== **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)
++- Make the key the default TDE protector
--**- Enable Transparent Data Encryption (TDE):**
++-Click save
--* Under **Security**, select **Transparent Data Encryption**.
++[[image:image-20250228122106-2.png||height="19" width="241"]]
--**- 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).
++- 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).
--**- Set the Key as Default TDE Protector:**
++We are now able to backup databases from SSMS to Azure storage.
--* Make the key the **default TDE protector** for your instance.
--**- Save Configuration:**
++=== Backup specific databases using SQL server agent jobs ===
--* 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** ===
--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**.
++1 Create the script
--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)
@@ -161,7 +161,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/'
@@ -170,7 +170,7 @@
  ----
--====== **3. Declaring the Cursor** ======
++=== **3. Declaring the Cursor** ===
  {{code language="sql"}}
@@ -187,7 +187,7 @@
  ----
--====== **4. Opening the Cursor** ======
++=== **4. Opening the Cursor** ===
  {{code language="sql"}}
  OPEN db_cursor
@@ -201,7 +201,7 @@
  ----
--====== **5. Looping Through Databases** ======
++=== **5. Looping Through Databases** ===
  {{code language="sql"}}
  -- Loop through each database and execute the stored procedure
@@ -239,7 +239,7 @@
  ----
--====== **6. Fetch the Next Database** ======
++=== **6. Fetch the Next Database** ===
  {{code language="sql"}}
      -- Fetch the next database in the cursor
@@ -253,7 +253,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
@@ -266,10 +266,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.
--(% class="wikigeneratedid" %)
--======  ======
--====== **8. Full Script** ======
++===== 8. Full Script =====
  {{code language="sql"}}
@@ -308,85 +308,54 @@
  {{/code}}
++=== 2. Set up a new job  ===
--=== **2. Set Up a New Job in SQL Server Agent** ===
++- Go to SSMS and click on 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.
++  Drop down menu and left click jobs and select new job
------
--====== **Step 1: Create a New Job** ======
++======  General: ======
--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**.
++- Enter Job name (DBA databases backup)
------
++-  Owner (ebssqladmin)
--====== **Step 2: Define Job Properties** ======
++-  Category (Database maintenance)
--In the **New Job** window, configure the job properties:
++ - Description (Description of the job)
--* **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 3: Create the Job Steps** ======
++ - Step name (Backup only DBA database)
--The job will perform the backup through **Transact-SQL** (T-SQL) commands. Set up the following steps:
++ - Type (Transact-SQL script)
--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.
++ - Database (master)
------
++ - Command (paste the script we created)
--====== **Step 4: Set the Job Schedule** ======
--Now, configure the schedule for the job to run daily at 3:00 AM:
++ Schedule: Create a schedule for the job to run
--1. **Schedule Name**: Name the schedule (DBA Database Backup).
--1. **Schedule Type**: Choose Recurring for a regular schedule.
--1. (((
--**Frequency**:
++  - Name (DBA database backup)
--* **Occurs**: Select Daily.
--* **Recurs Every**: Set it to **1 day** to run the job every day at the same time.
--)))
--1. (((
--**Daily Frequency**:
++  - Schedule Type (recurring)
--* Set the **time** for the backup to start, such as **3:00 AM**.
--)))
++  - Frequency (Occurs: Daily)
------
++                      (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?