Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 10.1 >
edited by Rudi Marais
on 2025/03/05 13:24
To version < 3.2 >
edited by Nikhil Singh
on 2025/02/26 13:24
>
Change comment: There is no comment for this version

Summary

Details

Page properties
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.rudim
1 +XWiki.nikhils
Content
... ... @@ -1,384 +1,5 @@
1 -= **1. Steps for Implementing Transparent Data Encryption (TDE) FROM SQL Server** =
1 += TDE(1) =
2 2  
3 -
4 -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.
5 -
6 -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.
7 -
8 -
9 -[[image:image-20250305152543-1.png]]
10 -
11 -
12 -**~1. Create a Master Key in the master Database**
13 -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.
14 -
15 -
16 -**2. Create a TDE Certificate (Encrypted by the Master Key)**
17 -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.
18 -
19 -
20 -**3. Choose the Database to Create the Database Encryption Key (DEK)**
21 -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.
22 -
23 -
24 -**4. Enable Encryption for the Database**
25 -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.
26 -
27 -
28 -**5. Create a Credential with a SAS Token**
29 -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.
30 -
31 -{{code language="sql"}}
32 -drop credential [https://zagpebslab.blob.core.windows.net/sql-backups]
33 -CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
34 -WITH IDENTITY='SHARED ACCESS SIGNATURE'
35 -, 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'
36 -GO
37 -
38 -{{/code}}
39 -
40 -
41 -**~ 6. Use a Stored Procedure to Back Up to Azure Storage Account**
42 -
43 -{{code language="sql"}}
44 -EXECUTE dba.dbo.DatabaseBackup
45 -@Databases = 'dba',
46 -@URL = 'https://zagpebslab.blob.core.windows.net/sql-backups',
47 -@BackupType = 'Full',
48 -@CopyOnly = 'Y',
49 -@Compress = 'Y',
50 -@Verify = 'N'
51 -{{/code}}
52 -
53 -=== ===
54 -
55 -=== Conclusion: Backup to Azure Storage Account with TDE Encrypted Databases ===
56 -
57 -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**.
58 -
59 -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**.
60 -
61 -
62 -
63 -
64 -
65 -= **2. Enabling Transparent Data Encryption (TDE) Using Azure Key Vault** =
66 -
67 -
68 -**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.
69 -
70 -
71 -===== **1. Generate a Key in Azure Key Vault** =====
72 -
73 -
74 -**- Navigate to Azure Key Vault:**
75 -
76 -* Go to the **Azure Portal** and select **Key Vault** (DemoRudiTest).
77 -
78 -**- Generate a New Key:**
79 -
80 -* Go to the **Keys** section and click **Generate** to create a new key.
81 -
82 -**- Configure Key Settings:**
83 -
84 -* **Name the Key**: Choose a name for your key, e.g., mysqlmikey.
85 -* **Key Type**: Select **RSA** as the key type.
86 -* **RSA Key Size**: Choose an **RSA key size** of **2048-bit**. (optional)
87 -
88 - Note:
89 -
90 -* 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.
91 -* **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.).
92 -* 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.
93 -
94 -**- Create the Key:**
95 -
96 -* Click **Create** to generate the key.
97 -
98 -
99 -
100 -===== **2. Enable TDE on the SQL Managed Instance** =====
101 -
102 -===== =====
103 -
104 -**- Navigate to Your Managed Instance:**
105 -
106 -* Go to your **SQL Managed Instance** (sqlmi-ebs-lab).
107 -
108 -**- Enable Transparent Data Encryption (TDE):**
109 -
110 -* Under **Security**, select **Transparent Data Encryption**.
111 -
112 -**- Configure TDE with a Customer-Managed Key (CMK):**
113 -
114 -* Select **Customer-managed key** as the encryption type.
115 -* Choose the key you created earlier from **Azure Key Vault** (mysqlmikey).
116 -
117 -
118 -**- Set the Key as Default TDE Protector:**
119 -
120 -* Make the key the **default TDE protector** for your instance.
121 -
122 -**- Save Configuration:**
123 -
124 -* Click **Save** to apply the changes.
125 -
126 -
127 -=== **Conclusion** ===
128 -
129 -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**.
130 -
131 -This process ensures that your data is protected both at rest and during backup, offering enhanced security for your managed databases in the cloud.
132 -
133 -
134 -
135 -=== **1. Backup Specific Databases Using SQL Server Agent Jobs** ===
136 -
137 -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.
138 -
139 -----
140 -
141 -==== **1. Create the Backup Script** ====
142 -
143 -
144 -====== **- Declaring Variables** ======
145 -
146 -{{code language="sql"}}
147 -DECLARE @DatabaseName NVARCHAR(128)
148 -DECLARE @BackupContainerURL NVARCHAR(512)
149 -{{/code}}
150 -
151 -DECLARE @DatabaseName NVARCHAR(128) DECLARE @BackupContainerURL NVARCHAR(512)
152 -
153 -* **@DatabaseName**: A variable to hold the name of the database during each iteration of the loop.
154 -* **@BackupContainerURL**: A variable to store the URL of the Azure Blob Storage container where the database backups will be stored.
155 -
156 -----
157 -
158 -====== **2. Setting the Azure Blob Storage URL** ======
159 -
160 -{{code language="sql"}}
161 -SET @BackupContainerURL = 'https://<your_storage_account>.blob.core.windows.net/sql-backups/'
162 -{{/code}}
163 -
164 -
165 -----
166 -
167 -====== **3. Declaring the Cursor** ======
168 -
169 -
170 -{{code language="sql"}}
171 -DECLARE db_cursor CURSOR FOR
172 -SELECT name
173 -FROM sys.databases
174 -WHERE name LIKE 'dba%' -- Only pick databases that start with 'dba'
175 -{{/code}}
176 -
177 -* **Cursor Declaration**:
178 -** The cursor db_cursor is declared to loop through the list of databases in the sys.databases system view.
179 -** 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.
180 -** **sys.databases**: A system catalog view that contains information about each database in the SQL Server instance.
181 -
182 -----
183 -
184 -====== **4. Opening the Cursor** ======
185 -
186 -{{code language="sql"}}
187 -OPEN db_cursor
188 -FETCH NEXT FROM db_cursor INTO @DatabaseName
189 -{{/code}}
190 -
191 -OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
192 -
193 -* **OPEN db_cursor**: This opens the cursor for reading the results.
194 -* **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.
195 -
196 -----
197 -
198 -====== **5. Looping Through Databases** ======
199 -
200 -{{code language="sql"}}
201 --- Loop through each database and execute the stored procedure
202 -WHILE @@FETCH_STATUS = 0
203 -BEGIN
204 - -- Print current database for logging/debugging
205 - PRINT 'Backing up database: ' + @DatabaseName
206 -
207 - -- Execute the DatabaseBackup stored procedure for each database
208 - EXECUTE dba.dbo.DatabaseBackup
209 - @Databases = @DatabaseName, -- Specify the current database
210 - @URL = @BackupContainerURL, -- Azure Blob Storage URL
211 - @BackupType = 'Full', -- Full backup type
212 - @CopyOnly = 'Y', -- Use copy-only backup (this doesn’t affect the transaction log chain)
213 - @Compress = 'Y', -- Enable compression
214 - @Verify = 'N'; -- Skip verification after backup
215 -
216 -{{/code}}
217 -
218 -
219 -* **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.
220 -* **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.
221 -* (((
222 -**EXECUTE dba.dbo.DatabaseBackup**:
223 -
224 -* This calls a stored procedure named dba.dbo.DatabaseBackup for each database.
225 -* The parameters passed to the stored procedure:
226 -** **@Databases = @DatabaseName**: Specifies which database to back up (the current database being processed).
227 -** **@URL = @BackupContainerURL**: Specifies the destination Azure Blob Storage URL.
228 -** **@BackupType = 'Full'**: Specifies the backup type, which is a **Full** backup (this means all data in the database is backed up).
229 -** **@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.
230 -** **@Compress = 'Y'**: Enables compression of the backup, reducing the size of the backup file.
231 -** **@Verify = 'N'**: Specifies that the backup verification step should be skipped. (Verification can be added if needed for additional safety.)
232 -)))
233 -
234 -----
235 -
236 -====== **6. Fetch the Next Database** ======
237 -
238 -{{code language="sql"}}
239 - -- Fetch the next database in the cursor
240 - FETCH NEXT FROM db_cursor INTO @DatabaseName
241 -
242 -{{/code}}
243 -
244 -~-~- Fetch the next database in the cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
245 -
246 -* **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.
247 -
248 -----
249 -
250 -====== **7. Closing and Deallocating the Cursor** ======
251 -
252 -{{code language="sql"}}
253 --- Close and deallocate the cursor to clean up resources
254 -CLOSE db_cursor
255 -DEALLOCATE db_cursor
256 -
257 -
258 -{{/code}}
259 -
260 -* **CLOSE db_cursor**: This closes the cursor once the loop finishes processing all databases.
261 -* **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.
262 -
263 -====== ======
264 -
265 -====== **8. Full Script** ======
266 -
267 -
268 -{{code language="sql"}}
269 -DECLARE @DatabaseName NVARCHAR(128)
270 -DECLARE @BackupContainerURL NVARCHAR(512)
271 -
272 -SET @BackupContainerURL = 'https://zagpebslab.blob.core.windows.net/sql-backups'
273 -
274 -DECLARE db_cursor CURSOR FOR
275 -SELECT name
276 -FROM sys.databases
277 -WHERE name LIKE 'dba%'
278 -
279 -OPEN db_cursor
280 -FETCH NEXT FROM db_cursor INTO @DatabaseName
281 -
282 -WHILE @@FETCH_STATUS = 0
283 -BEGIN
284 -
285 -PRINT 'Backing up database: ' + @DatabaseName
286 -
287 -EXECUTE dba.dbo.DatabaseBackup
288 - @Databases = @DatabaseName,
289 - @URL = @BackupContainerURL,
290 - @BackupType = 'Full',
291 - @CopyOnly = 'Y',
292 - @Compress = 'Y',
293 - @Verify = 'N';
294 -
295 -FETCH NEXT FROM db_cursor INTO @DatabaseName
296 -END
297 -
298 -CLOSE db_cursor
299 -DEALLOCATE db_cursor
300 -
301 -{{/code}}
302 -
303 -
304 -
305 -
306 -=== **2. Set Up a New Job in SQL Server Agent** ===
307 -
308 -To automate the backup process of the **DBA databases**, follow the steps below to create a new job in SQL Server Agent.
309 -
310 -----
311 -
312 -====== **Step 1: Create a New Job** ======
313 -
314 -1. **Open SQL Server Management Studio (SSMS)**:
315 -1*. In the **Object Explorer**, expand the **SQL Server Agent** node.
316 -1*. Right-click on **Jobs** and select **New Job**.
317 -
318 -----
319 -
320 -====== **Step 2: Define Job Properties** ======
321 -
322 -In the **New Job** window, configure the job properties:
323 -
324 -* **General Tab**:
325 -** **Job Name**: Enter a descriptive name for the job, such as DBA Databases Backup.
326 -** **Owner**: Specify the job owner (ebssqladmin).
327 -** **Category**: Select Database Maintenance as the category for the job.
328 -** **Description**: Provide a brief description of the job (Automated backup of DBA databases to Azure Storage").
329 -
330 -----
331 -
332 -====== **Step 3: Create the Job Steps** ======
333 -
334 -The job will perform the backup through **Transact-SQL** (T-SQL) commands. Set up the following steps:
335 -
336 -1. **Step Name**: Enter a descriptive step name, such as Backup Only DBA Databases.
337 -1. **Type**: Select Transact-SQL Script (T-SQL).
338 -1. **Database**: Choose the master database, as the script will be run in the context of the master database.
339 -1. **Command**: Paste the backup script you created earlier, which automates the backup of the DBA databases.
340 -
341 -----
342 -
343 -====== **Step 4: Set the Job Schedule** ======
344 -
345 -Now, configure the schedule for the job to run daily at 3:00 AM:
346 -
347 -1. **Schedule Name**: Name the schedule (DBA Database Backup).
348 -1. **Schedule Type**: Choose Recurring for a regular schedule.
349 -1. (((
350 -**Frequency**:
351 -
352 -* **Occurs**: Select Daily.
353 -* **Recurs Every**: Set it to **1 day** to run the job every day at the same time.
354 -)))
355 -1. (((
356 -**Daily Frequency**:
357 -
358 -* Set the **time** for the backup to start, such as **3:00 AM**.
359 -)))
360 -
361 -----
362 -
363 -=== **Conclusion** ===
364 -
365 -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.
366 -
367 -=== ===
368 -
369 -===== Note: We could use the job activity monitor to see if the job executed successfully. =====
370 -
371 -
372 -
373 -
374 -
375 -
376 -
377 -
378 -
379 -
380 -
381 -
382 382  1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
383 383  GO
384 384  
... ... @@ -389,18 +389,18 @@
389 389  2 USE master;
390 390  GO
391 391  
392 -SELECT
13 +SELECT
393 393   cert.name AS Certificate_Name,
394 394   cert.subject AS Certificate_Subject,
395 395   cert.issuer_name AS Issuer_Name,
396 396   cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
397 -FROM
18 +FROM
398 398   sys.certificates cert
399 -WHERE
20 +WHERE
400 400   cert.name LIKE 'TDE%';
401 401  
402 402  
403 -3 BACKUP CERTIFICATE TDE_Certificate
24 +3 BACKUP CERTIFICATE TDE_Certificate
404 404  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'
405 405  WITH PRIVATE KEY (
406 406   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',
... ... @@ -416,7 +416,7 @@
416 416  CREATE DATABASE ENCRYPTION KEY
417 417  WITH ALGORITHM = AES_256
418 418  ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
419 -GO--
40 +GO
420 420  
421 421  USE [dba]
422 422  GO
... ... @@ -465,7 +465,7 @@
465 465  -- 7. Enable Transparent Data Encryption (TDE) on the database
466 466  ALTER DATABASE dba
467 467  SET ENCRYPTION ON;
468 -GO--
89 +GO
469 469  
470 470  
471 471  select name, database_id, state_desc
... ... @@ -472,14 +472,14 @@
472 472  from sys.databases
473 473  
474 474  
475 -SELECT
96 +SELECT
476 476   database_id,
477 477   key_algorithm,
478 478   key_length,
479 479   encryption_state_desc
480 - encryptor_type
101 + encryptor_type
481 481  
482 -FROM
103 +FROM
483 483   sys.dm_database_encryption_keys;
484 484  
485 485  
... ... @@ -486,40 +486,41 @@
486 486   Select * from sys.dm_database_encryption_keys
487 487  
488 488  
489 - BACKUP DATABASE [dba2]
490 -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']]
110 + BACKUP DATABASE [dba2]
111 +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'
491 491  With copy_only
492 492  GO
493 493  
494 494  
495 495  BACKUP DATABASE [dba2]
496 -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']],
497 -\\ COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
498 - COMPRESSION,  -- Optional: Compresses the backup to save storage space
117 +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',
118 +
119 + COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
120 + COMPRESSION, -- Optional: Compresses the backup to save storage space
499 499   STATS = 10 -- Optional: Provides backup progress status
500 -GO--
122 +GO
501 501  
502 502  
503 503  
504 504  -- Step 1: Drop the existing credential (if needed)
505 505  DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
506 -GO--
128 +GO
507 507  
508 508  -- Step 2: Create a new credential with the SAS token
509 509  CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
510 510  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
511 511  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';
512 -GO--
134 +GO
513 513  
514 514  -- Step 3: Perform the database backup with the provided parameters
515 515  EXECUTE dba.dbo.DatabaseBackup
516 - @Databases = 'dba',                          -- Replace with your database name
517 - @URL = '[[https:~~/~~/zagpebslab.blob.core.windows.net/sql-backups'>>https://zagpebslab.blob.core.windows.net/sql-backups']], -- Azure Blob Storage URL
518 - @BackupType = 'Full',                        -- Full backup
138 + @Databases = 'dba', -- Replace with your database name
139 + @URL = 'https://zagpebslab.blob.core.windows.net/sql-backups', -- Azure Blob Storage URL
140 + @BackupType = 'Full', -- Full backup
519 519   @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
520 - @Compress = 'Y',                             -- Compress the backup
142 + @Compress = 'Y', -- Compress the backup
521 521   @Verify = 'N'; -- No verification of backup
522 -GO--
144 +GO
523 523  
524 524  
525 525  
... ... @@ -529,14 +529,14 @@
529 529  from sys.databases
530 530  
531 531  
532 -SELECT
154 +SELECT
533 533   database_id,
534 534   key_algorithm,
535 535   key_length,
536 536   encryption_state_desc
537 - encryptor_type
159 + encryptor_type
538 538  
539 -FROM
161 +FROM
540 540   select * from sys.dm_database_encryption_keys;
541 541  
542 542  
... ... @@ -543,17 +543,17 @@
543 543   select name, is_encrypted from sys.databases
544 544  
545 545  
546 - SELECT
168 + SELECT
547 547   cert.name AS Certificate_Name,
548 548   cert.subject AS Certificate_Subject,
549 549   cert.issuer_name AS Issuer_Name,
550 550   cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
551 -FROM
173 +FROM
552 552   sys.certificates cert
553 -WHERE
175 +WHERE
554 554   cert.name LIKE 'TDE%';
555 555  
556 - ALTER DATABASE dba1
178 + ALTER DATABASE dba1
557 557  SET ENCRYPTION off;
558 558  GO
559 559  
... ... @@ -594,39 +594,10 @@
594 594  
595 595  
596 596  
597 -{{code language="sql"}}
598 -USE master;
599 -GO
600 -CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
601 -GO
602 602  
603 -CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
604 -GO
605 605  
606 -BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
607 -WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
608 -ENCRYPTION BY PASSWORD='*****')
609 609  
610 -USE Everest_TDE_Master;
611 -GO
612 -CREATE DATABASE ENCRYPTION KEY
613 -WITH ALGORITHM = AES_256
614 -ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
615 -GO
616 -ALTER DATABASE Everest_TDE_Master
617 -SET ENCRYPTION ON;
618 -GO
619 619  
620 -USE Everest_TDE_Master_Documents;
621 -GO
622 -CREATE DATABASE ENCRYPTION KEY
623 -WITH ALGORITHM = AES_256
624 -ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
625 -GO
626 -ALTER DATABASE Everest_TDE_Master_Documents
627 -SET ENCRYPTION ON;
628 -GO
629 -{{/code}}
630 630  
631 631  
632 632  
... ... @@ -639,7 +639,6 @@
639 639  
640 640  
641 641  
642 -
643 643  {{code language="sql"}}
644 644  USE master;
645 645  GO
... ... @@ -673,6 +673,3 @@
673 673  SET ENCRYPTION ON;
674 674  GO
675 675  {{/code}}
676 -
677 -
678 -
image-20250305152543-1.png
Author
... ... @@ -1,1 +1,0 @@
1 -XWiki.rudim
Size
... ... @@ -1,1 +1,0 @@
1 -19.6 KB
Content
XWiki.XWikiComments[0]
Author
... ... @@ -1,1 +1,0 @@
1 -XWiki.rudim
Comment
... ... @@ -1,1 +1,0 @@
1 -put the sql code in code tags to start with otherwise its very hard to read
Date
... ... @@ -1,1 +1,0 @@
1 -2025-02-26 16:00:04.223

Applications

Navigation

Need help?

If you need help with XWiki you can contact: