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 < 10.1 >
edited by Rudi Marais
on 2025/03/05 13:24
Change comment: There is no comment for this version

Summary

Details

Page properties
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.nikhils
1 +XWiki.rudim
Content
... ... @@ -95,6 +95,8 @@
95 95  
96 96  * Click **Create** to generate the key.
97 97  
98 +
99 +
98 98  ===== **2. Enable TDE on the SQL Managed Instance** =====
99 99  
100 100  ===== =====
... ... @@ -112,6 +112,7 @@
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  
117 +
115 115  **- Set the Key as Default TDE Protector:**
116 116  
117 117  * Make the key the **default TDE protector** for your instance.
... ... @@ -120,6 +120,7 @@
120 120  
121 121  * Click **Save** to apply the changes.
122 122  
126 +
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**.
... ... @@ -299,8 +299,70 @@
299 299  
300 300  
301 301  
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 +
302 302  === ===
303 303  
369 +===== Note: We could use the job activity monitor to see if the job executed successfully. =====
304 304  
305 305  
306 306  
... ... @@ -313,5 +313,300 @@
313 313  
314 314  
315 315  
382 +1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
383 +GO
316 316  
317 -
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 +

Applications

Navigation

Need help?

If you need help with XWiki you can contact: