Wiki source code of sql-tde

Version 10.1 by Rudi Marais on 2025/03/05 13:24
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
100 ===== **2. Enable TDE on the SQL Managed Instance** =====
101
102 ===== =====
103
104 **- Navigate to Your Managed Instance:**
105
106 * Go to your **SQL Managed Instance** (sqlmi-ebs-lab).
107
108 **- Enable Transparent Data Encryption (TDE):**
109
110 * Under **Security**, select **Transparent Data Encryption**.
111
112 **- Configure TDE with a Customer-Managed Key (CMK):**
113
114 * Select **Customer-managed key** as the encryption type.
115 * Choose the key you created earlier from **Azure Key Vault** (mysqlmikey).
116
117
118 **- Set the Key as Default TDE Protector:**
119
120 * Make the key the **default TDE protector** for your instance.
121
122 **- Save Configuration:**
123
124 * Click **Save** to apply the changes.
125
126
127 === **Conclusion** ===
128
129 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**.
130
131 This process ensures that your data is protected both at rest and during backup, offering enhanced security for your managed databases in the cloud.
132
133
134
135 === **1. Backup Specific Databases Using SQL Server Agent Jobs** ===
136
137 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.
138
139 ----
140
141 ==== **1. Create the Backup Script** ====
142
143
144 ====== **- Declaring Variables** ======
145
146 {{code language="sql"}}
147 DECLARE @DatabaseName NVARCHAR(128)
148 DECLARE @BackupContainerURL NVARCHAR(512)
149 {{/code}}
150
151 DECLARE @DatabaseName NVARCHAR(128) DECLARE @BackupContainerURL NVARCHAR(512)
152
153 * **@DatabaseName**: A variable to hold the name of the database during each iteration of the loop.
154 * **@BackupContainerURL**: A variable to store the URL of the Azure Blob Storage container where the database backups will be stored.
155
156 ----
157
158 ====== **2. Setting the Azure Blob Storage URL** ======
159
160 {{code language="sql"}}
161 SET @BackupContainerURL = 'https://<your_storage_account>.blob.core.windows.net/sql-backups/'
162 {{/code}}
163
164
165 ----
166
167 ====== **3. Declaring the Cursor** ======
168
169
170 {{code language="sql"}}
171 DECLARE db_cursor CURSOR FOR
172 SELECT name
173 FROM sys.databases
174 WHERE name LIKE 'dba%' -- Only pick databases that start with 'dba'
175 {{/code}}
176
177 * **Cursor Declaration**:
178 ** The cursor db_cursor is declared to loop through the list of databases in the sys.databases system view.
179 ** 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.
180 ** **sys.databases**: A system catalog view that contains information about each database in the SQL Server instance.
181
182 ----
183
184 ====== **4. Opening the Cursor** ======
185
186 {{code language="sql"}}
187 OPEN db_cursor
188 FETCH NEXT FROM db_cursor INTO @DatabaseName
189 {{/code}}
190
191 OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
192
193 * **OPEN db_cursor**: This opens the cursor for reading the results.
194 * **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.
195
196 ----
197
198 ====== **5. Looping Through Databases** ======
199
200 {{code language="sql"}}
201 -- Loop through each database and execute the stored procedure
202 WHILE @@FETCH_STATUS = 0
203 BEGIN
204 -- Print current database for logging/debugging
205 PRINT 'Backing up database: ' + @DatabaseName
206
207 -- Execute the DatabaseBackup stored procedure for each database
208 EXECUTE dba.dbo.DatabaseBackup
209 @Databases = @DatabaseName, -- Specify the current database
210 @URL = @BackupContainerURL, -- Azure Blob Storage URL
211 @BackupType = 'Full', -- Full backup type
212 @CopyOnly = 'Y', -- Use copy-only backup (this doesn’t affect the transaction log chain)
213 @Compress = 'Y', -- Enable compression
214 @Verify = 'N'; -- Skip verification after backup
215
216 {{/code}}
217
218
219 * **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.
220 * **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.
221 * (((
222 **EXECUTE dba.dbo.DatabaseBackup**:
223
224 * This calls a stored procedure named dba.dbo.DatabaseBackup for each database.
225 * The parameters passed to the stored procedure:
226 ** **@Databases = @DatabaseName**: Specifies which database to back up (the current database being processed).
227 ** **@URL = @BackupContainerURL**: Specifies the destination Azure Blob Storage URL.
228 ** **@BackupType = 'Full'**: Specifies the backup type, which is a **Full** backup (this means all data in the database is backed up).
229 ** **@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.
230 ** **@Compress = 'Y'**: Enables compression of the backup, reducing the size of the backup file.
231 ** **@Verify = 'N'**: Specifies that the backup verification step should be skipped. (Verification can be added if needed for additional safety.)
232 )))
233
234 ----
235
236 ====== **6. Fetch the Next Database** ======
237
238 {{code language="sql"}}
239 -- Fetch the next database in the cursor
240 FETCH NEXT FROM db_cursor INTO @DatabaseName
241
242 {{/code}}
243
244 ~-~- Fetch the next database in the cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
245
246 * **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.
247
248 ----
249
250 ====== **7. Closing and Deallocating the Cursor** ======
251
252 {{code language="sql"}}
253 -- Close and deallocate the cursor to clean up resources
254 CLOSE db_cursor
255 DEALLOCATE db_cursor
256
257
258 {{/code}}
259
260 * **CLOSE db_cursor**: This closes the cursor once the loop finishes processing all databases.
261 * **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.
262
263 ====== ======
264
265 ====== **8. Full Script** ======
266
267
268 {{code language="sql"}}
269 DECLARE @DatabaseName NVARCHAR(128)
270 DECLARE @BackupContainerURL NVARCHAR(512)
271
272 SET @BackupContainerURL = 'https://zagpebslab.blob.core.windows.net/sql-backups'
273
274 DECLARE db_cursor CURSOR FOR
275 SELECT name
276 FROM sys.databases
277 WHERE name LIKE 'dba%'
278
279 OPEN db_cursor
280 FETCH NEXT FROM db_cursor INTO @DatabaseName
281
282 WHILE @@FETCH_STATUS = 0
283 BEGIN
284
285 PRINT 'Backing up database: ' + @DatabaseName
286
287 EXECUTE dba.dbo.DatabaseBackup
288 @Databases = @DatabaseName,
289 @URL = @BackupContainerURL,
290 @BackupType = 'Full',
291 @CopyOnly = 'Y',
292 @Compress = 'Y',
293 @Verify = 'N';
294
295 FETCH NEXT FROM db_cursor INTO @DatabaseName
296 END
297
298 CLOSE db_cursor
299 DEALLOCATE db_cursor
300
301 {{/code}}
302
303
304
305
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
367 === ===
368
369 ===== Note: We could use the job activity monitor to see if the job executed successfully. =====
370
371
372
373
374
375
376
377
378
379
380
381
382 1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
383 GO
384
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}}

Applications

Navigation

Need help?

If you need help with XWiki you can contact: