Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 12.1
edited by Nikhil Singh
on 2026/07/03 08:32
To version < 9.1 >
edited by Nikhil Singh
on 2025/02/28 13:57
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -6,9 +6,6 @@
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 -
12 12  **~1. Create a Master Key in the master Database**
13 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 14  
... ... @@ -50,7 +50,7 @@
50 50  @Verify = 'N'
51 51  {{/code}}
52 52  
53 -=== ===
50 +=== ===
54 54  
55 55  === Conclusion: Backup to Azure Storage Account with TDE Encrypted Databases ===
56 56  
... ... @@ -75,10 +75,12 @@
75 75  
76 76  * Go to the **Azure Portal** and select **Key Vault** (DemoRudiTest).
77 77  
75 +
78 78  **- Generate a New Key:**
79 79  
80 80  * Go to the **Keys** section and click **Generate** to create a new key.
81 81  
80 +
82 82  **- Configure Key Settings:**
83 83  
84 84  * **Name the Key**: Choose a name for your key, e.g., mysqlmikey.
... ... @@ -91,35 +91,46 @@
91 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 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 93  
93 +
94 94  **- Create the Key:**
95 95  
96 96  * Click **Create** to generate the key.
97 97  
98 +
99 +
100 +
98 98  ===== **2. Enable TDE on the SQL Managed Instance** =====
99 99  
100 -===== =====
103 +===== =====
101 101  
102 102  **- Navigate to Your Managed Instance:**
103 103  
104 104  * Go to your **SQL Managed Instance** (sqlmi-ebs-lab).
105 105  
109 +
106 106  **- Enable Transparent Data Encryption (TDE):**
107 107  
108 108  * Under **Security**, select **Transparent Data Encryption**.
109 109  
114 +
110 110  **- Configure TDE with a Customer-Managed Key (CMK):**
111 111  
112 112  * Select **Customer-managed key** as the encryption type.
113 113  * Choose the key you created earlier from **Azure Key Vault** (mysqlmikey).
114 114  
120 +
121 +
115 115  **- Set the Key as Default TDE Protector:**
116 116  
117 117  * Make the key the **default TDE protector** for your instance.
118 118  
126 +
119 119  **- Save Configuration:**
120 120  
121 121  * Click **Save** to apply the changes.
122 122  
131 +
132 +
123 123  === **Conclusion** ===
124 124  
125 125  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**.
... ... @@ -256,6 +256,7 @@
256 256  * **CLOSE db_cursor**: This closes the cursor once the loop finishes processing all databases.
257 257  * **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.
258 258  
269 +(% class="wikigeneratedid" %)
259 259  ====== ======
260 260  
261 261  ====== **8. Full Script** ======
... ... @@ -299,8 +299,71 @@
299 299  
300 300  
301 301  
313 +=== **2. Set Up a New Job in SQL Server Agent** ===
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 +
317 +----
318 +
319 +====== **Step 1: Create a New Job** ======
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 +
325 +----
326 +
327 +====== **Step 2: Define Job Properties** ======
328 +
329 +In the **New Job** window, configure the job properties:
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 +
337 +----
338 +
339 +====== **Step 3: Create the Job Steps** ======
340 +
341 +The job will perform the backup through **Transact-SQL** (T-SQL) commands. Set up the following steps:
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 +
348 +----
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" %)
302 302  === ===
303 303  
377 +===== Note: We could use the job activity monitor to see if the job executed successfully. =====
304 304  
305 305  
306 306  
... ... @@ -313,5 +313,301 @@
313 313  
314 314  
315 315  
390 +1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
391 +GO
316 316  
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 +
317 317  
image-20250305152543-1.png
Author
... ... @@ -1,1 +1,0 @@
1 -XWiki.rudim
Size
... ... @@ -1,1 +1,0 @@
1 -19.6 KB
Content

Applications

Navigation

Need help?

If you need help with XWiki you can contact: