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'

22

GO

23

7

EXECUTE dba.dbo.DatabaseBackup

31

@Databases = @DatabaseName,

32

@URL = @BackupContainerURL,

33

@BackupType = 'Full',

@CopyOnly = 'Y',

@Compress = 'Y',

@Verify = 'N';

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)

40

41

We have unencrypted a database and backed it up from ssms to azure blob successfully

42

43

44

Conclusion: Can be done with and unencrypted DB but not with an Encrypted one>

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

51

52

53

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

54

55

56

- Go to keys and click generate

57

58

- Name your key (mysqlmikey)

59

60

- Choose a key type (RSA)

61

62

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

67

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

68

* 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"]]

74

75

76

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

77

78

79

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

80

81

- Click on security and choose Transparent data encryption

82

83

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

84

85

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

86

87

- Make the key the default TDE protector

-Click save

[[image:image-20250228122106-2.png||height="19" width="241"]]

93

94

95

- TDE has now been enabled on the Managed instance

96

97

98

Conclusion: All the databases have been encrypted by an asymmetric key. The key is the same for each database ( same encryption thumbprint).

99

100

We are now able to backup databases from SSMS to Azure storage.

=== Backup specific databases using SQL server agent jobs ===

105

106

107

we are creating a job to automatically backup only the DBA databases in the instance via the SQL server agent.

108

109

The backup will be done daily at 3am. The backups will be backed up in the Azure storage account.

1 Create the script

=== **1. Declaring Variables** ===

116

117

118

DECLARE @DatabaseName NVARCHAR(128)

119

DECLARE @BackupContainerURL NVARCHAR(512)

120

121

122

DECLARE @DatabaseName NVARCHAR(128) DECLARE @BackupContainerURL NVARCHAR(512)

123

124

* **@DatabaseName**: A variable to hold the name of the database during each iteration of the loop.

125

* **@BackupContainerURL**: A variable to store the URL of the Azure Blob Storage container where the database backups will be stored.

----

=== **2. Setting the Azure Blob Storage URL** ===

130

131

132

SET @BackupContainerURL = 'https://<your_storage_account>.blob.core.windows.net/sql-backups/'

----

=== **3. Declaring the Cursor** ===

DECLARE db_cursor CURSOR FOR

143

SELECT name

144

FROM sys.databases

145

WHERE name LIKE 'dba%' -- Only pick databases that start with 'dba'

146

147

148

* **Cursor Declaration**:

149

** The cursor db_cursor is declared to loop through the list of databases in the sys.databases system view.

150

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

151

** **sys.databases**: A system catalog view that contains information about each database in the SQL Server instance.

----

=== **4. Opening the Cursor** ===

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DatabaseName

160

161

162

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DatabaseName

163

164

* **OPEN db_cursor**: This opens the cursor for reading the results.

165

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

----

=== **5. Looping Through Databases** ===

170

171

172

-- Loop through each database and execute the stored procedure

173

WHILE @@FETCH_STATUS = 0

174

BEGIN

175

-- Print current database for logging/debugging

176

PRINT 'Backing up database: ' + @DatabaseName

177

178

-- Execute the DatabaseBackup stored procedure for each database

179

EXECUTE dba.dbo.DatabaseBackup

180

@Databases = @DatabaseName, -- Specify the current database

181

@URL = @BackupContainerURL, -- Azure Blob Storage URL

182

@BackupType = 'Full', -- Full backup type

183

@CopyOnly = 'Y', -- Use copy-only backup (this doesn’t affect the transaction log chain)

184

@Compress = 'Y', -- Enable compression

185

@Verify = 'N'; -- Skip verification after backup

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

191

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

192

* (((

193

**EXECUTE dba.dbo.DatabaseBackup**:

194

195

* This calls a stored procedure named dba.dbo.DatabaseBackup for each database.

196

* The parameters passed to the stored procedure:

197

** **@Databases = @DatabaseName**: Specifies which database to back up (the current database being processed).

198

** **@URL = @BackupContainerURL**: Specifies the destination Azure Blob Storage URL.

199

** **@BackupType = 'Full'**: Specifies the backup type, which is a **Full** backup (this means all data in the database is backed up).

200

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

201

** **@Compress = 'Y'**: Enables compression of the backup, reducing the size of the backup file.

202

** **@Verify = 'N'**: Specifies that the backup verification step should be skipped. (Verification can be added if needed for additional safety.)

)))

----

=== **6. Fetch the Next Database** ===

208

209

210

-- Fetch the next database in the cursor

211

FETCH NEXT FROM db_cursor INTO @DatabaseName

~-~- Fetch the next database in the cursor FETCH NEXT FROM db_cursor INTO @DatabaseName

216

217

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

----

=== **7. Closing and Deallocating the Cursor** ===

222

223

224

-- Close and deallocate the cursor to clean up resources

CLOSE db_cursor

DEALLOCATE db_cursor

* **CLOSE db_cursor**: This closes the cursor once the loop finishes processing all databases.

232

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

233

234

235

===== 8. Full Script =====

DECLARE @DatabaseName NVARCHAR(128)

240

DECLARE @BackupContainerURL NVARCHAR(512)

241

242

SET @BackupContainerURL = 'https://zagpebslab.blob.core.windows.net/sql-backups'

243

244

DECLARE db_cursor CURSOR FOR

245

SELECT name

246

FROM sys.databases

247

WHERE name LIKE 'dba%'

248

249

OPEN db_cursor

250

FETCH NEXT FROM db_cursor INTO @DatabaseName

251

252

WHILE @@FETCH_STATUS = 0

253

BEGIN

254

255

PRINT 'Backing up database: ' + @DatabaseName

256

257

EXECUTE dba.dbo.DatabaseBackup

258

@Databases = @DatabaseName,

259

@URL = @BackupContainerURL,

260

@BackupType = 'Full',

@CopyOnly = 'Y',

@Compress = 'Y',

@Verify = 'N';

FETCH NEXT FROM db_cursor INTO @DatabaseName

END

CLOSE db_cursor

DEALLOCATE db_cursor

=== 2. Set up a new job ===

275

276

277

- Go to SSMS and click on SQL Server Agent

278

279

Drop down menu and left click jobs and select new job

280

281

282

====== General: ======

283

284

- Enter Job name (DBA databases backup)

285

286

- Owner (ebssqladmin)

287

288

- Category (Database maintenance)

289

290

- Description (Description of the job)

291

292

293

====== Steps: Create the steps for the job to follow ======

294

295

- Step name (Backup only DBA database)

296

297

- Type (Transact-SQL script)

- Database (master)

- Command (paste the script we created)

302

303

304

Schedule: Create a schedule for the job to run

305

306

- Name (DBA database backup)

307

308

- Schedule Type (recurring)

309

310

- Frequency (Occurs: Daily)

311

312

(Recurs every: 1 day(s))

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

323

GO

324

325

CREATE CERTIFICATE TDE_Certificate

326

WITH SUBJECT = 'TDE Certificate';

GO

2 USE master;

GO

SELECT

cert.name AS Certificate_Name,

334

cert.subject AS Certificate_Subject,

335

cert.issuer_name AS Issuer_Name,

336

cert.pvt_key_encryption_type AS Private_Key_Encryption_Type

337

FROM

338

sys.certificates cert

339

WHERE

340

cert.name LIKE 'TDE%';

341

342

343

3 BACKUP CERTIFICATE TDE_Certificate

344

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'

345

WITH PRIVATE KEY (

346

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

347

ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'

);

GO

USE Normal;

GO

-- 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate

356

CREATE DATABASE ENCRYPTION KEY

357

WITH ALGORITHM = AES_256

358

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

GO--

USE [dba]

GO

CREATE DATABASE ENCRYPTION KEY

365

WITH ALGORITHM = AES_256

366

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

GO

USE [dba1]

GO

CREATE DATABASE ENCRYPTION KEY

374

WITH ALGORITHM = AES_256

375

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

GO

USE [dba2]

GO

CREATE DATABASE ENCRYPTION KEY

383

WITH ALGORITHM = AES_256

384

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

GO

USE [dba3]

GO

CREATE DATABASE ENCRYPTION KEY

392

WITH ALGORITHM = AES_256

393

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

GO

USE [xwiki]

GO

CREATE DATABASE ENCRYPTION KEY

400

WITH ALGORITHM = AES_256

401

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;

424

425

426

Select * from sys.dm_database_encryption_keys

427

428

429

BACKUP DATABASE [dba2]

430

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]

436

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

437

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

438

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

439

STATS = 10 -- Optional: Provides backup progress status

GO--

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

445

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

446

GO--

447

448

-- Step 2: Create a new credential with the SAS token

449

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

450

WITH IDENTITY = 'SHARED ACCESS SIGNATURE',

451

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

452

GO--

453

454

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

455

EXECUTE dba.dbo.DatabaseBackup

456

@Databases = 'dba', -- Replace with your database name

457

@URL = '[[https:~~/~~/zagpebslab.blob.core.windows.net/sql-backups'>>https://zagpebslab.blob.core.windows.net/sql-backups']], -- Azure Blob Storage URL

458

@BackupType = 'Full', -- Full backup

459

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

460

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

461

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

481

482

483

select name, is_encrypted from sys.databases

SELECT

cert.name AS Certificate_Name,

488

cert.subject AS Certificate_Subject,

489

cert.issuer_name AS Issuer_Name,

490

cert.pvt_key_encryption_type AS Private_Key_Encryption_Type

491

FROM

492

sys.certificates cert

493

WHERE

494

cert.name LIKE 'TDE%';

ALTER DATABASE dba1

SET ENCRYPTION off;

GO

Use dba1;

DROP DATABASE ENCRYPTION KEY;

USE [dba1]

GO

CREATE DATABASE ENCRYPTION KEY

509

WITH ALGORITHM = AES_256

510

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

GO

ALTER DATABASE dba1

SET ENCRYPTION ON

USE master;

GO

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

541

GO

542

543

CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';

544

GO

545

546

BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'

547

WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',

548

ENCRYPTION BY PASSWORD='*****')

549

550

USE Everest_TDE_Master;

551

GO

552

CREATE DATABASE ENCRYPTION KEY

553

WITH ALGORITHM = AES_256

554

ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;

555

GO

556

ALTER DATABASE Everest_TDE_Master

SET ENCRYPTION ON;

GO

USE Everest_TDE_Master_Documents;

561

GO

562

CREATE DATABASE ENCRYPTION KEY

563

WITH ALGORITHM = AES_256

564

ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;

565

GO

566

ALTER DATABASE Everest_TDE_Master_Documents

SET ENCRYPTION ON;

GO

USE master;

GO

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

587

GO

588

589

CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';

590

GO

591

592

BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'

593

WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',

594

ENCRYPTION BY PASSWORD='*****')

595

596

USE Everest_TDE_Master;

597

GO

598

CREATE DATABASE ENCRYPTION KEY

599

WITH ALGORITHM = AES_256

600

ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;

601

GO

602

ALTER DATABASE Everest_TDE_Master

SET ENCRYPTION ON;

GO

USE Everest_TDE_Master_Documents;

607

GO

608

CREATE DATABASE ENCRYPTION KEY

609

WITH ALGORITHM = AES_256

610

ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;

611

GO

612

ALTER DATABASE Everest_TDE_Master_Documents

SET ENCRYPTION ON;

GO

Wiki source code of sql-tde