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.2 >
edited by Nikhil Singh
on 2025/05/21 12:45
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -95,6 +95,7 @@
95 95  
96 96  * Click **Create** to generate the key.
97 97  
98 +
98 98  ===== **2. Enable TDE on the SQL Managed Instance** =====
99 99  
100 100  ===== =====
... ... @@ -299,8 +299,70 @@
299 299  
300 300  
301 301  
303 +=== **2. Set Up a New Job in SQL Server Agent** ===
304 +
305 +To automate the backup process of the **DBA databases**, follow the steps below to create a new job in SQL Server Agent.
306 +
307 +----
308 +
309 +====== **Step 1: Create a New Job** ======
310 +
311 +1. **Open SQL Server Management Studio (SSMS)**:
312 +1*. In the **Object Explorer**, expand the **SQL Server Agent** node.
313 +1*. Right-click on **Jobs** and select **New Job**.
314 +
315 +----
316 +
317 +====== **Step 2: Define Job Properties** ======
318 +
319 +In the **New Job** window, configure the job properties:
320 +
321 +* **General Tab**:
322 +** **Job Name**: Enter a descriptive name for the job, such as DBA Databases Backup.
323 +** **Owner**: Specify the job owner (ebssqladmin).
324 +** **Category**: Select Database Maintenance as the category for the job.
325 +** **Description**: Provide a brief description of the job (Automated backup of DBA databases to Azure Storage").
326 +
327 +----
328 +
329 +====== **Step 3: Create the Job Steps** ======
330 +
331 +The job will perform the backup through **Transact-SQL** (T-SQL) commands. Set up the following steps:
332 +
333 +1. **Step Name**: Enter a descriptive step name, such as Backup Only DBA Databases.
334 +1. **Type**: Select Transact-SQL Script (T-SQL).
335 +1. **Database**: Choose the master database, as the script will be run in the context of the master database.
336 +1. **Command**: Paste the backup script you created earlier, which automates the backup of the DBA databases.
337 +
338 +----
339 +
340 +====== **Step 4: Set the Job Schedule** ======
341 +
342 +Now, configure the schedule for the job to run daily at 3:00 AM:
343 +
344 +1. **Schedule Name**: Name the schedule (DBA Database Backup).
345 +1. **Schedule Type**: Choose Recurring for a regular schedule.
346 +1. (((
347 +**Frequency**:
348 +
349 +* **Occurs**: Select Daily.
350 +* **Recurs Every**: Set it to **1 day** to run the job every day at the same time.
351 +)))
352 +1. (((
353 +**Daily Frequency**:
354 +
355 +* Set the **time** for the backup to start, such as **3:00 AM**.
356 +)))
357 +
358 +----
359 +
360 +=== **Conclusion** ===
361 +
362 +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.
363 +
302 302  === ===
303 303  
366 +===== Note: We could use the job activity monitor to see if the job executed successfully. =====
304 304  
305 305  
306 306  
... ... @@ -313,5 +313,299 @@
313 313  
314 314  
315 315  
379 +1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
380 +GO
316 316  
317 -
382 +CREATE CERTIFICATE TDE_Certificate
383 +WITH SUBJECT = 'TDE Certificate';
384 +GO
385 +
386 +2 USE master;
387 +GO
388 +
389 +SELECT
390 + cert.name AS Certificate_Name,
391 + cert.subject AS Certificate_Subject,
392 + cert.issuer_name AS Issuer_Name,
393 + cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
394 +FROM
395 + sys.certificates cert
396 +WHERE
397 + cert.name LIKE 'TDE%';
398 +
399 +
400 +3 BACKUP CERTIFICATE TDE_Certificate
401 +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'
402 +WITH PRIVATE KEY (
403 + 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',
404 + ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'
405 +);
406 +GO
407 +
408 +
409 +USE Normal;
410 +GO
411 +
412 +-- 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate
413 +CREATE DATABASE ENCRYPTION KEY
414 +WITH ALGORITHM = AES_256
415 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
416 +GO--
417 +
418 +USE [dba]
419 +GO
420 +
421 +CREATE DATABASE ENCRYPTION KEY
422 +WITH ALGORITHM = AES_256
423 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
424 +GO
425 +
426 +
427 +USE [dba1]
428 +GO
429 +
430 +CREATE DATABASE ENCRYPTION KEY
431 +WITH ALGORITHM = AES_256
432 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
433 +GO
434 +
435 +
436 +USE [dba2]
437 +GO
438 +
439 +CREATE DATABASE ENCRYPTION KEY
440 +WITH ALGORITHM = AES_256
441 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
442 +GO
443 +
444 +
445 +USE [dba3]
446 +GO
447 +
448 +CREATE DATABASE ENCRYPTION KEY
449 +WITH ALGORITHM = AES_256
450 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
451 +GO
452 +
453 +USE [xwiki]
454 +GO
455 +
456 +CREATE DATABASE ENCRYPTION KEY
457 +WITH ALGORITHM = AES_256
458 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
459 +GO
460 +
461 +
462 +-- 7. Enable Transparent Data Encryption (TDE) on the database
463 +ALTER DATABASE dba
464 +SET ENCRYPTION ON;
465 +GO--
466 +
467 +
468 +select name, database_id, state_desc
469 +from sys.databases
470 +
471 +
472 +SELECT
473 + database_id,
474 + key_algorithm,
475 + key_length,
476 + encryption_state_desc
477 + encryptor_type
478 +
479 +FROM
480 + sys.dm_database_encryption_keys;
481 +
482 +
483 + Select * from sys.dm_database_encryption_keys
484 +
485 +
486 + BACKUP DATABASE [dba2]
487 +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']]
488 +With copy_only
489 +GO
490 +
491 +
492 +BACKUP DATABASE [dba2]
493 +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']],
494 +\\ COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
495 + COMPRESSION,  -- Optional: Compresses the backup to save storage space
496 + STATS = 10 -- Optional: Provides backup progress status
497 +GO--
498 +
499 +
500 +
501 +-- Step 1: Drop the existing credential (if needed)
502 +DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
503 +GO--
504 +
505 +-- Step 2: Create a new credential with the SAS token
506 +CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
507 +WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
508 +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';
509 +GO--
510 +
511 +-- Step 3: Perform the database backup with the provided parameters
512 +EXECUTE dba.dbo.DatabaseBackup
513 + @Databases = 'dba',                          -- Replace with your database name
514 + @URL = '[[https:~~/~~/zagpebslab.blob.core.windows.net/sql-backups'>>https://zagpebslab.blob.core.windows.net/sql-backups']], -- Azure Blob Storage URL
515 + @BackupType = 'Full',                        -- Full backup
516 + @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
517 + @Compress = 'Y',                             -- Compress the backup
518 + @Verify = 'N'; -- No verification of backup
519 +GO--
520 +
521 +
522 +
523 +
524 +
525 +select name, database_id, state_desc
526 +from sys.databases
527 +
528 +
529 +SELECT
530 + database_id,
531 + key_algorithm,
532 + key_length,
533 + encryption_state_desc
534 + encryptor_type
535 +
536 +FROM
537 + select * from sys.dm_database_encryption_keys;
538 +
539 +
540 + select name, is_encrypted from sys.databases
541 +
542 +
543 + SELECT
544 + cert.name AS Certificate_Name,
545 + cert.subject AS Certificate_Subject,
546 + cert.issuer_name AS Issuer_Name,
547 + cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
548 +FROM
549 + sys.certificates cert
550 +WHERE
551 + cert.name LIKE 'TDE%';
552 +
553 + ALTER DATABASE dba1
554 +SET ENCRYPTION off;
555 +GO
556 +
557 +
558 +Use dba1;
559 +DROP DATABASE ENCRYPTION KEY;
560 +
561 +
562 +USE [dba1]
563 +GO
564 +
565 +CREATE DATABASE ENCRYPTION KEY
566 +WITH ALGORITHM = AES_256
567 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
568 +GO
569 +
570 +
571 +
572 +ALTER DATABASE dba1
573 +SET ENCRYPTION ON
574 +
575 +
576 +
577 +
578 +
579 +
580 +
581 +
582 +
583 +
584 +
585 +
586 +
587 +
588 +
589 +
590 +
591 +
592 +
593 +
594 +{{code language="sql"}}
595 +USE master;
596 +GO
597 +CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
598 +GO
599 +
600 +CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
601 +GO
602 +
603 +BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
604 +WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
605 +ENCRYPTION BY PASSWORD='*****')
606 +
607 +USE Everest_TDE_Master;
608 +GO
609 +CREATE DATABASE ENCRYPTION KEY
610 +WITH ALGORITHM = AES_256
611 +ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
612 +GO
613 +ALTER DATABASE Everest_TDE_Master
614 +SET ENCRYPTION ON;
615 +GO
616 +
617 +USE Everest_TDE_Master_Documents;
618 +GO
619 +CREATE DATABASE ENCRYPTION KEY
620 +WITH ALGORITHM = AES_256
621 +ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
622 +GO
623 +ALTER DATABASE Everest_TDE_Master_Documents
624 +SET ENCRYPTION ON;
625 +GO
626 +{{/code}}
627 +
628 +
629 +
630 +
631 +
632 +
633 +
634 +
635 +
636 +
637 +
638 +
639 +
640 +{{code language="sql"}}
641 +USE master;
642 +GO
643 +CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
644 +GO
645 +
646 +CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
647 +GO
648 +
649 +BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
650 +WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
651 +ENCRYPTION BY PASSWORD='*****')
652 +
653 +USE Everest_TDE_Master;
654 +GO
655 +CREATE DATABASE ENCRYPTION KEY
656 +WITH ALGORITHM = AES_256
657 +ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
658 +GO
659 +ALTER DATABASE Everest_TDE_Master
660 +SET ENCRYPTION ON;
661 +GO
662 +
663 +USE Everest_TDE_Master_Documents;
664 +GO
665 +CREATE DATABASE ENCRYPTION KEY
666 +WITH ALGORITHM = AES_256
667 +ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
668 +GO
669 +ALTER DATABASE Everest_TDE_Master_Documents
670 +SET ENCRYPTION ON;
671 +GO
672 +{{/code}}
673 +
674 +

Applications

Navigation

Need help?

If you need help with XWiki you can contact: