Wiki source code of sql-tde

Version 9.1 by Nikhil Singh on 2025/02/28 13:57
Show last authors
1 = **1. Steps for Implementing Transparent Data Encryption (TDE) FROM SQL Server** =
2
3
4 This document outlines the process of **encrypting SQL Server databases** using **Transparent Data Encryption (TDE)** and backing them up to **Azure Storage**. TDE ensures data at rest is encrypted, leveraging a **Master Key (MK)**, **TDE Certificate**, and **Database Encryption Key (DEK)** for encryption.
5
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
8
9 **~1. Create a Master Key in the master Database**
10 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.
11
12
13 **2. Create a TDE Certificate (Encrypted by the Master Key)**
14 Next, generate a **TDE certificate** that will be used to encrypt the **Database Encryption Key (DEK)**. This certificate is encrypted by the **Master Key** created in step 1, providing an additional layer of security.
15
16
17 **3. Choose the Database to Create the Database Encryption Key (DEK)**
18 For the selected database, create the **Database Encryption Key (DEK)**. The DEK will be encrypted by the **TDE certificate** and will use the **AES-256 encryption algorithm** to ensure data is securely encrypted at rest.
19
20
21 **4. Enable Encryption for the Database**
22 Once the **DEK** has been created, enable **TDE** for the database. This ensures that all data written to the database is automatically encrypted at rest, providing full protection for sensitive information.
23
24
25 **5. Create a Credential with a SAS Token**
26 Finally, create a **credential** that allows SQL Server to access Azure Blob Storage. This credential is created using a **Shared Access Signature (SAS) token**, which ensures secure and authenticated access to the storage account for backup purposes.
27
28 {{code language="sql"}}
29 drop credential [https://zagpebslab.blob.core.windows.net/sql-backups]
30 CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
31 WITH IDENTITY='SHARED ACCESS SIGNATURE'
32 , 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'
33 GO
34
35 {{/code}}
36
37
38 **~ 6. Use a Stored Procedure to Back Up to Azure Storage Account**
39
40 {{code language="sql"}}
41 EXECUTE dba.dbo.DatabaseBackup
42 @Databases = 'dba',
43 @URL = 'https://zagpebslab.blob.core.windows.net/sql-backups',
44 @BackupType = 'Full',
45 @CopyOnly = 'Y',
46 @Compress = 'Y',
47 @Verify = 'N'
48 {{/code}}
49
50 === ===
51
52 === Conclusion: Backup to Azure Storage Account with TDE Encrypted Databases ===
53
54 The attempt to back up an **encrypted database** (with Transparent Data Encryption - TDE) from **SQL Server Management Studio (SSMS)** to an Azure Storage Account was unsuccessful. This issue arises because **Azure does not permit TDE-encrypted databases to be backed up directly to Azure Storage using SSMS**.
55
56 However, after decrypting the database, we were able to successfully back it up to the Azure Storage Account via SSMS. This confirms that **backups can be performed on an unencrypted database**, but **not on an encrypted database**.
57
58
59
60
61
62 = **2. Enabling Transparent Data Encryption (TDE) Using Azure Key Vault** =
63
64
65 **Introduction:** This document outlines the process of enabling **Transparent Data Encryption (TDE)** on an **Azure SQL Managed Instance (SQL MI)** using a **Customer-Managed Key (CMK)** stored in **Azure Key Vault**. The encryption is managed using an asymmetric key from **Azure Key Vault**, ensuring that all data within the SQL Managed Instance is encrypted using a strong encryption algorithm.
66
67
68 ===== **1. Generate a Key in Azure Key Vault** =====
69
70
71 **- Navigate to Azure Key Vault:**
72
73 * Go to the **Azure Portal** and select **Key Vault** (DemoRudiTest).
74
75
76 **- Generate a New Key:**
77
78 * Go to the **Keys** section and click **Generate** to create a new key.
79
80
81 **- Configure Key Settings:**
82
83 * **Name the Key**: Choose a name for your key, e.g., mysqlmikey.
84 * **Key Type**: Select **RSA** as the key type.
85 * **RSA Key Size**: Choose an **RSA key size** of **2048-bit**. (optional)
86
87 Note:
88
89 * switching from 2048-bit RSA to 4096-bit RSA for TDE will affect performance, but the actual impact might be minor, especially on modern hardware and typical workloads.
90 * **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.).
91 * 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.
92
93
94 **- Create the Key:**
95
96 * Click **Create** to generate the key.
97
98
99
100
101 ===== **2. Enable TDE on the SQL Managed Instance** =====
102
103 ===== =====
104
105 **- Navigate to Your Managed Instance:**
106
107 * Go to your **SQL Managed Instance** (sqlmi-ebs-lab).
108
109
110 **- Enable Transparent Data Encryption (TDE):**
111
112 * Under **Security**, select **Transparent Data Encryption**.
113
114
115 **- Configure TDE with a Customer-Managed Key (CMK):**
116
117 * Select **Customer-managed key** as the encryption type.
118 * Choose the key you created earlier from **Azure Key Vault** (mysqlmikey).
119
120
121
122 **- Set the Key as Default TDE Protector:**
123
124 * Make the key the **default TDE protector** for your instance.
125
126
127 **- Save Configuration:**
128
129 * Click **Save** to apply the changes.
130
131
132
133 === **Conclusion** ===
134
135 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**.
136
137 This process ensures that your data is protected both at rest and during backup, offering enhanced security for your managed databases in the cloud.
138
139
140
141 === **1. Backup Specific Databases Using SQL Server Agent Jobs** ===
142
143 This document describes the process of creating an automated **SQL Server Agent Job** to back up only the **DBA databases** in a SQL Server instance. The backups will occur **daily at 3:00 AM** and will be stored in an **Azure Storage Account** for secure and reliable cloud storage.
144
145 ----
146
147 ==== **1. Create the Backup Script** ====
148
149
150 ====== **- Declaring Variables** ======
151
152 {{code language="sql"}}
153 DECLARE @DatabaseName NVARCHAR(128)
154 DECLARE @BackupContainerURL NVARCHAR(512)
155 {{/code}}
156
157 DECLARE @DatabaseName NVARCHAR(128) DECLARE @BackupContainerURL NVARCHAR(512)
158
159 * **@DatabaseName**: A variable to hold the name of the database during each iteration of the loop.
160 * **@BackupContainerURL**: A variable to store the URL of the Azure Blob Storage container where the database backups will be stored.
161
162 ----
163
164 ====== **2. Setting the Azure Blob Storage URL** ======
165
166 {{code language="sql"}}
167 SET @BackupContainerURL = 'https://<your_storage_account>.blob.core.windows.net/sql-backups/'
168 {{/code}}
169
170
171 ----
172
173 ====== **3. Declaring the Cursor** ======
174
175
176 {{code language="sql"}}
177 DECLARE db_cursor CURSOR FOR
178 SELECT name
179 FROM sys.databases
180 WHERE name LIKE 'dba%' -- Only pick databases that start with 'dba'
181 {{/code}}
182
183 * **Cursor Declaration**:
184 ** The cursor db_cursor is declared to loop through the list of databases in the sys.databases system view.
185 ** The **WHERE name LIKE 'dba%'** clause ensures that only databases with names starting with dba will be processed. For example, databases like dbaTest, dbaProd, etc., will be included in the loop.
186 ** **sys.databases**: A system catalog view that contains information about each database in the SQL Server instance.
187
188 ----
189
190 ====== **4. Opening the Cursor** ======
191
192 {{code language="sql"}}
193 OPEN db_cursor
194 FETCH NEXT FROM db_cursor INTO @DatabaseName
195 {{/code}}
196
197 OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
198
199 * **OPEN db_cursor**: This opens the cursor for reading the results.
200 * **FETCH NEXT**: The FETCH NEXT statement retrieves the first database name that meets the LIKE 'dba%' condition and stores it in the @DatabaseName variable. This will be used in the next step where the backup procedure is executed.
201
202 ----
203
204 ====== **5. Looping Through Databases** ======
205
206 {{code language="sql"}}
207 -- Loop through each database and execute the stored procedure
208 WHILE @@FETCH_STATUS = 0
209 BEGIN
210 -- Print current database for logging/debugging
211 PRINT 'Backing up database: ' + @DatabaseName
212
213 -- Execute the DatabaseBackup stored procedure for each database
214 EXECUTE dba.dbo.DatabaseBackup
215 @Databases = @DatabaseName, -- Specify the current database
216 @URL = @BackupContainerURL, -- Azure Blob Storage URL
217 @BackupType = 'Full', -- Full backup type
218 @CopyOnly = 'Y', -- Use copy-only backup (this doesn’t affect the transaction log chain)
219 @Compress = 'Y', -- Enable compression
220 @Verify = 'N'; -- Skip verification after backup
221
222 {{/code}}
223
224
225 * **WHILE @@FETCH_STATUS = 0**: The loop will continue as long as the FETCH command successfully retrieves a database name. @@FETCH_STATUS is a system function that returns 0 if the fetch operation is successful.
226 * **PRINT**: This line prints the name of the database that is currently being backed up. It helps with logging or debugging purposes, as you can track which database is being processed at any given time.
227 * (((
228 **EXECUTE dba.dbo.DatabaseBackup**:
229
230 * This calls a stored procedure named dba.dbo.DatabaseBackup for each database.
231 * The parameters passed to the stored procedure:
232 ** **@Databases = @DatabaseName**: Specifies which database to back up (the current database being processed).
233 ** **@URL = @BackupContainerURL**: Specifies the destination Azure Blob Storage URL.
234 ** **@BackupType = 'Full'**: Specifies the backup type, which is a **Full** backup (this means all data in the database is backed up).
235 ** **@CopyOnly = 'Y'**: Specifies that this is a **copy-only backup**. This ensures that the backup does not affect the transaction log chain and does not interfere with regular backups.
236 ** **@Compress = 'Y'**: Enables compression of the backup, reducing the size of the backup file.
237 ** **@Verify = 'N'**: Specifies that the backup verification step should be skipped. (Verification can be added if needed for additional safety.)
238 )))
239
240 ----
241
242 ====== **6. Fetch the Next Database** ======
243
244 {{code language="sql"}}
245 -- Fetch the next database in the cursor
246 FETCH NEXT FROM db_cursor INTO @DatabaseName
247
248 {{/code}}
249
250 ~-~- Fetch the next database in the cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
251
252 * **FETCH NEXT**: After executing the backup for the current database, this statement retrieves the next database from the cursor and stores it in the @DatabaseName variable. The loop will continue until all matching databases have been processed.
253
254 ----
255
256 ====== **7. Closing and Deallocating the Cursor** ======
257
258 {{code language="sql"}}
259 -- Close and deallocate the cursor to clean up resources
260 CLOSE db_cursor
261 DEALLOCATE db_cursor
262
263
264 {{/code}}
265
266 * **CLOSE db_cursor**: This closes the cursor once the loop finishes processing all databases.
267 * **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.
268
269 (% class="wikigeneratedid" %)
270 ====== ======
271
272 ====== **8. Full Script** ======
273
274
275 {{code language="sql"}}
276 DECLARE @DatabaseName NVARCHAR(128)
277 DECLARE @BackupContainerURL NVARCHAR(512)
278
279 SET @BackupContainerURL = 'https://zagpebslab.blob.core.windows.net/sql-backups'
280
281 DECLARE db_cursor CURSOR FOR
282 SELECT name
283 FROM sys.databases
284 WHERE name LIKE 'dba%'
285
286 OPEN db_cursor
287 FETCH NEXT FROM db_cursor INTO @DatabaseName
288
289 WHILE @@FETCH_STATUS = 0
290 BEGIN
291
292 PRINT 'Backing up database: ' + @DatabaseName
293
294 EXECUTE dba.dbo.DatabaseBackup
295 @Databases = @DatabaseName,
296 @URL = @BackupContainerURL,
297 @BackupType = 'Full',
298 @CopyOnly = 'Y',
299 @Compress = 'Y',
300 @Verify = 'N';
301
302 FETCH NEXT FROM db_cursor INTO @DatabaseName
303 END
304
305 CLOSE db_cursor
306 DEALLOCATE db_cursor
307
308 {{/code}}
309
310
311
312
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" %)
375 === ===
376
377 ===== Note: We could use the job activity monitor to see if the job executed successfully. =====
378
379
380
381
382
383
384
385
386
387
388
389
390 1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
391 GO
392
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
687