Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 6.2 >
edited by Rudi Marais
on 2025/02/26 16:00
To version < 4.1 >
edited by Nikhil Singh
on 2025/02/26 13:24
>
Change comment: There is no comment for this version

Summary

Details

Page properties
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.rudim
1 +XWiki.nikhils
Content
... ... @@ -1,67 +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 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 65  1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
66 66  GO
67 67  
... ... @@ -72,18 +72,18 @@
72 72  2 USE master;
73 73  GO
74 74  
75 -SELECT
13 +SELECT
76 76   cert.name AS Certificate_Name,
77 77   cert.subject AS Certificate_Subject,
78 78   cert.issuer_name AS Issuer_Name,
79 79   cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
80 -FROM
18 +FROM
81 81   sys.certificates cert
82 -WHERE
20 +WHERE
83 83   cert.name LIKE 'TDE%';
84 84  
85 85  
86 -3 BACKUP CERTIFICATE TDE_Certificate
24 +3 BACKUP CERTIFICATE TDE_Certificate
87 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 88  WITH PRIVATE KEY (
89 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',
... ... @@ -99,7 +99,7 @@
99 99  CREATE DATABASE ENCRYPTION KEY
100 100  WITH ALGORITHM = AES_256
101 101  ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
102 -GO--
40 +GO
103 103  
104 104  USE [dba]
105 105  GO
... ... @@ -148,7 +148,7 @@
148 148  -- 7. Enable Transparent Data Encryption (TDE) on the database
149 149  ALTER DATABASE dba
150 150  SET ENCRYPTION ON;
151 -GO--
89 +GO
152 152  
153 153  
154 154  select name, database_id, state_desc
... ... @@ -155,14 +155,14 @@
155 155  from sys.databases
156 156  
157 157  
158 -SELECT
96 +SELECT
159 159   database_id,
160 160   key_algorithm,
161 161   key_length,
162 162   encryption_state_desc
163 - encryptor_type
101 + encryptor_type
164 164  
165 -FROM
103 +FROM
166 166   sys.dm_database_encryption_keys;
167 167  
168 168  
... ... @@ -169,41 +169,41 @@
169 169   Select * from sys.dm_database_encryption_keys
170 170  
171 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']]
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'
174 174  With copy_only
175 175  GO
176 176  
177 177  
178 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
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
183 183   STATS = 10 -- Optional: Provides backup progress status
184 -GO--
122 +GO
185 185  
186 186  
187 187  
188 188  -- Step 1: Drop the existing credential (if needed)
189 189  DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
190 -GO--
128 +GO
191 191  
192 192  -- Step 2: Create a new credential with the SAS token
193 193  CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
194 194  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
195 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--
134 +GO
197 197  
198 198  -- Step 3: Perform the database backup with the provided parameters
199 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
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
203 203   @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
204 - @Compress = 'Y',                             -- Compress the backup
142 + @Compress = 'Y', -- Compress the backup
205 205   @Verify = 'N'; -- No verification of backup
206 -GO--
144 +GO
207 207  
208 208  
209 209  
... ... @@ -213,14 +213,14 @@
213 213  from sys.databases
214 214  
215 215  
216 -SELECT
154 +SELECT
217 217   database_id,
218 218   key_algorithm,
219 219   key_length,
220 220   encryption_state_desc
221 - encryptor_type
159 + encryptor_type
222 222  
223 -FROM
161 +FROM
224 224   select * from sys.dm_database_encryption_keys;
225 225  
226 226  
... ... @@ -227,17 +227,17 @@
227 227   select name, is_encrypted from sys.databases
228 228  
229 229  
230 - SELECT
168 + SELECT
231 231   cert.name AS Certificate_Name,
232 232   cert.subject AS Certificate_Subject,
233 233   cert.issuer_name AS Issuer_Name,
234 234   cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
235 -FROM
173 +FROM
236 236   sys.certificates cert
237 -WHERE
175 +WHERE
238 238   cert.name LIKE 'TDE%';
239 239  
240 - ALTER DATABASE dba1
178 + ALTER DATABASE dba1
241 241  SET ENCRYPTION off;
242 242  GO
243 243  
... ... @@ -291,6 +291,9 @@
291 291  
292 292  
293 293  
232 +
233 +
234 +
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 -
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: