How to configure Transparent Data Encryption (TDE) in SQL Server

Introduction

Transparent Data Encryption (TD) was introduced in SQL Server 2008. Its main purpose was to protect data by encrypting the physical files, both the data (mdf) and log (ldf) files (as opposed to the actual data stored within the database). TDE encrypts SQL Server, Azure SQL Databases, and Azure SQL Data Warehouse data files.

Instructions

step-by-step guide:

  1. Create a master key
  2. Create or obtain a certificate protected by the master key
  3. Create a database encryption key and protect it by using the certificate.
  4. Set the database to use encryption.

The following example shows the encryption and decryption of the StackOverflow database using a certificate named MyServerCert that’s installed on the server.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE StackOverflow;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE StackOverflow
SET ENCRYPTION ON;
GO

The encryption and decryption operations are scheduled on background threads by SQL server. You can use the DMVs to query the cert and key information.

What happens when you enable TDE?

To enable TDE on a database, SQL server must do an encryption scan. The scan reads each page from the data files into the buffer pool and then writes the encrypted pages back out to disk.

Is Tempdb encrypted too when you enable TDE on any database in a SQL instance?

Yes, the tempdb system database is encrypted if any other database on the SQL server instance is encrypted by using TDE. This encryption might have a performance effect for unencrypted databases on the same SQL instance.

Are you able to add an encrypted database to an AlwaysOn availability group?

Yes, you can. To encrypt databases that are part of an availability group, create the master key and certificates on all secondary replicas before creating the database encryption key on the primary replica.

If a certificate is used to protect the database encryption key, backup the certificate created on the primary replicate, and then create the certificate from a file on all secondary replicas before creating the database encryption key on the primary replica.

Here is the script to view all the encrypted DBs and their certificate names on a SQL server instance:

SELECT db_name(database_id) AS [Database Name],
	dek.key_length as [Key Length],
	case encryption_state when '0' then 'No database encryption key present, no encryption'
	when '1' then 'Not Encrypted' 
	when '2' then 'Encryption in Progress'
	when '3' then 'Encrypted'
	when '4' then 'Key Change in Progress'
	when '5' then 'Decryption in Progress'
	when '6' then 'Protection Change in Progress'
	end as [Encryption Status] ,
	key_algorithm as [Key Algorithm],
	Name as [Cert Name],
	pvt_key_encryption_type_desc as [Pvt Key Desc],
	[subject] as [Subject],
	[expiry_date] as [Expiry Date],
	[start_date] as [Start Date]
FROM sys.certificates c
INNER JOIN sys.dm_database_encryption_keys dek ON c.thumbprint = dek.encryptor_thumbprint

Get alerts when SQL server reboots

How do you find out when your SQL server reboots?

Pretty neat trick illustrated by Brent Ozar on his blog. You write a stored procedure to send an email, and then mark that stored procedure as a startup stored procedure so that it runs automatically whenever SQL Server starts up. You can also try this using an Agent job scheduled to run at Agent startup, but sometimes Agent may not actually start.

So here is the script to do that, the body of the email lists any databases with unusual states.

To set this up, you need to:

  1. Enable startup stored procs
  2. Configure database mail: at least one profile and one operator
  3. If you have multiple profiles and/or operators, configure the table that
    sp_SendStartupMail uses to pick which profile & operator to use
  4. Create sp_SendStartupMail and mark it as a startup stored procedure
USE master;
GO


/* 1. Enable startup stored procs if they're not already enabled: */
IF 0 = (SELECT value_in_use FROM sys.configurations WHERE name = 'scan for startup procs')
	AND 0 = (SELECT value FROM sys.configurations WHERE name = 'scan for startup procs')
	BEGIN

	PRINT '/* WARNING! Startup stored procs not enabled. Run this to enable: */';
	IF 0 = (SELECT value_in_use FROM sys.configurations WHERE name = 'show advanced options')
		BEGIN
		PRINT 'EXEC sp_configure ''show advanced options'', 1;';
		PRINT 'RECONFIGURE;';
		END

	PRINT 'EXEC sp_configure ''scan for startup procs'', 1;';
	PRINT 'RECONFIGURE;';
	PRINT '/* And then restart the SQL Server service. (Or it will take effect automatically on the next restart.) */';
	END
GO


/* 2. Configure database mail: at least one profile and one operator */
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysmail_profile)
	BEGIN
	PRINT 'Database mail is not configured. Configure it: https://www.brentozar.com/blitz/database-mail-configuration/';
	PRINT 'Then create and enable an operator: https://www.brentozar.com/blitz/configure-sql-server-operators/';
	END
ELSE IF NOT EXISTS (SELECT COUNT(*) FROM msdb.dbo.sysoperators WHERE enabled = 1)
	BEGIN
	PRINT 'No operators are enabled. Create and enable one: https://www.brentozar.com/blitz/configure-sql-server-operators/'
	END
ELSE
	PRINT 'No work to do here. Keep going.';
GO



/* Create a table to hold the mail config: */
IF NOT EXISTS(SELECT * FROM sys.all_objects WHERE name = 'sp_SendStartupEmail_Config')
	BEGIN
	CREATE TABLE dbo.sp_SendStartupEmail_Config
		(DatabaseMailProfileName SYSNAME, Recipients VARCHAR(MAX));
	END


/* 3. If you have multiple profiles and/or operators, configure the table that
  sp_SendStartupMail uses to pick which profile & operator to use. */
SELECT 'Profiles' AS table_name, name
	FROM msdb.dbo.sysmail_profile;
SELECT 'Recipients' AS table_name, email_address
	FROM msdb.dbo.sysoperators;
GO


/* Armed with the above list of profiles & recipients, pick the one you want to use: */
INSERT INTO dbo.sp_SendStartupEmail_Config (DatabaseMailProfileName, Recipients)
	VALUES ('DBA', 'sql-alerts@domain.com');
GO




/* 4. Create sp_SendStartupMail and mark it as a startup stored procedure  */
IF OBJECT_ID('dbo.sp_SendStartupEmail') IS NULL
  EXEC ('CREATE PROCEDURE dbo.sp_SendStartupEmail AS RETURN 0;');
GO

ALTER PROC dbo.sp_SendStartupEmail AS
BEGIN
/* More info: https://www.BrentOzar.com/go/startupmail
*/
DECLARE @DatabaseMailProfileName SYSNAME = NULL, 
	@Recipients VARCHAR(MAX) = NULL,
	@StringToExecute NVARCHAR(4000);


/* If the config table exists, get recipients & valid email profile */
IF EXISTS (SELECT * FROM sys.all_objects WHERE name = 'sp_SendStartupEmail_Config')
	BEGIN
	SET @StringToExecute = N'SELECT TOP 1 @DatabaseMailProfileName_Table = DatabaseMailProfileName, @Recipients_Table = Recipients 
		FROM dbo.sp_SendStartupEmail_Config mc
		INNER JOIN msdb.dbo.sysmail_profile p ON mc.DatabaseMailProfileName = p.name;'
	EXEC sp_executesql @StringToExecute, N'@DatabaseMailProfileName_Table SYSNAME OUTPUT, @Recipients_Table VARCHAR(MAX) OUTPUT',
		@DatabaseMailProfileName_Table = @DatabaseMailProfileName OUTPUT, @Recipients_Table = @Recipients OUTPUT;
	END

IF @DatabaseMailProfileName IS NULL AND 1 = (SELECT COUNT(*) FROM msdb.dbo.sysmail_profile)
	SELECT TOP 1 @DatabaseMailProfileName = name
	FROM msdb.dbo.sysmail_profile;

/* If they didn't specify a recipient, use the last operator that got an email */
IF @Recipients IS NULL
	SELECT TOP (1) @Recipients = email_address 
	FROM msdb.dbo.sysoperators o 
	WHERE o.[enabled] = 1 ORDER BY o.last_email_date DESC;

IF @DatabaseMailProfileName IS NULL OR @Recipients IS NULL 
	RETURN;

DECLARE @email_subject NVARCHAR(255) = N'SQL Server Started: ' + COALESCE(@@SERVERNAME, N'Unknown Server Name'),
	@email_body NVARCHAR(MAX);

IF NOT EXISTS (SELECT * FROM sys.databases WHERE state NOT IN (0, 1, 7, 10))
	SET @email_body = N'All databases okay.';
ELSE
	BEGIN
	SELECT @email_body = CONCAT(@email_body, COALESCE(name, N' Database ID ' + CAST(database_id AS NVARCHAR(10))), N' state: ' + state_desc + NCHAR(13) + NCHAR(10)) 
		FROM sys.databases
		WHERE state NOT IN (0, 1, 7, 10);

	IF @email_body IS NULL
		SET @email_body = N'We couldn''t get a list of databases with problems. Better check on this server manually.';
	END


EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = @DatabaseMailProfileName,  
    @recipients = @Recipients,  
    @body = @email_body,  
    @subject = @email_subject ;
END
GO

/* Mark this stored procedure as a startup stored procedure: */
EXEC sp_procoption @ProcName = N'sp_SendStartupEmail',
	@OptionName = 'startup',
	@OptionValue = 'on';
GO


/* To test it, just run it and verify that it runs without error, and you get an email: */
EXEC sp_SendStartupEmail;
GO

Minimum necessary permissions to monitor SQL server instance – Red Gate SQLMonitor

  1. The account used to monitor your SQL Server instances should have the following permissions:
  2. Member of the sysadmin role (role required for Integrity check overdue alerts (to run DBCC DBINFO) and to allow SQL Monitor to tur on the deadlock trace flag.
  3. If you are unable to grant sysadmin permissions to the account. Grant the following permissions:
  4. Member of the db_datareader role on the msdb system database.
  5. Member of the SQL_AgentReader role on the msdb system database.
  6. Member of the db_ddladmin database role on all databases (needed to run sys.dm_db_index_physical_stats() required by the Fragmented index alert).
  7. VIEW ANY DEFINITION server permission.
  8. ALTER TRACE server permissions (if you want to enable trace data).
  9. VIEW SERVER STATE and VIEW DATABASE STATE database permissions on all databases.
  10. Member of the db_owner role on the tempdb database.
  11. EXECUTE on xp_readerrorlog.
  12. Below is the script to grant the non-sysadmin permissions described above:
USE [msdb]
GO
CREATE USER [Domain\SQLServerAccount] FOR LOGIN [Domain\SQLServerAccount]
GO
USE [msdb]
GO
/*Member of the db_datareader role on the msdb system database*/
ALTER ROLE [db_datareader] ADD MEMBER [Domain\SQLServerAccount]
GO
USE [msdb]
GO
/*Member of SQLAgentReader role on the msdb system database*/
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [Domain\SQLServerAccount]
GO
USE [tempdb]
GO
CREATE USER [Domain\SQLServerAccount] FOR LOGIN [Domain\SQLServerAccount]
GO
USE [tempdb]
GO
/*Member of the db_owner  role on the tempdb database*/
ALTER ROLE [db_owner] ADD MEMBER [Domain\SQLServerAccount]
GO
use [master]
GO
/*ALTER TRACE server permission*/
GRANT ALTER TRACE TO [Domain\SQLServerAccount]
GO
use [master]
GO
/*VIEW ANY DEFINITION server permission*/
GRANT VIEW ANY DEFINITION TO [Domain\SQLServerAccount]
GO
USE master;
GRANT EXEC ON xp_readerrorlog TO [Domain\SQLServerAccount];

/* Run the output of the below script in a separate window in order to grant db_ddladmin role and view database state permission to the user*/
USE [master]
GO
DECLARE @UserName VARCHAR(25) = 'Domain\SQLServerAccount'
SELECT 'USE ['+name+'] CREATE USER ['+@UserName+'] FOR LOGIN ['+@UserName+']; ALTER ROLE db_ddladmin ADD MEMBER ['+@UserName+']; GRANT VIEW DATABASE STATE TO ['+@UserName+'];'
FROM sys.databases