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 < 3.2 >
edited by Nikhil Singh
on 2025/02/26 13:24
>
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -1,111 +1,5 @@
1 -= TDE(backup from smss to azure) =
1 += TDE(1) =
2 2  
3 -
4 -1 Create master key in master database (set master key)
5 -
6 -2 Create TDE certificate (encrypted by MK)
7 -
8 -3 Backup the Certificate and private key, By encryption with a password ( did not do it in this case due to storage blog problems)
9 -
10 -4 Choose DB to create the DEK ,Create database encryption key (DEK) with algorithm ( AES= 256) and encryption by certificate
11 -
12 -5 Set encryption on for the database
13 -
14 --- 6 Create a new credential with the SAS token
15 -CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
16 -WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
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 -Go--
19 -
20 ---7 --
21 -
22 -{{code language="sql"}}
23 -Perform the database backup with the provided parameters
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}}
33 -
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 -
36 -We have unencrypted a database and backed it up from ssms to azure blob successfully
37 -
38 -
39 -Conclusion: Can be done with and unencrypted DB but not with an Encrypted one>
40 -
41 -
42 -
43 -
44 -
45 -= **Using TDE directly from azure portal** =
46 -
47 -
48 -===== 1 Go to azure key vault ( DemoTestRudi) to generate a key =====
49 -
50 -
51 -- Go to keys and click generate
52 -
53 -- Name your key (mysqlmikey)
54 -
55 -- Choose a key type (RSA)
56 -
57 --Choose RSA key size (2048-bit)
58 -
59 -Note:
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 -
65 -- Click create
66 -
67 -
68 -[[image:image-20250228120622-1.png||height="236" width="542"]]
69 -
70 -
71 -===== 2 Enable TDE =====
72 -
73 -
74 -- Go to your Managed instance ( sqlmi-ebs-lab)
75 -
76 -- Click on security and choose Transparent data encryption
77 -
78 -- Select the type of managed key ( Customer-managed key0
79 -
80 -- Select the key from the key vault we generated in the key vault( mysqlmikey)
81 -
82 -- Make the key the default TDE protector
83 -
84 --Click save
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  
... ... @@ -116,18 +116,18 @@
116 116  2 USE master;
117 117  GO
118 118  
119 -SELECT
13 +SELECT
120 120   cert.name AS Certificate_Name,
121 121   cert.subject AS Certificate_Subject,
122 122   cert.issuer_name AS Issuer_Name,
123 123   cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
124 -FROM
18 +FROM
125 125   sys.certificates cert
126 -WHERE
20 +WHERE
127 127   cert.name LIKE 'TDE%';
128 128  
129 129  
130 -3 BACKUP CERTIFICATE TDE_Certificate
24 +3 BACKUP CERTIFICATE TDE_Certificate
131 131  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'
132 132  WITH PRIVATE KEY (
133 133   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',
... ... @@ -143,7 +143,7 @@
143 143  CREATE DATABASE ENCRYPTION KEY
144 144  WITH ALGORITHM = AES_256
145 145  ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
146 -GO--
40 +GO
147 147  
148 148  USE [dba]
149 149  GO
... ... @@ -192,7 +192,7 @@
192 192  -- 7. Enable Transparent Data Encryption (TDE) on the database
193 193  ALTER DATABASE dba
194 194  SET ENCRYPTION ON;
195 -GO--
89 +GO
196 196  
197 197  
198 198  select name, database_id, state_desc
... ... @@ -199,14 +199,14 @@
199 199  from sys.databases
200 200  
201 201  
202 -SELECT
96 +SELECT
203 203   database_id,
204 204   key_algorithm,
205 205   key_length,
206 206   encryption_state_desc
207 - encryptor_type
101 + encryptor_type
208 208  
209 -FROM
103 +FROM
210 210   sys.dm_database_encryption_keys;
211 211  
212 212  
... ... @@ -213,40 +213,41 @@
213 213   Select * from sys.dm_database_encryption_keys
214 214  
215 215  
216 - BACKUP DATABASE [dba2]
217 -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']]
110 + BACKUP DATABASE [dba2]
111 +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'
218 218  With copy_only
219 219  GO
220 220  
221 221  
222 222  BACKUP DATABASE [dba2]
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
225 - COMPRESSION,  -- Optional: Compresses the backup to save storage space
117 +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',
118 +
119 + COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
120 + COMPRESSION, -- Optional: Compresses the backup to save storage space
226 226   STATS = 10 -- Optional: Provides backup progress status
227 -GO--
122 +GO
228 228  
229 229  
230 230  
231 231  -- Step 1: Drop the existing credential (if needed)
232 232  DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
233 -GO--
128 +GO
234 234  
235 235  -- Step 2: Create a new credential with the SAS token
236 236  CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
237 237  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
238 238  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';
239 -GO--
134 +GO
240 240  
241 241  -- Step 3: Perform the database backup with the provided parameters
242 242  EXECUTE dba.dbo.DatabaseBackup
243 - @Databases = 'dba',                          -- Replace with your database name
244 - @URL = '[[https:~~/~~/zagpebslab.blob.core.windows.net/sql-backups'>>https://zagpebslab.blob.core.windows.net/sql-backups']], -- Azure Blob Storage URL
245 - @BackupType = 'Full',                        -- Full backup
138 + @Databases = 'dba', -- Replace with your database name
139 + @URL = 'https://zagpebslab.blob.core.windows.net/sql-backups', -- Azure Blob Storage URL
140 + @BackupType = 'Full', -- Full backup
246 246   @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
247 - @Compress = 'Y',                             -- Compress the backup
142 + @Compress = 'Y', -- Compress the backup
248 248   @Verify = 'N'; -- No verification of backup
249 -GO--
144 +GO
250 250  
251 251  
252 252  
... ... @@ -256,14 +256,14 @@
256 256  from sys.databases
257 257  
258 258  
259 -SELECT
154 +SELECT
260 260   database_id,
261 261   key_algorithm,
262 262   key_length,
263 263   encryption_state_desc
264 - encryptor_type
159 + encryptor_type
265 265  
266 -FROM
161 +FROM
267 267   select * from sys.dm_database_encryption_keys;
268 268  
269 269  
... ... @@ -270,17 +270,17 @@
270 270   select name, is_encrypted from sys.databases
271 271  
272 272  
273 - SELECT
168 + SELECT
274 274   cert.name AS Certificate_Name,
275 275   cert.subject AS Certificate_Subject,
276 276   cert.issuer_name AS Issuer_Name,
277 277   cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
278 -FROM
173 +FROM
279 279   sys.certificates cert
280 -WHERE
175 +WHERE
281 281   cert.name LIKE 'TDE%';
282 282  
283 - ALTER DATABASE dba1
178 + ALTER DATABASE dba1
284 284  SET ENCRYPTION off;
285 285  GO
286 286  
... ... @@ -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  
... ... @@ -366,7 +366,6 @@
366 366  
367 367  
368 368  
369 -
370 370  {{code language="sql"}}
371 371  USE master;
372 372  GO
... ... @@ -400,7 +400,3 @@
400 400  SET ENCRYPTION ON;
401 401  GO
402 402  {{/code}}
403 -
404 -
405 -
406 -
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: