Wiki source code of sql-tde

Version 7.1 by Nikhil Singh on 2025/02/28 11:30

version	line-number	content
5.1	1	= TDE(backup from smss to azure) =
3.1	2
5.1	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
7.1	20	--7 --
	21
	22	{{code language="sql"}}
	23	Perform the database backup with the provided parameters
5.1	24	EXECUTE dba.dbo.DatabaseBackup
7.1	25	@Databases = 'dba', Replace with your database name
	26	@URL = 'https://zagpebslab.blob.core.windows.net/sql-backups', Azure Blob Storage URL
	27	@BackupType = 'Full', Full backup
	28	@CopyOnly = 'Y', Copy-only backup to avoid breaking backup chain
	29	@Compress = 'Y', Compress the backup
	30	@Verify = 'N'; No verification of backup
	31	GO
	32	{{/code}}
5.1	33
	34	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)
	35
	36	We have unencrypted a database and backed it up from ssms to azure blob successfully
	37
	38
	39	Conclusion: Can be done with and unencrypted DB but not with an Encrypted one>
	40
	41
	42
	43
	44
6.1	45	= Using TDE directly from azure portal =
5.1	46
	47
7.1	48	===== 1 Go to azure key vault ( DemoTestRudi) to generate a key =====
5.1	49
	50
7.1	51	- Go to keys and click generate
5.1	52
7.1	53	- Name your key (mysqlmikey)
5.1	54
7.1	55	- Choose a key type (RSA)
5.1	56
7.1	57	-Choose RSA key size (2048-bit)
5.1	58
7.1	59	Note:
5.1	60
7.1	61	* switching from 2048-bit RSA to 4096-bit RSA for TDE will affect performance, but the actual impact might be minor, especially on modern hardware and typical workloads.
	62	* It will likely affect CPU usage more than disk I/O, and the impact might be more noticeable during key management operations (key generation, encryption, etc.).
	63	* If your database is not under heavy load and your hardware can handle the extra processing, the trade-off for better security may be worth it.
5.1	64
7.1	65	- Click create
5.1	66
	67
7.1	68	[[image:image-20250228120622-1.png\|\|height="236" width="542"]]
5.1	69
	70
7.1	71	===== 2 Enable TDE =====
5.1	72
	73
7.1	74	- Go to your Managed instance ( sqlmi-ebs-lab)
5.1	75
7.1	76	- Click on security and choose Transparent data encryption
5.1	77
7.1	78	- Select the type of managed key ( Customer-managed key0
5.1	79
7.1	80	- Select the key from the key vault we generated in the key vault( mysqlmikey)
5.1	81
7.1	82	- Make the key the default TDE protector
5.1	83
7.1	84	-Click save
5.1	85
7.1	86
	87	[[image:image-20250228122106-2.png\|\|height="251" width="511"]]
	88
	89
	90	- TDE has now been enabled on the Managed instance
	91
	92
	93	Conclusion: All the databases have been encrypted by an asymmetric key. The key is the same for each database ( same encryption thumbprint).
	94
	95	We are now able to backup databases from SSMS to Azure storage.
	96
	97
	98
	99
	100
	101
	102
	103
	104
	105
	106
	107
	108
3.2	109	1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
	110	GO
3.1	111
3.2	112	CREATE CERTIFICATE TDE_Certificate
	113	WITH SUBJECT = 'TDE Certificate';
	114	GO
3.1	115
3.2	116	2 USE master;
	117	GO
3.1	118
5.1	119	SELECT
3.2	120	cert.name AS Certificate_Name,
	121	cert.subject AS Certificate_Subject,
	122	cert.issuer_name AS Issuer_Name,
	123	cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
5.1	124	FROM
3.2	125	sys.certificates cert
5.1	126	WHERE
3.2	127	cert.name LIKE 'TDE%';
3.1	128
3.2	129
5.1	130	3 BACKUP CERTIFICATE TDE_Certificate
3.2	131	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'
	132	WITH PRIVATE KEY (
	133	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',
	134	ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'
	135	);
	136	GO
	137
	138
	139	USE Normal;
	140	GO
	141
	142	-- 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate
	143	CREATE DATABASE ENCRYPTION KEY
	144	WITH ALGORITHM = AES_256
	145	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
5.1	146	GO--
3.2	147
	148	USE [dba]
	149	GO
	150
	151	CREATE DATABASE ENCRYPTION KEY
	152	WITH ALGORITHM = AES_256
	153	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
	154	GO
	155
	156
	157	USE [dba1]
	158	GO
	159
	160	CREATE DATABASE ENCRYPTION KEY
	161	WITH ALGORITHM = AES_256
	162	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
	163	GO
	164
	165
	166	USE [dba2]
	167	GO
	168
	169	CREATE DATABASE ENCRYPTION KEY
	170	WITH ALGORITHM = AES_256
	171	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
	172	GO
	173
	174
	175	USE [dba3]
	176	GO
	177
	178	CREATE DATABASE ENCRYPTION KEY
	179	WITH ALGORITHM = AES_256
	180	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
	181	GO
	182
	183	USE [xwiki]
	184	GO
	185
	186	CREATE DATABASE ENCRYPTION KEY
	187	WITH ALGORITHM = AES_256
	188	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
	189	GO
	190
	191
	192	-- 7. Enable Transparent Data Encryption (TDE) on the database
	193	ALTER DATABASE dba
	194	SET ENCRYPTION ON;
5.1	195	GO--
3.2	196
	197
	198	select name, database_id, state_desc
	199	from sys.databases
	200
	201
5.1	202	SELECT
3.2	203	database_id,
	204	key_algorithm,
	205	key_length,
	206	encryption_state_desc
5.1	207	encryptor_type
3.2	208
5.1	209	FROM
3.2	210	sys.dm_database_encryption_keys;
	211
	212
	213	Select * from sys.dm_database_encryption_keys
	214
	215
5.1	216	BACKUP DATABASE [dba2]
	217	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']]
3.2	218	With copy_only
	219	GO
	220
	221
	222	BACKUP DATABASE [dba2]
5.1	223	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']],
7.1	224	\\ COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
5.1	225	COMPRESSION, -- Optional: Compresses the backup to save storage space
3.2	226	STATS = 10 -- Optional: Provides backup progress status
5.1	227	GO--
3.2	228
	229
	230
	231	-- Step 1: Drop the existing credential (if needed)
	232	DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
5.1	233	GO--
3.2	234
	235	-- Step 2: Create a new credential with the SAS token
	236	CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
	237	WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
	238	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';
5.1	239	GO--
3.2	240
	241	-- Step 3: Perform the database backup with the provided parameters
	242	EXECUTE dba.dbo.DatabaseBackup
5.1	243	@Databases = 'dba', -- Replace with your database name
	244	@URL = '[[https:~~/~~/zagpebslab.blob.core.windows.net/sql-backups'>>https://zagpebslab.blob.core.windows.net/sql-backups']], -- Azure Blob Storage URL
	245	@BackupType = 'Full', -- Full backup
3.2	246	@CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
5.1	247	@Compress = 'Y', -- Compress the backup
3.2	248	@Verify = 'N'; -- No verification of backup
5.1	249	GO--
3.2	250
	251
	252
	253
	254
	255	select name, database_id, state_desc
	256	from sys.databases
	257
	258
5.1	259	SELECT
3.2	260	database_id,
	261	key_algorithm,
	262	key_length,
	263	encryption_state_desc
5.1	264	encryptor_type
3.2	265
5.1	266	FROM
3.2	267	select * from sys.dm_database_encryption_keys;
	268
	269
	270	select name, is_encrypted from sys.databases
	271
	272
5.1	273	SELECT
3.2	274	cert.name AS Certificate_Name,
	275	cert.subject AS Certificate_Subject,
	276	cert.issuer_name AS Issuer_Name,
	277	cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
5.1	278	FROM
3.2	279	sys.certificates cert
5.1	280	WHERE
3.2	281	cert.name LIKE 'TDE%';
	282
5.1	283	ALTER DATABASE dba1
3.2	284	SET ENCRYPTION off;
	285	GO
	286
	287
	288	Use dba1;
	289	DROP DATABASE ENCRYPTION KEY;
	290
	291
	292	USE [dba1]
	293	GO
	294
	295	CREATE DATABASE ENCRYPTION KEY
	296	WITH ALGORITHM = AES_256
	297	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
	298	GO
	299
	300
	301
	302	ALTER DATABASE dba1
	303	SET ENCRYPTION ON
	304
	305
	306
	307
	308
	309
	310
	311
	312
	313
	314
	315
	316
	317
	318
	319
	320
	321
	322
	323
7.1	324	{{code language="sql"}}
	325	USE master;
	326	GO
	327	CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
	328	GO
3.2	329
7.1	330	CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
	331	GO
3.2	332
7.1	333	BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
	334	WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
	335	ENCRYPTION BY PASSWORD='*****')
3.2	336
7.1	337	USE Everest_TDE_Master;
	338	GO
	339	CREATE DATABASE ENCRYPTION KEY
	340	WITH ALGORITHM = AES_256
	341	ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
	342	GO
	343	ALTER DATABASE Everest_TDE_Master
	344	SET ENCRYPTION ON;
	345	GO
3.2	346
7.1	347	USE Everest_TDE_Master_Documents;
	348	GO
	349	CREATE DATABASE ENCRYPTION KEY
	350	WITH ALGORITHM = AES_256
	351	ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
	352	GO
	353	ALTER DATABASE Everest_TDE_Master_Documents
	354	SET ENCRYPTION ON;
	355	GO
	356	{{/code}}
3.2	357
	358
	359
	360
	361
	362
	363
	364
	365
7.1	366
	367
	368
	369
2.1	370	{{code language="sql"}}
	371	USE master;
	372	GO
	373	CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
	374	GO
	375
	376	CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
	377	GO
	378
	379	BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
	380	WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
	381	ENCRYPTION BY PASSWORD='*****')
	382
	383	USE Everest_TDE_Master;
	384	GO
	385	CREATE DATABASE ENCRYPTION KEY
	386	WITH ALGORITHM = AES_256
	387	ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
	388	GO
	389	ALTER DATABASE Everest_TDE_Master
	390	SET ENCRYPTION ON;
	391	GO
	392
	393	USE Everest_TDE_Master_Documents;
	394	GO
	395	CREATE DATABASE ENCRYPTION KEY
	396	WITH ALGORITHM = AES_256
	397	ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
	398	GO
	399	ALTER DATABASE Everest_TDE_Master_Documents
	400	SET ENCRYPTION ON;
	401	GO
	402	{{/code}}
5.1	403
	404
	405
	406

Applications

More applications

Need help?

If you need help with XWiki you can contact:

XWiki 14.10.21

Wiki source code of sql-tde

Applications

Navigation

Need help?