Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 10.2 >
edited by Nikhil Singh
on 2025/05/21 12:45
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
... ... @@ -95,7 +95,6 @@
95 95  
96 96  * Click **Create** to generate the key.
97 97  
98 -
99 99  ===== **2. Enable TDE on the SQL Managed Instance** =====
100 100  
101 101  ===== =====
... ... @@ -300,375 +300,19 @@
300 300  
301 301  
302 302  
303 -=== **2. Set Up a New Job in SQL Server Agent** ===
302 +=== ===
304 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 306  
307 -----
308 308  
309 -====== **Step 1: Create a New Job** ======
310 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 314  
315 -----
316 316  
317 -====== **Step 2: Define Job Properties** ======
318 318  
319 -In the **New Job** window, configure the job properties:
320 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 326  
327 -----
328 328  
329 -====== **Step 3: Create the Job Steps** ======
330 330  
331 -The job will perform the backup through **Transact-SQL** (T-SQL) commands. Set up the following steps:
332 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 337  
338 -----
339 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 -
364 -=== ===
365 -
366 -===== Note: We could use the job activity monitor to see if the job executed successfully. =====
367 -
368 -
369 -
370 -
371 -
372 -
373 -
374 -
375 -
376 -
377 -
378 -
379 -1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
380 -GO
381 -
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 -
317 +

Applications

Navigation

Need help?

If you need help with XWiki you can contact: