Changes for page sql-tde

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

Summary

Details

Page properties
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.nikhils
1 +XWiki.rudim
Content
... ... @@ -1,5 +1,67 @@
1 -= TDE(1) =
1 += TDE(backup from smss to azure) =
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 +
3 3  1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
4 4  GO
5 5  
... ... @@ -10,18 +10,18 @@
10 10  2 USE master;
11 11  GO
12 12  
13 -SELECT
75 +SELECT
14 14   cert.name AS Certificate_Name,
15 15   cert.subject AS Certificate_Subject,
16 16   cert.issuer_name AS Issuer_Name,
17 17   cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
18 -FROM
80 +FROM
19 19   sys.certificates cert
20 -WHERE
82 +WHERE
21 21   cert.name LIKE 'TDE%';
22 22  
23 23  
24 -3 BACKUP CERTIFICATE TDE_Certificate
86 +3 BACKUP CERTIFICATE TDE_Certificate
25 25  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'
26 26  WITH PRIVATE KEY (
27 27   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',
... ... @@ -37,7 +37,7 @@
37 37  CREATE DATABASE ENCRYPTION KEY
38 38  WITH ALGORITHM = AES_256
39 39  ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
40 -GO
102 +GO--
41 41  
42 42  USE [dba]
43 43  GO
... ... @@ -86,7 +86,7 @@
86 86  -- 7. Enable Transparent Data Encryption (TDE) on the database
87 87  ALTER DATABASE dba
88 88  SET ENCRYPTION ON;
89 -GO
151 +GO--
90 90  
91 91  
92 92  select name, database_id, state_desc
... ... @@ -93,14 +93,14 @@
93 93  from sys.databases
94 94  
95 95  
96 -SELECT
158 +SELECT
97 97   database_id,
98 98   key_algorithm,
99 99   key_length,
100 100   encryption_state_desc
101 - encryptor_type
163 + encryptor_type
102 102  
103 -FROM
165 +FROM
104 104   sys.dm_database_encryption_keys;
105 105  
106 106  
... ... @@ -107,41 +107,41 @@
107 107   Select * from sys.dm_database_encryption_keys
108 108  
109 109  
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'
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']]
112 112  With copy_only
113 113  GO
114 114  
115 115  
116 116  BACKUP DATABASE [dba2]
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
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
121 121   STATS = 10 -- Optional: Provides backup progress status
122 -GO
184 +GO--
123 123  
124 124  
125 125  
126 126  -- Step 1: Drop the existing credential (if needed)
127 127  DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
128 -GO
190 +GO--
129 129  
130 130  -- Step 2: Create a new credential with the SAS token
131 131  CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
132 132  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
133 133  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';
134 -GO
196 +GO--
135 135  
136 136  -- Step 3: Perform the database backup with the provided parameters
137 137  EXECUTE dba.dbo.DatabaseBackup
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
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
141 141   @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
142 - @Compress = 'Y', -- Compress the backup
204 + @Compress = 'Y',                             -- Compress the backup
143 143   @Verify = 'N'; -- No verification of backup
144 -GO
206 +GO--
145 145  
146 146  
147 147  
... ... @@ -151,14 +151,14 @@
151 151  from sys.databases
152 152  
153 153  
154 -SELECT
216 +SELECT
155 155   database_id,
156 156   key_algorithm,
157 157   key_length,
158 158   encryption_state_desc
159 - encryptor_type
221 + encryptor_type
160 160  
161 -FROM
223 +FROM
162 162   select * from sys.dm_database_encryption_keys;
163 163  
164 164  
... ... @@ -165,17 +165,17 @@
165 165   select name, is_encrypted from sys.databases
166 166  
167 167  
168 - SELECT
230 + SELECT
169 169   cert.name AS Certificate_Name,
170 170   cert.subject AS Certificate_Subject,
171 171   cert.issuer_name AS Issuer_Name,
172 172   cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
173 -FROM
235 +FROM
174 174   sys.certificates cert
175 -WHERE
237 +WHERE
176 176   cert.name LIKE 'TDE%';
177 177  
178 - ALTER DATABASE dba1
240 + ALTER DATABASE dba1
179 179  SET ENCRYPTION off;
180 180  GO
181 181  
... ... @@ -229,9 +229,6 @@
229 229  
230 230  
231 231  
232 -
233 -
234 -
235 235  {{code language="sql"}}
236 236  USE master;
237 237  GO
... ... @@ -265,3 +265,7 @@
265 265  SET ENCRYPTION ON;
266 266  GO
267 267  {{/code}}
327 +
328 +
329 +
330 +
XWiki.XWikiComments[0]
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.rudim
Comment
... ... @@ -1,0 +1,1 @@
1 +put the sql code in code tags to start with otherwise its very hard to read
Date
... ... @@ -1,0 +1,1 @@
1 +2025-02-26 16:00:04.223

Applications

Navigation

Need help?

If you need help with XWiki you can contact: