Changes for page sql-tde

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

Summary

Details

Page properties
Content
... ... @@ -17,20 +17,17 @@
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 --
21 -
22 -{{code language="sql"}}
23 -Perform the database backup with the provided parameters
20 +--7 Perform the database backup with the provided parameters
24 24  EXECUTE dba.dbo.DatabaseBackup
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}}
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--
33 33  
30 +
34 34  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)
35 35  
36 36  We have unencrypted a database and backed it up from ssms to azure blob successfully
... ... @@ -42,70 +42,28 @@
42 42  
43 43  
44 44  
45 -= **Using TDE directly from azure portal** =
46 46  
47 47  
48 -===== 1 Go to azure key vault ( DemoTestRudi) to generate a key =====
49 49  
50 50  
51 -- Go to keys and click generate
52 52  
53 -- Name your key (mysqlmikey)
54 54  
55 -- Choose a key type (RSA)
56 56  
57 --Choose RSA key size (2048-bit)
58 58  
59 -Note:
60 60  
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.
64 64  
65 -- Click create
66 66  
67 67  
68 -[[image:image-20250228120622-1.png||height="236" width="542"]]
69 69  
70 70  
71 -===== 2 Enable TDE =====
72 72  
73 73  
74 -- Go to your Managed instance ( sqlmi-ebs-lab)
75 75  
76 -- Click on security and choose Transparent data encryption
77 77  
78 -- Select the type of managed key ( Customer-managed key0
79 79  
80 -- Select the key from the key vault we generated in the key vault( mysqlmikey)
81 81  
82 -- Make the key the default TDE protector
83 83  
84 --Click save
85 85  
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 -
109 109  1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
110 110  GO
111 111  
... ... @@ -221,7 +221,8 @@
221 221  
222 222  BACKUP DATABASE [dba2]
223 223  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']],
224 -\\ COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
179 +
180 + COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
225 225   COMPRESSION,  -- Optional: Compresses the backup to save storage space
226 226   STATS = 10 -- Optional: Provides backup progress status
227 227  GO--
... ... @@ -321,39 +321,10 @@
321 321  
322 322  
323 323  
324 -{{code language="sql"}}
325 -USE master;
326 -GO
327 -CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
328 -GO
329 329  
330 -CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
331 -GO
332 332  
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='*****')
336 336  
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
346 346  
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}}
357 357  
358 358  
359 359  
... ... @@ -363,10 +363,6 @@
363 363  
364 364  
365 365  
366 -
367 -
368 -
369 -
370 370  {{code language="sql"}}
371 371  USE master;
372 372  GO
XWiki.XWikiComments[0]
Author
... ... @@ -1,1 +1,0 @@
1 -XWiki.rudim
Comment
... ... @@ -1,1 +1,0 @@
1 -put the sql code in code tags to start with otherwise its very hard to read
Date
... ... @@ -1,1 +1,0 @@
1 -2025-02-26 16:00:04.223

Applications

Navigation

Need help?

If you need help with XWiki you can contact: