sql-tde - XWiki

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 --

Perform the database backup with the provided parameters

24

EXECUTE dba.dbo.DatabaseBackup

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

GO

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>

= **Using TDE directly from azure portal** =

46

47

48

===== 1 Go to azure key vault ( DemoTestRudi) to generate a key =====

49

50

51

- Go to keys and click generate

52

53

- Name your key (mysqlmikey)

54

55

- Choose a key type (RSA)

56

57

-Choose RSA key size (2048-bit)

Note:

* 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.

- Click create

[[image:image-20250228120622-1.png||height="236" width="542"]]

69

70

71

===== 2 Enable TDE =====

72

73

74

- Go to your Managed instance ( sqlmi-ebs-lab)

75

76

- Click on security and choose Transparent data encryption

77

78

- Select the type of managed key ( Customer-managed key0

79

80

- Select the key from the key vault we generated in the key vault( mysqlmikey)

81

82

- Make the key the default TDE protector

-Click save

[[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.

1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';

110

GO

111

112

CREATE CERTIFICATE TDE_Certificate

113

WITH SUBJECT = 'TDE Certificate';

GO

2 USE master;

GO

SELECT

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

124

FROM

125

sys.certificates cert

126

WHERE

127

cert.name LIKE 'TDE%';

128

129

130

3 BACKUP CERTIFICATE TDE_Certificate

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!'

);

GO

USE Normal;

GO

-- 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;

GO--

USE [dba]

GO

CREATE DATABASE ENCRYPTION KEY

152

WITH ALGORITHM = AES_256

153

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

GO

USE [dba1]

GO

CREATE DATABASE ENCRYPTION KEY

161

WITH ALGORITHM = AES_256

162

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

GO

USE [dba2]

GO

CREATE DATABASE ENCRYPTION KEY

170

WITH ALGORITHM = AES_256

171

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

GO

USE [dba3]

GO

CREATE DATABASE ENCRYPTION KEY

179

WITH ALGORITHM = AES_256

180

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

GO

USE [xwiki]

GO

CREATE DATABASE ENCRYPTION KEY

187

WITH ALGORITHM = AES_256

188

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

GO

-- 7. Enable Transparent Data Encryption (TDE) on the database

ALTER DATABASE dba

SET ENCRYPTION ON;

GO--

select name, database_id, state_desc

from sys.databases

SELECT

database_id,

key_algorithm,

key_length,

encryption_state_desc

encryptor_type

FROM

sys.dm_database_encryption_keys;

211

212

213

Select * from sys.dm_database_encryption_keys

214

215

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']]

With copy_only

GO

BACKUP DATABASE [dba2]

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']],

224

\\ COPY_ONLY, -- Ensures the backup does not affect the regular backup chain

225

COMPRESSION, -- Optional: Compresses the backup to save storage space

226

STATS = 10 -- Optional: Provides backup progress status

GO--

-- Step 1: Drop the existing credential (if needed)

232

DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];

233

GO--

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';

239

GO--

240

241

-- Step 3: Perform the database backup with the provided parameters

242

EXECUTE dba.dbo.DatabaseBackup

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

246

@CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain

247

@Compress = 'Y', -- Compress the backup

248

@Verify = 'N'; -- No verification of backup

GO--

select name, database_id, state_desc

from sys.databases

SELECT

database_id,

key_algorithm,

key_length,

encryption_state_desc

encryptor_type

FROM

select * from sys.dm_database_encryption_keys;

268

269

270

select name, is_encrypted from sys.databases

SELECT

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

278

FROM

279

sys.certificates cert

280

WHERE

281

cert.name LIKE 'TDE%';

ALTER DATABASE dba1

SET ENCRYPTION off;

GO

Use dba1;

DROP DATABASE ENCRYPTION KEY;

USE [dba1]

GO

CREATE DATABASE ENCRYPTION KEY

296

WITH ALGORITHM = AES_256

297

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

GO

ALTER DATABASE dba1

SET ENCRYPTION ON

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';

328

GO

329

330

CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';

331

GO

332

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='*****')

336

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

SET ENCRYPTION ON;

GO

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

SET ENCRYPTION ON;

GO

USE master;

GO

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

SET ENCRYPTION ON;

GO

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

SET ENCRYPTION ON;

GO

Wiki source code of sql-tde