SQL Server TDE (Data at Rest Encryption)

What is TDE ?

Transparent Data Encryption (TDE)  is used by Microsoft, Oracle, and other Database provider companies to encrypt data files. This solves the compliance issue generally known as Data at Rest Encryption.

TDE was first introduced by Microsoft with SQL Server 2008. TDE is available with Evaluation, Developer and Enterprise Editions only. (Also Datacenter Editions in some older versions).

How TDE works ?

With TDE Database Files are encrypted, hence, prevents the Data Files from exploitation if data files or backups are copied out with bad intentions. 

Some Facts about TDE

• During Backup, if compression is enabled, it cannot take much advantage from compression due to the encryption.
• If any of the database is encrypted on an instance, TempDB is anyways encrypted.
• TDE only encrypts Data at Rest. Data in Motion, Data in Transit or Data in Memory is not encrypted.
• Any File groups which are Read-Only, won't be encrypted, hence TDE implementation will fail.
• Once TDE is enabled, Mirroring or Log Shipped databases on the other side will also need be encrypted as TDE keeps Transaction Logs Encrypted.
• Even though the Certificate Expires, it still encrypts database. Certificate Expiration is more of compliance related and you can set custom expiry date with EXPIRY_DATE option while creating the certificate, by default it is a year.

Encryption Hierarchy (https://msdn.microsoft.com/en-us/library/bb934049.aspx)

• Encryption Hierarchy starts with Windows level Data Protection API (DPAPI). During the setup or installation of SQL Server Instance, the Service Master Key is encrypted with DPAPI. 
• Service Master Encrypts the Database Master Key on Master database.
• Using Database Master key, a Certificate on Master Database is created. 
• The Certificate on Master Database is used by all other databases on same instance to create Database Encryption Key (DEK). 
• Finally the DEK is used to Encrypt the entire User Database.

Implementing TDE

1. Create Master Key on Master Database
2. Create Certificate protected by Master Key on Master Database
3. Create Database Encryption Key using Certificate on User Database that has to be encrypted
4. Change the Database Option setting to Encryption
5. Backup Certificate and Private Key at safe place (optional but recommended)

Demo

Create Master Key

Use Master
Go
Create Master Key
Encryption By Password ='@$trongPa$$word@'
Go


Create Certificate

use master
Go
create Certificate TDE_CERT
WITH SUBJECT ='TDE Certificate'
Go



Create DEK (Database Encryption Key) using the certificate created in last step. This step has to be run on the database where the TDE has to be enabled

Use myDB
Go
Create Database Encryption Key
with algorithm = AES_256
Encryption By Server Certificate TDE_CERT;
GO



Finally Enable TDE for the database

use master
Go
Alter database myDB
Set encryption on
Go

You can check the status of encryption by using DMV: sys.dm_database_encryption_keys

select encryption_state, percent_complete, * from sys.dm_database_encryption_keys

Encryption_States:
0 - No Encryption
1 = Unencrypted
2 = Encryption in Progress
3 = Encrypted
4 = Key Change in Progress
5 = Decryption in Progress (Disabling TDE)
6 = Protection change in progress



Backup Certificate

Use Master
Go
backup certificate TDE_CERT
To FIle='C:\encryption\TDE_CERT.cer'
with private key (file='C:\encryption\pkey.key' ,encryption by password ='@$trongPa$$word@')
Go

Make sure to keep the Certificate and Private key at safe place. Without Certificate and Private Key, database recovery will be impossible.

SQL Server Stops sending database mails - How to Fix and How to resend failed Emails without restarting the SQL Server

Database mail is a nice feature, it is generally intended for internal notifications to DBAs, DBEs, etc.

But sometimes Database Mail feature is taken too far. I come across feedback where production support teams mention that SQL Server stopped sending emails to clients. Well, SQL Server should not be used for sending mass emails and attachments, especially to clients or end users, it does not only impact SQL Server performance but not safe too.

Anyways, principles on a side for this blog.

I assume you already tried these steps and this did not help:

• The recipients' email addresses were checked for correctness.
• The SMTP server was up and running.
• SMTP Server was reachaable, there was no firewall between SQL Server and SMTP server and the port was opened in firewall.

Next, Try these steps:

• Open SSMS:
• Stop Mail Service

USE MSDB
GO
sysmail_stop_sp
GO

•  Start Mail Service

USE MSDB
GO
sysmail_start_sp
GO

This will refresh the Extended Procedure. If this does not help then go to the next step:

• Open SSMS:
• Stop Mail Service

USE MSDB
GO
sysmail_stop_sp
GO

•  Kill DatabaseMail.exe process from Windows Task Manager

• Start Mail Service 

USE MSDB
GO
sysmail_start_sp
GO

• This would also bring up the DatabaseMail.exe process.

Database mail should now work. Any emails awaiting in queue should be picked up though any emails which were failed already will not be sent. Though you can manually resend those emails utilizing metadata from system table "sysmail_faileditems".

declare @recipients varchar(max), @cc varchar(max), @subject nvarchar(max), @body nvarchar(max), @format varchar(10), @attachment varchar(max)

DECLARE failedemails CURSOR FOR

Select recipients, copy_recipients, [subject], body, body_format, file_attachments from msdb.dbo.sysmail_faileditems where send_request_date >= 'If you want to filter with days, enter here'

OPEN failedemails
fetch next from failedemails
into @recipients, @cc, @subject, @body, @format, @attachment
while @@fetch_status = 0
begin EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@copy_recipients = @cc,
@body = @body,
@subject = @subject,
@body_format = @format,
@file_attachments = @attachment;
fetch next from failedemails
into @recipients, @cc, @subject, @body, @format, @attachment
end

close failedemails;
deallocate failedemails;

That's it.