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 < 11.1 >
edited by Nikhil Singh
on 2026/07/03 08:30
>
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -6,6 +6,9 @@
6 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 7  
8 8  
9 +[[image:image-20250305152543-1.png]]
10 +
11 +
9 9  **~1. Create a Master Key in the master Database**
10 10  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.
11 11  
... ... @@ -47,7 +47,7 @@
47 47  @Verify = 'N'
48 48  {{/code}}
49 49  
50 -=== ===
53 +=== ===
51 51  
52 52  === Conclusion: Backup to Azure Storage Account with TDE Encrypted Databases ===
53 53  
... ... @@ -72,12 +72,10 @@
72 72  
73 73  * Go to the **Azure Portal** and select **Key Vault** (DemoRudiTest).
74 74  
75 -
76 76  **- Generate a New Key:**
77 77  
78 78  * Go to the **Keys** section and click **Generate** to create a new key.
79 79  
80 -
81 81  **- Configure Key Settings:**
82 82  
83 83  * **Name the Key**: Choose a name for your key, e.g., mysqlmikey.
... ... @@ -90,46 +90,35 @@
90 90  * **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.).
91 91  * 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.
92 92  
93 -
94 94  **- Create the Key:**
95 95  
96 96  * Click **Create** to generate the key.
97 97  
98 -
99 -
100 -
101 101  ===== **2. Enable TDE on the SQL Managed Instance** =====
102 102  
103 -===== =====
100 +===== =====
104 104  
105 105  **- Navigate to Your Managed Instance:**
106 106  
107 107  * Go to your **SQL Managed Instance** (sqlmi-ebs-lab).
108 108  
109 -
110 110  **- Enable Transparent Data Encryption (TDE):**
111 111  
112 112  * Under **Security**, select **Transparent Data Encryption**.
113 113  
114 -
115 115  **- Configure TDE with a Customer-Managed Key (CMK):**
116 116  
117 117  * Select **Customer-managed key** as the encryption type.
118 118  * Choose the key you created earlier from **Azure Key Vault** (mysqlmikey).
119 119  
120 -
121 -
122 122  **- Set the Key as Default TDE Protector:**
123 123  
124 124  * Make the key the **default TDE protector** for your instance.
125 125  
126 -
127 127  **- Save Configuration:**
128 128  
129 129  * Click **Save** to apply the changes.
130 130  
131 -
132 -
133 133  === **Conclusion** ===
134 134  
135 135  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**.
... ... @@ -266,7 +266,6 @@
266 266  * **CLOSE db_cursor**: This closes the cursor once the loop finishes processing all databases.
267 267  * **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.
268 268  
269 -(% class="wikigeneratedid" %)
270 270  ====== ======
271 271  
272 272  ====== **8. Full Script** ======
... ... @@ -310,378 +310,19 @@
310 310  
311 311  
312 312  
313 -=== **2. Set Up a New Job in SQL Server Agent** ===
302 +=== ===
314 314  
315 -To automate the backup process of the **DBA databases**, follow the steps below to create a new job in SQL Server Agent.
316 316  
317 -----
318 318  
319 -====== **Step 1: Create a New Job** ======
320 320  
321 -1. **Open SQL Server Management Studio (SSMS)**:
322 -1*. In the **Object Explorer**, expand the **SQL Server Agent** node.
323 -1*. Right-click on **Jobs** and select **New Job**.
324 324  
325 -----
326 326  
327 -====== **Step 2: Define Job Properties** ======
328 328  
329 -In the **New Job** window, configure the job properties:
330 330  
331 -* **General Tab**:
332 -** **Job Name**: Enter a descriptive name for the job, such as DBA Databases Backup.
333 -** **Owner**: Specify the job owner (ebssqladmin).
334 -** **Category**: Select Database Maintenance as the category for the job.
335 -** **Description**: Provide a brief description of the job (Automated backup of DBA databases to Azure Storage").
336 336  
337 -----
338 338  
339 -====== **Step 3: Create the Job Steps** ======
340 340  
341 -The job will perform the backup through **Transact-SQL** (T-SQL) commands. Set up the following steps:
342 342  
343 -1. **Step Name**: Enter a descriptive step name, such as Backup Only DBA Databases.
344 -1. **Type**: Select Transact-SQL Script (T-SQL).
345 -1. **Database**: Choose the master database, as the script will be run in the context of the master database.
346 -1. **Command**: Paste the backup script you created earlier, which automates the backup of the DBA databases.
347 347  
348 -----
349 349  
350 -====== **Step 4: Set the Job Schedule** ======
351 -
352 -Now, configure the schedule for the job to run daily at 3:00 AM:
353 -
354 -1. **Schedule Name**: Name the schedule (DBA Database Backup).
355 -1. **Schedule Type**: Choose Recurring for a regular schedule.
356 -1. (((
357 -**Frequency**:
358 -
359 -* **Occurs**: Select Daily.
360 -* **Recurs Every**: Set it to **1 day** to run the job every day at the same time.
361 -)))
362 -1. (((
363 -**Daily Frequency**:
364 -
365 -* Set the **time** for the backup to start, such as **3:00 AM**.
366 -)))
367 -
368 -----
369 -
370 -=== **Conclusion** ===
371 -
372 -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.
373 -
374 -(% class="wikigeneratedid" %)
375 -=== ===
376 -
377 -===== Note: We could use the job activity monitor to see if the job executed successfully. =====
378 -
379 -
380 -
381 -
382 -
383 -
384 -
385 -
386 -
387 -
388 -
389 -
390 -1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
391 -GO
392 -
393 -CREATE CERTIFICATE TDE_Certificate
394 -WITH SUBJECT = 'TDE Certificate';
395 -GO
396 -
397 -2 USE master;
398 -GO
399 -
400 -SELECT
401 - cert.name AS Certificate_Name,
402 - cert.subject AS Certificate_Subject,
403 - cert.issuer_name AS Issuer_Name,
404 - cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
405 -FROM
406 - sys.certificates cert
407 -WHERE
408 - cert.name LIKE 'TDE%';
409 -
410 -
411 -3 BACKUP CERTIFICATE TDE_Certificate
412 -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'
413 -WITH PRIVATE KEY (
414 - 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',
415 - ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'
416 -);
417 -GO
418 -
419 -
420 -USE Normal;
421 -GO
422 -
423 --- 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate
424 -CREATE DATABASE ENCRYPTION KEY
425 -WITH ALGORITHM = AES_256
426 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
427 -GO--
428 -
429 -USE [dba]
430 -GO
431 -
432 -CREATE DATABASE ENCRYPTION KEY
433 -WITH ALGORITHM = AES_256
434 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
435 -GO
436 -
437 -
438 -USE [dba1]
439 -GO
440 -
441 -CREATE DATABASE ENCRYPTION KEY
442 -WITH ALGORITHM = AES_256
443 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
444 -GO
445 -
446 -
447 -USE [dba2]
448 -GO
449 -
450 -CREATE DATABASE ENCRYPTION KEY
451 -WITH ALGORITHM = AES_256
452 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
453 -GO
454 -
455 -
456 -USE [dba3]
457 -GO
458 -
459 -CREATE DATABASE ENCRYPTION KEY
460 -WITH ALGORITHM = AES_256
461 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
462 -GO
463 -
464 -USE [xwiki]
465 -GO
466 -
467 -CREATE DATABASE ENCRYPTION KEY
468 -WITH ALGORITHM = AES_256
469 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
470 -GO
471 -
472 -
473 --- 7. Enable Transparent Data Encryption (TDE) on the database
474 -ALTER DATABASE dba
475 -SET ENCRYPTION ON;
476 -GO--
477 -
478 -
479 -select name, database_id, state_desc
480 -from sys.databases
481 -
482 -
483 -SELECT
484 - database_id,
485 - key_algorithm,
486 - key_length,
487 - encryption_state_desc
488 - encryptor_type
489 -
490 -FROM
491 - sys.dm_database_encryption_keys;
492 -
493 -
494 - Select * from sys.dm_database_encryption_keys
495 -
496 -
497 - BACKUP DATABASE [dba2]
498 -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']]
499 -With copy_only
500 -GO
501 -
502 -
503 -BACKUP DATABASE [dba2]
504 -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']],
505 -\\ COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
506 - COMPRESSION,  -- Optional: Compresses the backup to save storage space
507 - STATS = 10 -- Optional: Provides backup progress status
508 -GO--
509 -
510 -
511 -
512 --- Step 1: Drop the existing credential (if needed)
513 -DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
514 -GO--
515 -
516 --- Step 2: Create a new credential with the SAS token
517 -CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
518 -WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
519 -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';
520 -GO--
521 -
522 --- Step 3: Perform the database backup with the provided parameters
523 -EXECUTE dba.dbo.DatabaseBackup
524 - @Databases = 'dba',                          -- Replace with your database name
525 - @URL = '[[https:~~/~~/zagpebslab.blob.core.windows.net/sql-backups'>>https://zagpebslab.blob.core.windows.net/sql-backups']], -- Azure Blob Storage URL
526 - @BackupType = 'Full',                        -- Full backup
527 - @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
528 - @Compress = 'Y',                             -- Compress the backup
529 - @Verify = 'N'; -- No verification of backup
530 -GO--
531 -
532 -
533 -
534 -
535 -
536 -select name, database_id, state_desc
537 -from sys.databases
538 -
539 -
540 -SELECT
541 - database_id,
542 - key_algorithm,
543 - key_length,
544 - encryption_state_desc
545 - encryptor_type
546 -
547 -FROM
548 - select * from sys.dm_database_encryption_keys;
549 -
550 -
551 - select name, is_encrypted from sys.databases
552 -
553 -
554 - SELECT
555 - cert.name AS Certificate_Name,
556 - cert.subject AS Certificate_Subject,
557 - cert.issuer_name AS Issuer_Name,
558 - cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
559 -FROM
560 - sys.certificates cert
561 -WHERE
562 - cert.name LIKE 'TDE%';
563 -
564 - ALTER DATABASE dba1
565 -SET ENCRYPTION off;
566 -GO
567 -
568 -
569 -Use dba1;
570 -DROP DATABASE ENCRYPTION KEY;
571 -
572 -
573 -USE [dba1]
574 -GO
575 -
576 -CREATE DATABASE ENCRYPTION KEY
577 -WITH ALGORITHM = AES_256
578 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
579 -GO
580 -
581 -
582 -
583 -ALTER DATABASE dba1
584 -SET ENCRYPTION ON
585 -
586 -
587 -
588 -
589 -
590 -
591 -
592 -
593 -
594 -
595 -
596 -
597 -
598 -
599 -
600 -
601 -
602 -
603 -
604 -
605 -{{code language="sql"}}
606 -USE master;
607 -GO
608 -CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
609 -GO
610 -
611 -CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
612 -GO
613 -
614 -BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
615 -WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
616 -ENCRYPTION BY PASSWORD='*****')
617 -
618 -USE Everest_TDE_Master;
619 -GO
620 -CREATE DATABASE ENCRYPTION KEY
621 -WITH ALGORITHM = AES_256
622 -ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
623 -GO
624 -ALTER DATABASE Everest_TDE_Master
625 -SET ENCRYPTION ON;
626 -GO
627 -
628 -USE Everest_TDE_Master_Documents;
629 -GO
630 -CREATE DATABASE ENCRYPTION KEY
631 -WITH ALGORITHM = AES_256
632 -ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
633 -GO
634 -ALTER DATABASE Everest_TDE_Master_Documents
635 -SET ENCRYPTION ON;
636 -GO
637 -{{/code}}
638 -
639 -
640 -
641 -
642 -
643 -
644 -
645 -
646 -
647 -
648 -
649 -
650 -
651 -{{code language="sql"}}
652 -USE master;
653 -GO
654 -CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
655 -GO
656 -
657 -CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
658 -GO
659 -
660 -BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
661 -WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
662 -ENCRYPTION BY PASSWORD='*****')
663 -
664 -USE Everest_TDE_Master;
665 -GO
666 -CREATE DATABASE ENCRYPTION KEY
667 -WITH ALGORITHM = AES_256
668 -ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
669 -GO
670 -ALTER DATABASE Everest_TDE_Master
671 -SET ENCRYPTION ON;
672 -GO
673 -
674 -USE Everest_TDE_Master_Documents;
675 -GO
676 -CREATE DATABASE ENCRYPTION KEY
677 -WITH ALGORITHM = AES_256
678 -ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
679 -GO
680 -ALTER DATABASE Everest_TDE_Master_Documents
681 -SET ENCRYPTION ON;
682 -GO
683 -{{/code}}
684 -
685 -
686 -
687 687  
image-20250305152543-1.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.rudim
Size
... ... @@ -1,0 +1,1 @@
1 +19.6 KB
Content

Applications

Navigation

Need help?

If you need help with XWiki you can contact: