Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 6.2 >
edited by Rudi Marais
on 2025/02/26 16:00
To version < 7.1 >
edited by Nikhil Singh
on 2025/02/28 11:30
>
Change comment: There is no comment for this version

Summary

Details

Page properties
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.rudim
1 +XWiki.nikhils
Content
... ... @@ -17,17 +17,20 @@
17 17  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';
18 18  Go--
19 19  
20 ---7 Perform the database backup with the provided parameters
20 +--7 --
21 +
22 +{{code language="sql"}}
23 +Perform the database backup with the provided parameters
21 21  EXECUTE dba.dbo.DatabaseBackup
22 - @Databases = 'dba',                          -- Replace with your database name
23 - @URL = '[[https:~~/~~/zagpebslab.blob.core.windows.net/sql-backups'>>https://zagpebslab.blob.core.windows.net/sql-backups']], -- Azure Blob Storage URL
24 - @BackupType = 'Full',                        -- Full backup
25 - @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
26 - @Compress = 'Y',                             -- Compress the backup
27 - @Verify = 'N'; -- No verification of backup
28 -GO--
25 +    @Databases = 'dba',                           Replace with your database name
26 +    @URL = 'https://zagpebslab.blob.core.windows.net/sql-backups',   Azure Blob Storage URL
27 +    @BackupType = 'Full',                         Full backup
28 +    @CopyOnly = 'Y',                              Copy-only backup to avoid breaking backup chain
29 +    @Compress = 'Y',                              Compress the backup
30 +    @Verify = 'N';                                No verification of backup
31 +GO
32 +{{/code}}
29 29  
30 -
31 31  The backup was unable to be done from smss to azure ( the MI does not support encrypted DBs to be backed up from smss to azure)
32 32  
33 33  We have unencrypted a database and backed it up from ssms to azure blob successfully
... ... @@ -42,26 +42,67 @@
42 42  = **Using TDE directly from azure portal** =
43 43  
44 44  
48 +===== 1 Go to azure key vault ( DemoTestRudi) to generate a key =====
45 45  
46 46  
51 +- Go to keys and click generate
47 47  
53 +- Name your key (mysqlmikey)
48 48  
55 +- Choose a key type (RSA)
49 49  
57 +-Choose RSA key size (2048-bit)
50 50  
59 +Note:
51 51  
61 +* 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.
62 +* **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.).
63 +* 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.
52 52  
65 +- Click create
53 53  
54 54  
68 +[[image:image-20250228120622-1.png||height="236" width="542"]]
55 55  
56 56  
71 +===== 2 Enable TDE =====
57 57  
58 58  
74 +- Go to your Managed instance ( sqlmi-ebs-lab)
59 59  
76 +- Click on security and choose Transparent data encryption
60 60  
78 +- Select the type of managed key ( Customer-managed key0
61 61  
80 +- Select the key from the key vault we generated in the key vault( mysqlmikey)
62 62  
82 +- Make the key the default TDE protector
63 63  
84 +-Click save
64 64  
86 +
87 +[[image:image-20250228122106-2.png||height="251" width="511"]]
88 +
89 +
90 +- TDE has now been enabled on the Managed instance
91 +
92 +
93 +Conclusion: All the databases have been encrypted by an asymmetric key. The key is the same for each database ( same encryption thumbprint).
94 +
95 +We are now able to backup databases from SSMS to Azure storage.
96 +
97 +
98 +
99 +
100 +
101 +
102 +
103 +
104 +
105 +
106 +
107 +
108 +
65 65  1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
66 66  GO
67 67  
... ... @@ -177,8 +177,7 @@
177 177  
178 178  BACKUP DATABASE [dba2]
179 179  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']],
180 -
181 - COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
224 +\\ COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
182 182   COMPRESSION,  -- Optional: Compresses the backup to save storage space
183 183   STATS = 10 -- Optional: Provides backup progress status
184 184  GO--
... ... @@ -278,10 +278,39 @@
278 278  
279 279  
280 280  
324 +{{code language="sql"}}
325 +USE master;
326 +GO
327 +CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
328 +GO
281 281  
330 +CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
331 +GO
282 282  
333 +BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
334 +WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
335 +ENCRYPTION BY PASSWORD='*****')
283 283  
337 +USE Everest_TDE_Master;
338 +GO
339 +CREATE DATABASE ENCRYPTION KEY
340 +WITH ALGORITHM = AES_256
341 +ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
342 +GO
343 +ALTER DATABASE Everest_TDE_Master
344 +SET ENCRYPTION ON;
345 +GO
284 284  
347 +USE Everest_TDE_Master_Documents;
348 +GO
349 +CREATE DATABASE ENCRYPTION KEY
350 +WITH ALGORITHM = AES_256
351 +ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
352 +GO
353 +ALTER DATABASE Everest_TDE_Master_Documents
354 +SET ENCRYPTION ON;
355 +GO
356 +{{/code}}
285 285  
286 286  
287 287  
... ... @@ -291,6 +291,10 @@
291 291  
292 292  
293 293  
366 +
367 +
368 +
369 +
294 294  {{code language="sql"}}
295 295  USE master;
296 296  GO

Applications

Navigation

Need help?

If you need help with XWiki you can contact: