Wiki source code of sql-tde

Version 10.2 by Nikhil Singh on 2025/05/21 12:45
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 [[image:image-20250305152543-1.png]]
10
11
12 **~1. Create a Master Key in the master Database**
13 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.
14
15
16 **2. Create a TDE Certificate (Encrypted by the Master Key)**
17 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.
18
19
20 **3. Choose the Database to Create the Database Encryption Key (DEK)**
21 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.
22
23
24 **4. Enable Encryption for the Database**
25 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.
26
27
28 **5. Create a Credential with a SAS Token**
29 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.
30
31 {{code language="sql"}}
32 drop credential [https://zagpebslab.blob.core.windows.net/sql-backups]
33 CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
34 WITH IDENTITY='SHARED ACCESS SIGNATURE'
35 , 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'
36 GO
37
38 {{/code}}
39
40
41 **~ 6. Use a Stored Procedure to Back Up to Azure Storage Account**
42
43 {{code language="sql"}}
44 EXECUTE dba.dbo.DatabaseBackup
45 @Databases = 'dba',
46 @URL = 'https://zagpebslab.blob.core.windows.net/sql-backups',
47 @BackupType = 'Full',
48 @CopyOnly = 'Y',
49 @Compress = 'Y',
50 @Verify = 'N'
51 {{/code}}
52
53 === ===
54
55 === Conclusion: Backup to Azure Storage Account with TDE Encrypted Databases ===
56
57 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**.
58
59 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**.
60
61
62
63
64
65 = **2. Enabling Transparent Data Encryption (TDE) Using Azure Key Vault** =
66
67
68 **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.
69
70
71 ===== **1. Generate a Key in Azure Key Vault** =====
72
73
74 **- Navigate to Azure Key Vault:**
75
76 * Go to the **Azure Portal** and select **Key Vault** (DemoRudiTest).
77
78 **- Generate a New Key:**
79
80 * Go to the **Keys** section and click **Generate** to create a new key.
81
82 **- Configure Key Settings:**
83
84 * **Name the Key**: Choose a name for your key, e.g., mysqlmikey.
85 * **Key Type**: Select **RSA** as the key type.
86 * **RSA Key Size**: Choose an **RSA key size** of **2048-bit**. (optional)
87
88 Note:
89
90 * 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.
91 * **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.).
92 * 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.
93
94 **- Create the Key:**
95
96 * Click **Create** to generate the key.
97
98
99 ===== **2. Enable TDE on the SQL Managed Instance** =====
100
101 ===== =====
102
103 **- Navigate to Your Managed Instance:**
104
105 * Go to your **SQL Managed Instance** (sqlmi-ebs-lab).
106
107 **- Enable Transparent Data Encryption (TDE):**
108
109 * Under **Security**, select **Transparent Data Encryption**.
110
111 **- Configure TDE with a Customer-Managed Key (CMK):**
112
113 * Select **Customer-managed key** as the encryption type.
114 * Choose the key you created earlier from **Azure Key Vault** (mysqlmikey).
115
116 **- Set the Key as Default TDE Protector:**
117
118 * Make the key the **default TDE protector** for your instance.
119
120 **- Save Configuration:**
121
122 * Click **Save** to apply the changes.
123
124 === **Conclusion** ===
125
126 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**.
127
128 This process ensures that your data is protected both at rest and during backup, offering enhanced security for your managed databases in the cloud.
129
130
131
132 === **1. Backup Specific Databases Using SQL Server Agent Jobs** ===
133
134 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.
135
136 ----
137
138 ==== **1. Create the Backup Script** ====
139
140
141 ====== **- Declaring Variables** ======
142
143 {{code language="sql"}}
144 DECLARE @DatabaseName NVARCHAR(128)
145 DECLARE @BackupContainerURL NVARCHAR(512)
146 {{/code}}
147
148 DECLARE @DatabaseName NVARCHAR(128) DECLARE @BackupContainerURL NVARCHAR(512)
149
150 * **@DatabaseName**: A variable to hold the name of the database during each iteration of the loop.
151 * **@BackupContainerURL**: A variable to store the URL of the Azure Blob Storage container where the database backups will be stored.
152
153 ----
154
155 ====== **2. Setting the Azure Blob Storage URL** ======
156
157 {{code language="sql"}}
158 SET @BackupContainerURL = 'https://<your_storage_account>.blob.core.windows.net/sql-backups/'
159 {{/code}}
160
161
162 ----
163
164 ====== **3. Declaring the Cursor** ======
165
166
167 {{code language="sql"}}
168 DECLARE db_cursor CURSOR FOR
169 SELECT name
170 FROM sys.databases
171 WHERE name LIKE 'dba%' -- Only pick databases that start with 'dba'
172 {{/code}}
173
174 * **Cursor Declaration**:
175 ** The cursor db_cursor is declared to loop through the list of databases in the sys.databases system view.
176 ** 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.
177 ** **sys.databases**: A system catalog view that contains information about each database in the SQL Server instance.
178
179 ----
180
181 ====== **4. Opening the Cursor** ======
182
183 {{code language="sql"}}
184 OPEN db_cursor
185 FETCH NEXT FROM db_cursor INTO @DatabaseName
186 {{/code}}
187
188 OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
189
190 * **OPEN db_cursor**: This opens the cursor for reading the results.
191 * **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.
192
193 ----
194
195 ====== **5. Looping Through Databases** ======
196
197 {{code language="sql"}}
198 -- Loop through each database and execute the stored procedure
199 WHILE @@FETCH_STATUS = 0
200 BEGIN
201 -- Print current database for logging/debugging
202 PRINT 'Backing up database: ' + @DatabaseName
203
204 -- Execute the DatabaseBackup stored procedure for each database
205 EXECUTE dba.dbo.DatabaseBackup
206 @Databases = @DatabaseName, -- Specify the current database
207 @URL = @BackupContainerURL, -- Azure Blob Storage URL
208 @BackupType = 'Full', -- Full backup type
209 @CopyOnly = 'Y', -- Use copy-only backup (this doesn’t affect the transaction log chain)
210 @Compress = 'Y', -- Enable compression
211 @Verify = 'N'; -- Skip verification after backup
212
213 {{/code}}
214
215
216 * **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.
217 * **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.
218 * (((
219 **EXECUTE dba.dbo.DatabaseBackup**:
220
221 * This calls a stored procedure named dba.dbo.DatabaseBackup for each database.
222 * The parameters passed to the stored procedure:
223 ** **@Databases = @DatabaseName**: Specifies which database to back up (the current database being processed).
224 ** **@URL = @BackupContainerURL**: Specifies the destination Azure Blob Storage URL.
225 ** **@BackupType = 'Full'**: Specifies the backup type, which is a **Full** backup (this means all data in the database is backed up).
226 ** **@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.
227 ** **@Compress = 'Y'**: Enables compression of the backup, reducing the size of the backup file.
228 ** **@Verify = 'N'**: Specifies that the backup verification step should be skipped. (Verification can be added if needed for additional safety.)
229 )))
230
231 ----
232
233 ====== **6. Fetch the Next Database** ======
234
235 {{code language="sql"}}
236 -- Fetch the next database in the cursor
237 FETCH NEXT FROM db_cursor INTO @DatabaseName
238
239 {{/code}}
240
241 ~-~- Fetch the next database in the cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
242
243 * **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.
244
245 ----
246
247 ====== **7. Closing and Deallocating the Cursor** ======
248
249 {{code language="sql"}}
250 -- Close and deallocate the cursor to clean up resources
251 CLOSE db_cursor
252 DEALLOCATE db_cursor
253
254
255 {{/code}}
256
257 * **CLOSE db_cursor**: This closes the cursor once the loop finishes processing all databases.
258 * **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.
259
260 ====== ======
261
262 ====== **8. Full Script** ======
263
264
265 {{code language="sql"}}
266 DECLARE @DatabaseName NVARCHAR(128)
267 DECLARE @BackupContainerURL NVARCHAR(512)
268
269 SET @BackupContainerURL = 'https://zagpebslab.blob.core.windows.net/sql-backups'
270
271 DECLARE db_cursor CURSOR FOR
272 SELECT name
273 FROM sys.databases
274 WHERE name LIKE 'dba%'
275
276 OPEN db_cursor
277 FETCH NEXT FROM db_cursor INTO @DatabaseName
278
279 WHILE @@FETCH_STATUS = 0
280 BEGIN
281
282 PRINT 'Backing up database: ' + @DatabaseName
283
284 EXECUTE dba.dbo.DatabaseBackup
285 @Databases = @DatabaseName,
286 @URL = @BackupContainerURL,
287 @BackupType = 'Full',
288 @CopyOnly = 'Y',
289 @Compress = 'Y',
290 @Verify = 'N';
291
292 FETCH NEXT FROM db_cursor INTO @DatabaseName
293 END
294
295 CLOSE db_cursor
296 DEALLOCATE db_cursor
297
298 {{/code}}
299
300
301
302
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
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}}