Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 2.3 >
edited by Rudi Marais
on 2023/05/22 23:50
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
Parent
... ... @@ -1,1 +1,1 @@
1 -xwiki:technical-documentation.analysis.sql.WebHome
1 +technical-documentation.analysis.sql.WebHome
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.rudim
1 +XWiki.nikhils
Content
... ... @@ -1,3 +1,237 @@
1 += TDE(1) =
2 +
3 +1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
4 +GO
5 +
6 +CREATE CERTIFICATE TDE_Certificate
7 +WITH SUBJECT = 'TDE Certificate';
8 +GO
9 +
10 +2 USE master;
11 +GO
12 +
13 +SELECT
14 + cert.name AS Certificate_Name,
15 + cert.subject AS Certificate_Subject,
16 + cert.issuer_name AS Issuer_Name,
17 + cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
18 +FROM
19 + sys.certificates cert
20 +WHERE
21 + cert.name LIKE 'TDE%';
22 +
23 +
24 +3 BACKUP CERTIFICATE TDE_Certificate
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 +WITH PRIVATE KEY (
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',
28 + ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'
29 +);
30 +GO
31 +
32 +
33 +USE Normal;
34 +GO
35 +
36 +-- 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate
37 +CREATE DATABASE ENCRYPTION KEY
38 +WITH ALGORITHM = AES_256
39 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
40 +GO
41 +
42 +USE [dba]
43 +GO
44 +
45 +CREATE DATABASE ENCRYPTION KEY
46 +WITH ALGORITHM = AES_256
47 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
48 +GO
49 +
50 +
51 +USE [dba1]
52 +GO
53 +
54 +CREATE DATABASE ENCRYPTION KEY
55 +WITH ALGORITHM = AES_256
56 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
57 +GO
58 +
59 +
60 +USE [dba2]
61 +GO
62 +
63 +CREATE DATABASE ENCRYPTION KEY
64 +WITH ALGORITHM = AES_256
65 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
66 +GO
67 +
68 +
69 +USE [dba3]
70 +GO
71 +
72 +CREATE DATABASE ENCRYPTION KEY
73 +WITH ALGORITHM = AES_256
74 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
75 +GO
76 +
77 +USE [xwiki]
78 +GO
79 +
80 +CREATE DATABASE ENCRYPTION KEY
81 +WITH ALGORITHM = AES_256
82 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
83 +GO
84 +
85 +
86 +-- 7. Enable Transparent Data Encryption (TDE) on the database
87 +ALTER DATABASE dba
88 +SET ENCRYPTION ON;
89 +GO
90 +
91 +
92 +select name, database_id, state_desc
93 +from sys.databases
94 +
95 +
96 +SELECT
97 + database_id,
98 + key_algorithm,
99 + key_length,
100 + encryption_state_desc
101 + encryptor_type
102 +
103 +FROM
104 + sys.dm_database_encryption_keys;
105 +
106 +
107 + Select * from sys.dm_database_encryption_keys
108 +
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'
112 +With copy_only
113 +GO
114 +
115 +
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
121 + STATS = 10 -- Optional: Provides backup progress status
122 +GO
123 +
124 +
125 +
126 +-- Step 1: Drop the existing credential (if needed)
127 +DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
128 +GO
129 +
130 +-- Step 2: Create a new credential with the SAS token
131 +CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
132 +WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
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
135 +
136 +-- Step 3: Perform the database backup with the provided parameters
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
141 + @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
142 + @Compress = 'Y', -- Compress the backup
143 + @Verify = 'N'; -- No verification of backup
144 +GO
145 +
146 +
147 +
148 +
149 +
150 +select name, database_id, state_desc
151 +from sys.databases
152 +
153 +
154 +SELECT
155 + database_id,
156 + key_algorithm,
157 + key_length,
158 + encryption_state_desc
159 + encryptor_type
160 +
161 +FROM
162 + select * from sys.dm_database_encryption_keys;
163 +
164 +
165 + select name, is_encrypted from sys.databases
166 +
167 +
168 + SELECT
169 + cert.name AS Certificate_Name,
170 + cert.subject AS Certificate_Subject,
171 + cert.issuer_name AS Issuer_Name,
172 + cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
173 +FROM
174 + sys.certificates cert
175 +WHERE
176 + cert.name LIKE 'TDE%';
177 +
178 + ALTER DATABASE dba1
179 +SET ENCRYPTION off;
180 +GO
181 +
182 +
183 +Use dba1;
184 +DROP DATABASE ENCRYPTION KEY;
185 +
186 +
187 +USE [dba1]
188 +GO
189 +
190 +CREATE DATABASE ENCRYPTION KEY
191 +WITH ALGORITHM = AES_256
192 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
193 +GO
194 +
195 +
196 +
197 +ALTER DATABASE dba1
198 +SET ENCRYPTION ON
199 +
200 +
201 +
202 +
203 +
204 +
205 +
206 +
207 +
208 +
209 +
210 +
211 +
212 +
213 +
214 +
215 +
216 +
217 +
218 +
219 +
220 +
221 +
222 +
223 +
224 +
225 +
226 +
227 +
228 +
229 +
230 +
231 +
232 +
233 +
234 +
1 1  {{code language="sql"}}
2 2  USE master;
3 3  GO

Applications

Navigation

Need help?

If you need help with XWiki you can contact: