Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 6.1 >
edited by Nikhil Singh
on 2025/02/26 14:02
To version < 2.3 >
edited by Rudi Marais
on 2023/05/22 23:50
>
Change comment: Updated parent field.

Summary

Details

Page properties
Parent
... ... @@ -1,1 +1,1 @@
1 -technical-documentation.analysis.sql.WebHome
1 +xwiki:technical-documentation.analysis.sql.WebHome
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.nikhils
1 +XWiki.rudim
Content
... ... @@ -1,296 +294,3 @@
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 294  {{code language="sql"}}
295 295  USE master;
296 296  GO
... ... @@ -324,7 +324,3 @@
324 324  SET ENCRYPTION ON;
325 325  GO
326 326  {{/code}}
327 -
328 -
329 -
330 -

Applications

Navigation

Need help?

If you need help with XWiki you can contact: