Wiki source code of sql-tde

Version 11.1 by Nikhil Singh on 2026/07/03 08:30

version	line-number	content
9.1	1	= 1. Steps for Implementing Transparent Data Encryption (TDE) FROM SQL Server =
3.1	2
5.1	3
9.1	4	This document outlines the process of encrypting SQL Server databases using Transparent Data Encryption (TDE) and backing them up to Azure Storage. TDE ensures data at rest is encrypted, leveraging a Master Key (MK), TDE Certificate, and Database Encryption Key (DEK) for encryption.
5.1	5
9.1	6	The process also includes creating a credential for secure backup to Azure Blob Storage, providing a scalable and secure solution for storing encrypted databases in the cloud.
5.1	7
	8
10.1	9	[[image:image-20250305152543-1.png]]
	10
	11
9.1	12	~1. Create a Master Key in the master Database
	13	The first step is to create a Master Key in the master database. This key will be used to encrypt other cryptographic objects, such as certificates and symmetric keys, within SQL Server.
5.1	14
	15
9.1	16	2. Create a TDE Certificate (Encrypted by the Master Key)
	17	Next, generate a TDE certificate that will be used to encrypt the Database Encryption Key (DEK). This certificate is encrypted by the Master Key created in step 1, providing an additional layer of security.
8.1	18
	19
9.1	20	3. Choose the Database to Create the Database Encryption Key (DEK)
	21	For the selected database, create the Database Encryption Key (DEK). The DEK will be encrypted by the TDE certificate and will use the AES-256 encryption algorithm to ensure data is securely encrypted at rest.
	22
	23
	24	4. Enable Encryption for the Database
	25	Once the DEK has been created, enable TDE for the database. This ensures that all data written to the database is automatically encrypted at rest, providing full protection for sensitive information.
	26
	27
	28	5. Create a Credential with a SAS Token
	29	Finally, create a credential that allows SQL Server to access Azure Blob Storage. This credential is created using a Shared Access Signature (SAS) token, which ensures secure and authenticated access to the storage account for backup purposes.
	30
8.1	31	{{code language="sql"}}
	32	drop credential [https://zagpebslab.blob.core.windows.net/sql-backups]
5.1	33	CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
8.1	34	WITH IDENTITY='SHARED ACCESS SIGNATURE'
	35	, 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'
	36	GO
	37
	38	{{/code}}
5.1	39
7.1	40
9.1	41	~ 6. Use a Stored Procedure to Back Up to Azure Storage Account
8.1	42
7.1	43	{{code language="sql"}}
5.1	44	EXECUTE dba.dbo.DatabaseBackup
9.1	45	@Databases = 'dba',
	46	@URL = 'https://zagpebslab.blob.core.windows.net/sql-backups',
	47	@BackupType = 'Full',
	48	@CopyOnly = 'Y',
	49	@Compress = 'Y',
	50	@Verify = 'N'
7.1	51	{{/code}}
5.1	52
10.1	53	=== ===
5.1	54
9.1	55	=== Conclusion: Backup to Azure Storage Account with TDE Encrypted Databases ===
5.1	56
9.1	57	The attempt to back up an encrypted database (with Transparent Data Encryption - TDE) from SQL Server Management Studio (SSMS) to an Azure Storage Account was unsuccessful. This issue arises because Azure does not permit TDE-encrypted databases to be backed up directly to Azure Storage using SSMS.
5.1	58
9.1	59	However, after decrypting the database, we were able to successfully back it up to the Azure Storage Account via SSMS. This confirms that backups can be performed on an unencrypted database, but not on an encrypted database.
5.1	60
	61
	62
	63
	64
9.1	65	= 2. Enabling Transparent Data Encryption (TDE) Using Azure Key Vault =
5.1	66
	67
9.1	68	Introduction: This document outlines the process of enabling Transparent Data Encryption (TDE) on an Azure SQL Managed Instance (SQL MI) using a Customer-Managed Key (CMK) stored in Azure Key Vault. The encryption is managed using an asymmetric key from Azure Key Vault, ensuring that all data within the SQL Managed Instance is encrypted using a strong encryption algorithm.
5.1	69
	70
9.1	71	===== 1. Generate a Key in Azure Key Vault =====
5.1	72
	73
9.1	74	- Navigate to Azure Key Vault:
5.1	75
9.1	76	* Go to the Azure Portal and select Key Vault (DemoRudiTest).
5.1	77
9.1	78	- Generate a New Key:
	79
	80	* Go to the Keys section and click Generate to create a new key.
	81
	82	- Configure Key Settings:
	83
	84	* Name the Key: Choose a name for your key, e.g., mysqlmikey.
	85	* Key Type: Select RSA as the key type.
	86	* RSA Key Size: Choose an RSA key size of 2048-bit. (optional)
	87
	88	Note:
	89
7.1	90	* 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.
	91	* 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.).
	92	* 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	93
9.1	94	- Create the Key:
5.1	95
9.1	96	* Click Create to generate the key.
5.1	97
9.1	98	===== 2. Enable TDE on the SQL Managed Instance =====
5.1	99
10.1	100	===== =====
5.1	101
9.1	102	- Navigate to Your Managed Instance:
5.1	103
9.1	104	* Go to your SQL Managed Instance (sqlmi-ebs-lab).
5.1	105
9.1	106	- Enable Transparent Data Encryption (TDE):
5.1	107
9.1	108	* Under Security, select Transparent Data Encryption.
7.1	109
9.1	110	- Configure TDE with a Customer-Managed Key (CMK):
7.1	111
9.1	112	* Select Customer-managed key as the encryption type.
	113	* Choose the key you created earlier from Azure Key Vault (mysqlmikey).
7.1	114
9.1	115	- Set the Key as Default TDE Protector:
7.1	116
9.1	117	* Make the key the default TDE protector for your instance.
7.1	118
9.1	119	- Save Configuration:
7.1	120
9.1	121	* Click Save to apply the changes.
7.1	122
9.1	123	=== Conclusion ===
7.1	124
9.1	125	After following these steps, TDE has been successfully enabled on your SQL Managed Instance using an asymmetric key stored in Azure Key Vault. All databases within the instance are now encrypted using the same encryption key (identified by the same encryption thumbprint). You can now securely back up these encrypted databases from SSMS to Azure Storage.
7.1	126
9.1	127	This process ensures that your data is protected both at rest and during backup, offering enhanced security for your managed databases in the cloud.
7.1	128
	129
9.1	130
	131	=== 1. Backup Specific Databases Using SQL Server Agent Jobs ===
	132
	133	This document describes the process of creating an automated SQL Server Agent Job to back up only the DBA databases in a SQL Server instance. The backups will occur daily at 3:00 AM and will be stored in an Azure Storage Account for secure and reliable cloud storage.
	134
	135	----
	136
	137	==== 1. Create the Backup Script ====
	138
	139
	140	====== - Declaring Variables ======
	141
8.1	142	{{code language="sql"}}
	143	DECLARE @DatabaseName NVARCHAR(128)
	144	DECLARE @BackupContainerURL NVARCHAR(512)
	145	{{/code}}
7.1	146
8.1	147	DECLARE @DatabaseName NVARCHAR(128) DECLARE @BackupContainerURL NVARCHAR(512)
7.1	148
8.1	149	* @DatabaseName: A variable to hold the name of the database during each iteration of the loop.
	150	* @BackupContainerURL: A variable to store the URL of the Azure Blob Storage container where the database backups will be stored.
	151
	152	----
	153
9.1	154	====== 2. Setting the Azure Blob Storage URL ======
8.1	155
	156	{{code language="sql"}}
	157	SET @BackupContainerURL = 'https://<your_storage_account>.blob.core.windows.net/sql-backups/'
	158	{{/code}}
	159
	160
	161	----
	162
9.1	163	====== 3. Declaring the Cursor ======
8.1	164
	165
	166	{{code language="sql"}}
	167	DECLARE db_cursor CURSOR FOR
	168	SELECT name
	169	FROM sys.databases
	170	WHERE name LIKE 'dba%' -- Only pick databases that start with 'dba'
	171	{{/code}}
	172
	173	* Cursor Declaration:
	174	** The cursor db_cursor is declared to loop through the list of databases in the sys.databases system view.
	175	The WHERE name LIKE 'dba%'** clause ensures that only databases with names starting with dba will be processed. For example, databases like dbaTest, dbaProd, etc., will be included in the loop.
	176	sys.databases**: A system catalog view that contains information about each database in the SQL Server instance.
	177
	178	----
	179
9.1	180	====== 4. Opening the Cursor ======
8.1	181
	182	{{code language="sql"}}
	183	OPEN db_cursor
	184	FETCH NEXT FROM db_cursor INTO @DatabaseName
	185	{{/code}}
	186
	187	OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
	188
	189	* OPEN db_cursor: This opens the cursor for reading the results.
	190	* FETCH NEXT: The FETCH NEXT statement retrieves the first database name that meets the LIKE 'dba%' condition and stores it in the @DatabaseName variable. This will be used in the next step where the backup procedure is executed.
	191
	192	----
	193
9.1	194	====== 5. Looping Through Databases ======
8.1	195
	196	{{code language="sql"}}
	197	-- Loop through each database and execute the stored procedure
	198	WHILE @@FETCH_STATUS = 0
	199	BEGIN
	200	-- Print current database for logging/debugging
	201	PRINT 'Backing up database: ' + @DatabaseName
	202
	203	-- Execute the DatabaseBackup stored procedure for each database
	204	EXECUTE dba.dbo.DatabaseBackup
	205	@Databases = @DatabaseName, -- Specify the current database
	206	@URL = @BackupContainerURL, -- Azure Blob Storage URL
	207	@BackupType = 'Full', -- Full backup type
	208	@CopyOnly = 'Y', -- Use copy-only backup (this doesn’t affect the transaction log chain)
	209	@Compress = 'Y', -- Enable compression
	210	@Verify = 'N'; -- Skip verification after backup
	211
	212	{{/code}}
	213
	214
	215	* WHILE @@FETCH_STATUS = 0: The loop will continue as long as the FETCH command successfully retrieves a database name. @@FETCH_STATUS is a system function that returns 0 if the fetch operation is successful.
	216	* PRINT: This line prints the name of the database that is currently being backed up. It helps with logging or debugging purposes, as you can track which database is being processed at any given time.
	217	* (((
	218	EXECUTE dba.dbo.DatabaseBackup:
	219
	220	* This calls a stored procedure named dba.dbo.DatabaseBackup for each database.
	221	* The parameters passed to the stored procedure:
	222	@Databases = @DatabaseName**: Specifies which database to back up (the current database being processed).
	223	@URL = @BackupContainerURL**: Specifies the destination Azure Blob Storage URL.
	224	@BackupType = 'Full': Specifies the backup type, which is a Full** backup (this means all data in the database is backed up).
	225	@CopyOnly = 'Y': Specifies that this is a copy-only backup**. This ensures that the backup does not affect the transaction log chain and does not interfere with regular backups.
	226	@Compress = 'Y'**: Enables compression of the backup, reducing the size of the backup file.
	227	@Verify = 'N'**: Specifies that the backup verification step should be skipped. (Verification can be added if needed for additional safety.)
	228	)))
	229
	230	----
	231
9.1	232	====== 6. Fetch the Next Database ======
8.1	233
	234	{{code language="sql"}}
	235	-- Fetch the next database in the cursor
	236	FETCH NEXT FROM db_cursor INTO @DatabaseName
	237
	238	{{/code}}
	239
	240	~-~- Fetch the next database in the cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
	241
	242	* FETCH NEXT: After executing the backup for the current database, this statement retrieves the next database from the cursor and stores it in the @DatabaseName variable. The loop will continue until all matching databases have been processed.
	243
	244	----
	245
9.1	246	====== 7. Closing and Deallocating the Cursor ======
8.1	247
	248	{{code language="sql"}}
	249	-- Close and deallocate the cursor to clean up resources
	250	CLOSE db_cursor
	251	DEALLOCATE db_cursor
	252
	253
	254	{{/code}}
	255
	256	* CLOSE db_cursor: This closes the cursor once the loop finishes processing all databases.
	257	* DEALLOCATE db_cursor: This deallocates the cursor, freeing up any resources used by the cursor. It’s a good practice to always deallocate cursors to avoid resource leaks.
	258
9.1	259	====== ======
8.1	260
9.1	261	====== 8. Full Script ======
8.1	262
	263
	264	{{code language="sql"}}
	265	DECLARE @DatabaseName NVARCHAR(128)
	266	DECLARE @BackupContainerURL NVARCHAR(512)
	267
	268	SET @BackupContainerURL = 'https://zagpebslab.blob.core.windows.net/sql-backups'
	269
	270	DECLARE db_cursor CURSOR FOR
	271	SELECT name
	272	FROM sys.databases
	273	WHERE name LIKE 'dba%'
	274
	275	OPEN db_cursor
	276	FETCH NEXT FROM db_cursor INTO @DatabaseName
	277
	278	WHILE @@FETCH_STATUS = 0
	279	BEGIN
	280
	281	PRINT 'Backing up database: ' + @DatabaseName
	282
	283	EXECUTE dba.dbo.DatabaseBackup
	284	@Databases = @DatabaseName,
	285	@URL = @BackupContainerURL,
	286	@BackupType = 'Full',
	287	@CopyOnly = 'Y',
	288	@Compress = 'Y',
	289	@Verify = 'N';
	290
	291	FETCH NEXT FROM db_cursor INTO @DatabaseName
	292	END
	293
	294	CLOSE db_cursor
	295	DEALLOCATE db_cursor
	296
	297	{{/code}}
	298
	299
	300
	301
11.1	302	=== ===
8.1	303
	304
	305
	306
	307
	308
	309
	310
	311
	312
	313
	314
	315
	316
11.1	317

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?