Wiki source code of sql-tde

Version 6.2 by Rudi Marais on 2025/02/26 16:00
Show last authors
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 Perform the database backup with the provided parameters
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--
29
30
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
33 We have unencrypted a database and backed it up from ssms to azure blob successfully
34
35
36 Conclusion: Can be done with and unencrypted DB but not with an Encrypted one>
37
38
39
40
41
42 = **Using TDE directly from azure portal** =
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65 1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
66 GO
67
68 CREATE CERTIFICATE TDE_Certificate
69 WITH SUBJECT = 'TDE Certificate';
70 GO
71
72 2 USE master;
73 GO
74
75 SELECT
76 cert.name AS Certificate_Name,
77 cert.subject AS Certificate_Subject,
78 cert.issuer_name AS Issuer_Name,
79 cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
80 FROM
81 sys.certificates cert
82 WHERE
83 cert.name LIKE 'TDE%';
84
85
86 3 BACKUP CERTIFICATE TDE_Certificate
87 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'
88 WITH PRIVATE KEY (
89 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',
90 ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'
91 );
92 GO
93
94
95 USE Normal;
96 GO
97
98 -- 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate
99 CREATE DATABASE ENCRYPTION KEY
100 WITH ALGORITHM = AES_256
101 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
102 GO--
103
104 USE [dba]
105 GO
106
107 CREATE DATABASE ENCRYPTION KEY
108 WITH ALGORITHM = AES_256
109 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
110 GO
111
112
113 USE [dba1]
114 GO
115
116 CREATE DATABASE ENCRYPTION KEY
117 WITH ALGORITHM = AES_256
118 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
119 GO
120
121
122 USE [dba2]
123 GO
124
125 CREATE DATABASE ENCRYPTION KEY
126 WITH ALGORITHM = AES_256
127 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
128 GO
129
130
131 USE [dba3]
132 GO
133
134 CREATE DATABASE ENCRYPTION KEY
135 WITH ALGORITHM = AES_256
136 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
137 GO
138
139 USE [xwiki]
140 GO
141
142 CREATE DATABASE ENCRYPTION KEY
143 WITH ALGORITHM = AES_256
144 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
145 GO
146
147
148 -- 7. Enable Transparent Data Encryption (TDE) on the database
149 ALTER DATABASE dba
150 SET ENCRYPTION ON;
151 GO--
152
153
154 select name, database_id, state_desc
155 from sys.databases
156
157
158 SELECT
159 database_id,
160 key_algorithm,
161 key_length,
162 encryption_state_desc
163 encryptor_type
164
165 FROM
166 sys.dm_database_encryption_keys;
167
168
169 Select * from sys.dm_database_encryption_keys
170
171
172 BACKUP DATABASE [dba2]
173 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']]
174 With copy_only
175 GO
176
177
178 BACKUP DATABASE [dba2]
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
182 COMPRESSION,  -- Optional: Compresses the backup to save storage space
183 STATS = 10 -- Optional: Provides backup progress status
184 GO--
185
186
187
188 -- Step 1: Drop the existing credential (if needed)
189 DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
190 GO--
191
192 -- Step 2: Create a new credential with the SAS token
193 CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
194 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
195 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';
196 GO--
197
198 -- Step 3: Perform the database backup with the provided parameters
199 EXECUTE dba.dbo.DatabaseBackup
200 @Databases = 'dba',                          -- Replace with your database name
201 @URL = '[[https:~~/~~/zagpebslab.blob.core.windows.net/sql-backups'>>https://zagpebslab.blob.core.windows.net/sql-backups']], -- Azure Blob Storage URL
202 @BackupType = 'Full',                        -- Full backup
203 @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
204 @Compress = 'Y',                             -- Compress the backup
205 @Verify = 'N'; -- No verification of backup
206 GO--
207
208
209
210
211
212 select name, database_id, state_desc
213 from sys.databases
214
215
216 SELECT
217 database_id,
218 key_algorithm,
219 key_length,
220 encryption_state_desc
221 encryptor_type
222
223 FROM
224 select * from sys.dm_database_encryption_keys;
225
226
227 select name, is_encrypted from sys.databases
228
229
230 SELECT
231 cert.name AS Certificate_Name,
232 cert.subject AS Certificate_Subject,
233 cert.issuer_name AS Issuer_Name,
234 cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
235 FROM
236 sys.certificates cert
237 WHERE
238 cert.name LIKE 'TDE%';
239
240 ALTER DATABASE dba1
241 SET ENCRYPTION off;
242 GO
243
244
245 Use dba1;
246 DROP DATABASE ENCRYPTION KEY;
247
248
249 USE [dba1]
250 GO
251
252 CREATE DATABASE ENCRYPTION KEY
253 WITH ALGORITHM = AES_256
254 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
255 GO
256
257
258
259 ALTER DATABASE dba1
260 SET ENCRYPTION ON
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294 {{code language="sql"}}
295 USE master;
296 GO
297 CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
298 GO
299
300 CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
301 GO
302
303 BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
304 WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
305 ENCRYPTION BY PASSWORD='*****')
306
307 USE Everest_TDE_Master;
308 GO
309 CREATE DATABASE ENCRYPTION KEY
310 WITH ALGORITHM = AES_256
311 ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
312 GO
313 ALTER DATABASE Everest_TDE_Master
314 SET ENCRYPTION ON;
315 GO
316
317 USE Everest_TDE_Master_Documents;
318 GO
319 CREATE DATABASE ENCRYPTION KEY
320 WITH ALGORITHM = AES_256
321 ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
322 GO
323 ALTER DATABASE Everest_TDE_Master_Documents
324 SET ENCRYPTION ON;
325 GO
326 {{/code}}
327
328
329
330