Wiki source code of sql-tde

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

Applications

Navigation

Need help?

If you need help with XWiki you can contact: