Sql Server Tde

Front 1

Steps to use TDE on DB

Back 1

1. create master key
2. create certificate protected by master key
3. create db encryption key and protect it w/ certificate
4. set db to use encryption

Front 2

Steps to use TDE on DB transact sql example

Back 2

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

Front 3

sys.databases

Back 3

Catalog view that displays database info

Front 4

sys.certificates

Back 4

catalog view that shows the certificates in a database

Front 5

sys.dm_database_encryption_keys

Back 5

Dynamic Management view that provides info about the encryption keys used in a database, and the state of encryption of the database.

Front 6

Restrictions - Operations not allowed during initial db encryption, key change or database decryption

Back 6

1. dropping a file from a filegroup in the db
2. dropping the db
3. taking the db offline
4. detaching the db
5. transitioning a db or filegroup into a "read only" state

Front 7

Restrictions - Operations not allowed during the create, alter, drop encryption key or alter db..set encryption statements

Back 7

1. dropping a file from a filegroup in the db
2. dropping the db
3 taking the db offline
4. transitioning a db or filegroup into a "read only" state
5. using the alter database command
6. starting a db or db file backup
7. starting a db or db file restore
8. creating a snapshot

Front 8

Restrictions - operations and conditions that will prevent create, alter, drop encryption key or alter db..set encryption statements

Back 8

1. db is read-only or has any read-only file groups
2. an alter db command is executing
3. any data backup is running
4. the db is in an offline or restore condition
5. the snapshot is in progress
6. DB maintenance tasks

Front 9

TDE and tempdb system database

Back 9

The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This might have a performance effect for unencrypted databases on the same instance of SQL Server. For more information about the tempdb system database, see tempdb Database.

Front 10

TDE and Replication

Back 10

Replication does not automatically replicate data from a TDE-enabled database in an encrypted form. You must separately enable TDE if you want to protect the distribution and subscriber databases. Snapshot replication, as well as the initial distribution of data for transactional and merge replication, can store data in unencrypted intermediate files; for example, the bcp files. During transactional or merge replication, encryption can be enabled to protect the communication channel. For more information, see Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).

Front 11

TDE and Filestream Data

Back 11

FILESTREAM data is not encrypted even when TDE is enabled.