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 < 12.1
edited by Nikhil Singh
on 2026/07/03 08:32
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
... ... @@ -95,8 +95,6 @@
95 95  
96 96  * Click **Create** to generate the key.
97 97  
98 -
99 -
100 100  ===== **2. Enable TDE on the SQL Managed Instance** =====
101 101  
102 102  ===== =====
... ... @@ -114,7 +114,6 @@
114 114  * Select **Customer-managed key** as the encryption type.
115 115  * Choose the key you created earlier from **Azure Key Vault** (mysqlmikey).
116 116  
117 -
118 118  **- Set the Key as Default TDE Protector:**
119 119  
120 120  * Make the key the **default TDE protector** for your instance.
... ... @@ -123,7 +123,6 @@
123 123  
124 124  * Click **Save** to apply the changes.
125 125  
126 -
127 127  === **Conclusion** ===
128 128  
129 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**.
... ... @@ -303,70 +303,8 @@
303 303  
304 304  
305 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 367  === ===
368 368  
369 -===== Note: We could use the job activity monitor to see if the job executed successfully. =====
370 370  
371 371  
372 372  
... ... @@ -379,300 +379,5 @@
379 379  
380 380  
381 381  
382 -1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
383 -GO
384 384  
385 -CREATE CERTIFICATE TDE_Certificate
386 -WITH SUBJECT = 'TDE Certificate';
387 -GO
388 -
389 -2 USE master;
390 -GO
391 -
392 -SELECT
393 - cert.name AS Certificate_Name,
394 - cert.subject AS Certificate_Subject,
395 - cert.issuer_name AS Issuer_Name,
396 - cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
397 -FROM
398 - sys.certificates cert
399 -WHERE
400 - cert.name LIKE 'TDE%';
401 -
402 -
403 -3 BACKUP CERTIFICATE TDE_Certificate
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 -WITH PRIVATE KEY (
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',
407 - ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'
408 -);
409 -GO
410 -
411 -
412 -USE Normal;
413 -GO
414 -
415 --- 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate
416 -CREATE DATABASE ENCRYPTION KEY
417 -WITH ALGORITHM = AES_256
418 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
419 -GO--
420 -
421 -USE [dba]
422 -GO
423 -
424 -CREATE DATABASE ENCRYPTION KEY
425 -WITH ALGORITHM = AES_256
426 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
427 -GO
428 -
429 -
430 -USE [dba1]
431 -GO
432 -
433 -CREATE DATABASE ENCRYPTION KEY
434 -WITH ALGORITHM = AES_256
435 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
436 -GO
437 -
438 -
439 -USE [dba2]
440 -GO
441 -
442 -CREATE DATABASE ENCRYPTION KEY
443 -WITH ALGORITHM = AES_256
444 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
445 -GO
446 -
447 -
448 -USE [dba3]
449 -GO
450 -
451 -CREATE DATABASE ENCRYPTION KEY
452 -WITH ALGORITHM = AES_256
453 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
454 -GO
455 -
456 -USE [xwiki]
457 -GO
458 -
459 -CREATE DATABASE ENCRYPTION KEY
460 -WITH ALGORITHM = AES_256
461 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
462 -GO
463 -
464 -
465 --- 7. Enable Transparent Data Encryption (TDE) on the database
466 -ALTER DATABASE dba
467 -SET ENCRYPTION ON;
468 -GO--
469 -
470 -
471 -select name, database_id, state_desc
472 -from sys.databases
473 -
474 -
475 -SELECT
476 - database_id,
477 - key_algorithm,
478 - key_length,
479 - encryption_state_desc
480 - encryptor_type
481 -
482 -FROM
483 - sys.dm_database_encryption_keys;
484 -
485 -
486 - Select * from sys.dm_database_encryption_keys
487 -
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']]
491 -With copy_only
492 -GO
493 -
494 -
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
499 - STATS = 10 -- Optional: Provides backup progress status
500 -GO--
501 -
502 -
503 -
504 --- Step 1: Drop the existing credential (if needed)
505 -DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
506 -GO--
507 -
508 --- Step 2: Create a new credential with the SAS token
509 -CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
510 -WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
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--
513 -
514 --- Step 3: Perform the database backup with the provided parameters
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
519 - @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
520 - @Compress = 'Y',                             -- Compress the backup
521 - @Verify = 'N'; -- No verification of backup
522 -GO--
523 -
524 -
525 -
526 -
527 -
528 -select name, database_id, state_desc
529 -from sys.databases
530 -
531 -
532 -SELECT
533 - database_id,
534 - key_algorithm,
535 - key_length,
536 - encryption_state_desc
537 - encryptor_type
538 -
539 -FROM
540 - select * from sys.dm_database_encryption_keys;
541 -
542 -
543 - select name, is_encrypted from sys.databases
544 -
545 -
546 - SELECT
547 - cert.name AS Certificate_Name,
548 - cert.subject AS Certificate_Subject,
549 - cert.issuer_name AS Issuer_Name,
550 - cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
551 -FROM
552 - sys.certificates cert
553 -WHERE
554 - cert.name LIKE 'TDE%';
555 -
556 - ALTER DATABASE dba1
557 -SET ENCRYPTION off;
558 -GO
559 -
560 -
561 -Use dba1;
562 -DROP DATABASE ENCRYPTION KEY;
563 -
564 -
565 -USE [dba1]
566 -GO
567 -
568 -CREATE DATABASE ENCRYPTION KEY
569 -WITH ALGORITHM = AES_256
570 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
571 -GO
572 -
573 -
574 -
575 -ALTER DATABASE dba1
576 -SET ENCRYPTION ON
577 -
578 -
579 -
580 -
581 -
582 -
583 -
584 -
585 -
586 -
587 -
588 -
589 -
590 -
591 -
592 -
593 -
594 -
595 -
596 -
597 -{{code language="sql"}}
598 -USE master;
599 -GO
600 -CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
601 -GO
602 -
603 -CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
604 -GO
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 -
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 -
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 -
631 -
632 -
633 -
634 -
635 -
636 -
637 -
638 -
639 -
640 -
641 -
642 -
643 -{{code language="sql"}}
644 -USE master;
645 -GO
646 -CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
647 -GO
648 -
649 -CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
650 -GO
651 -
652 -BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
653 -WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
654 -ENCRYPTION BY PASSWORD='*****')
655 -
656 -USE Everest_TDE_Master;
657 -GO
658 -CREATE DATABASE ENCRYPTION KEY
659 -WITH ALGORITHM = AES_256
660 -ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
661 -GO
662 -ALTER DATABASE Everest_TDE_Master
663 -SET ENCRYPTION ON;
664 -GO
665 -
666 -USE Everest_TDE_Master_Documents;
667 -GO
668 -CREATE DATABASE ENCRYPTION KEY
669 -WITH ALGORITHM = AES_256
670 -ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
671 -GO
672 -ALTER DATABASE Everest_TDE_Master_Documents
673 -SET ENCRYPTION ON;
674 -GO
675 -{{/code}}
676 -
677 -
678 -
317 +

Applications

Navigation

Need help?

If you need help with XWiki you can contact: