Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 5.1 >
edited by Nikhil Singh
on 2025/02/26 13:57
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
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
... ... @@ -39,28 +39,70 @@
39 39  
40 40  
41 41  
45 += **Using TDE directly from azure portal** =
42 42  
43 43  
48 +===== 1 Go to azure key vault ( DemoTestRudi) to generate a key =====
44 44  
45 45  
51 +- Go to keys and click generate
46 46  
53 +- Name your key (mysqlmikey)
47 47  
55 +- Choose a key type (RSA)
48 48  
57 +-Choose RSA key size (2048-bit)
49 49  
59 +Note:
50 50  
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.
51 51  
65 +- Click create
52 52  
53 53  
68 +[[image:image-20250228120622-1.png||height="236" width="542"]]
54 54  
55 55  
71 +===== 2 Enable TDE =====
56 56  
57 57  
74 +- Go to your Managed instance ( sqlmi-ebs-lab)
58 58  
76 +- Click on security and choose Transparent data encryption
59 59  
78 +- Select the type of managed key ( Customer-managed key0
60 60  
80 +- Select the key from the key vault we generated in the key vault( mysqlmikey)
61 61  
82 +- Make the key the default TDE protector
62 62  
84 +-Click save
63 63  
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 +
64 64  1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
65 65  GO
66 66  
... ... @@ -176,8 +176,7 @@
176 176  
177 177  BACKUP DATABASE [dba2]
178 178  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']],
179 -
180 - 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
181 181   COMPRESSION,  -- Optional: Compresses the backup to save storage space
182 182   STATS = 10 -- Optional: Provides backup progress status
183 183  GO--
... ... @@ -277,10 +277,39 @@
277 277  
278 278  
279 279  
324 +{{code language="sql"}}
325 +USE master;
326 +GO
327 +CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
328 +GO
280 280  
330 +CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
331 +GO
281 281  
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='*****')
282 282  
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
283 283  
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}}
284 284  
285 285  
286 286  
... ... @@ -290,6 +290,10 @@
290 290  
291 291  
292 292  
366 +
367 +
368 +
369 +
293 293  {{code language="sql"}}
294 294  USE master;
295 295  GO
XWiki.XWikiComments[0]
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.rudim
Comment
... ... @@ -1,0 +1,1 @@
1 +put the sql code in code tags to start with otherwise its very hard to read
Date
... ... @@ -1,0 +1,1 @@
1 +2025-02-26 16:00:04.223

Applications

Navigation

Need help?

If you need help with XWiki you can contact: