Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 5.1 >
edited by Nikhil Singh
on 2025/02/26 13:57
To version < 3.2 >
edited by Nikhil Singh
on 2025/02/26 13:24
>
Change comment: There is no comment for this version

Summary

Details

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

Applications

Navigation

Need help?

If you need help with XWiki you can contact: