Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 2.3 >
edited by Rudi Marais
on 2023/05/22 23:50
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
Parent
... ... @@ -1,1 +1,1 @@
1 -xwiki:technical-documentation.analysis.sql.WebHome
1 +technical-documentation.analysis.sql.WebHome
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.rudim
1 +XWiki.nikhils
Content
... ... @@ -1,4 +1,327 @@
1 += TDE(backup from smss to azure) =
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 +
1 1  {{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 +1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
110 +GO
111 +
112 +CREATE CERTIFICATE TDE_Certificate
113 +WITH SUBJECT = 'TDE Certificate';
114 +GO
115 +
116 +2 USE master;
117 +GO
118 +
119 +SELECT
120 + cert.name AS Certificate_Name,
121 + cert.subject AS Certificate_Subject,
122 + cert.issuer_name AS Issuer_Name,
123 + cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
124 +FROM
125 + sys.certificates cert
126 +WHERE
127 + cert.name LIKE 'TDE%';
128 +
129 +
130 +3 BACKUP CERTIFICATE TDE_Certificate
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 +WITH PRIVATE KEY (
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',
134 + ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'
135 +);
136 +GO
137 +
138 +
139 +USE Normal;
140 +GO
141 +
142 +-- 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate
143 +CREATE DATABASE ENCRYPTION KEY
144 +WITH ALGORITHM = AES_256
145 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
146 +GO--
147 +
148 +USE [dba]
149 +GO
150 +
151 +CREATE DATABASE ENCRYPTION KEY
152 +WITH ALGORITHM = AES_256
153 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
154 +GO
155 +
156 +
157 +USE [dba1]
158 +GO
159 +
160 +CREATE DATABASE ENCRYPTION KEY
161 +WITH ALGORITHM = AES_256
162 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
163 +GO
164 +
165 +
166 +USE [dba2]
167 +GO
168 +
169 +CREATE DATABASE ENCRYPTION KEY
170 +WITH ALGORITHM = AES_256
171 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
172 +GO
173 +
174 +
175 +USE [dba3]
176 +GO
177 +
178 +CREATE DATABASE ENCRYPTION KEY
179 +WITH ALGORITHM = AES_256
180 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
181 +GO
182 +
183 +USE [xwiki]
184 +GO
185 +
186 +CREATE DATABASE ENCRYPTION KEY
187 +WITH ALGORITHM = AES_256
188 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
189 +GO
190 +
191 +
192 +-- 7. Enable Transparent Data Encryption (TDE) on the database
193 +ALTER DATABASE dba
194 +SET ENCRYPTION ON;
195 +GO--
196 +
197 +
198 +select name, database_id, state_desc
199 +from sys.databases
200 +
201 +
202 +SELECT
203 + database_id,
204 + key_algorithm,
205 + key_length,
206 + encryption_state_desc
207 + encryptor_type
208 +
209 +FROM
210 + sys.dm_database_encryption_keys;
211 +
212 +
213 + Select * from sys.dm_database_encryption_keys
214 +
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']]
218 +With copy_only
219 +GO
220 +
221 +
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
226 + STATS = 10 -- Optional: Provides backup progress status
227 +GO--
228 +
229 +
230 +
231 +-- Step 1: Drop the existing credential (if needed)
232 +DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
233 +GO--
234 +
235 +-- Step 2: Create a new credential with the SAS token
236 +CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
237 +WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
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--
240 +
241 +-- Step 3: Perform the database backup with the provided parameters
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
246 + @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
247 + @Compress = 'Y',                             -- Compress the backup
248 + @Verify = 'N'; -- No verification of backup
249 +GO--
250 +
251 +
252 +
253 +
254 +
255 +select name, database_id, state_desc
256 +from sys.databases
257 +
258 +
259 +SELECT
260 + database_id,
261 + key_algorithm,
262 + key_length,
263 + encryption_state_desc
264 + encryptor_type
265 +
266 +FROM
267 + select * from sys.dm_database_encryption_keys;
268 +
269 +
270 + select name, is_encrypted from sys.databases
271 +
272 +
273 + SELECT
274 + cert.name AS Certificate_Name,
275 + cert.subject AS Certificate_Subject,
276 + cert.issuer_name AS Issuer_Name,
277 + cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
278 +FROM
279 + sys.certificates cert
280 +WHERE
281 + cert.name LIKE 'TDE%';
282 +
283 + ALTER DATABASE dba1
284 +SET ENCRYPTION off;
285 +GO
286 +
287 +
288 +Use dba1;
289 +DROP DATABASE ENCRYPTION KEY;
290 +
291 +
292 +USE [dba1]
293 +GO
294 +
295 +CREATE DATABASE ENCRYPTION KEY
296 +WITH ALGORITHM = AES_256
297 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
298 +GO
299 +
300 +
301 +
302 +ALTER DATABASE dba1
303 +SET ENCRYPTION ON
304 +
305 +
306 +
307 +
308 +
309 +
310 +
311 +
312 +
313 +
314 +
315 +
316 +
317 +
318 +
319 +
320 +
321 +
322 +
323 +
324 +{{code language="sql"}}
2 2  USE master;
3 3  GO
4 4  CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
... ... @@ -31,3 +31,53 @@
31 31  SET ENCRYPTION ON;
32 32  GO
33 33  {{/code}}
357 +
358 +
359 +
360 +
361 +
362 +
363 +
364 +
365 +
366 +
367 +
368 +
369 +
370 +{{code language="sql"}}
371 +USE master;
372 +GO
373 +CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
374 +GO
375 +
376 +CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
377 +GO
378 +
379 +BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
380 +WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
381 +ENCRYPTION BY PASSWORD='*****')
382 +
383 +USE Everest_TDE_Master;
384 +GO
385 +CREATE DATABASE ENCRYPTION KEY
386 +WITH ALGORITHM = AES_256
387 +ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
388 +GO
389 +ALTER DATABASE Everest_TDE_Master
390 +SET ENCRYPTION ON;
391 +GO
392 +
393 +USE Everest_TDE_Master_Documents;
394 +GO
395 +CREATE DATABASE ENCRYPTION KEY
396 +WITH ALGORITHM = AES_256
397 +ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
398 +GO
399 +ALTER DATABASE Everest_TDE_Master_Documents
400 +SET ENCRYPTION ON;
401 +GO
402 +{{/code}}
403 +
404 +
405 +
406 +
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: