How to set up TDE, back up certificates, and restore all to a new server

/**

-- This lets us look at existing encryption bits.

select * from sys.symmetric_keys

select [name]
, [is_master_key_encrypted_by_server]
, [is_encrypted]
from master.sys.databases


- This defines a master key

USE MASTER;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO

select * from sys.certificates

-- Now create the certificate

CREATE CERTIFICATE [SQL-A]
WITH SUBJECT = 'SQL-A CERTIFICATE'

select * from sys.certificates

-- Need to be in the user database

use mydb
go

-- Create an encryption key within the user database
-- I wonder if AES_128 is appropriate

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE [SQL-A];
GO

select [name]
, [is_master_key_encrypted_by_server]
, [is_encrypted]
from master.sys.databases

-- This turns on encryption for that database

alter database mydb
set encryption on 
go

select [name]
, [is_master_key_encrypted_by_server]
, [is_encrypted]
from master.sys.databases

select * from students;

-- Backups are not that important.
-- Restores, however, are critical.

use master;
go

-- So let us back up the certificate

BACKUP CERTIFICATE [SQL-A]
TO FILE = 'C:\BACKUP\SQL-A.CER'
WITH PRIVATE KEY 
(
FILE = 'C:\BACKUP\MYDB.PVK',
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
);
GO

-- And back up the actual database

BACKUP DATABASE [mydb] 
TO  DISK = N'C:\backup\mydb.bak' WITH NOFORMAT, NOINIT,  NAME = N'mydb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


-- We can restore that database to the original server 
-- without messing with certificates, keys, and passwords.
-- This would work if we were restoring on the same server as we started on.
-- That's because the certificate and keys are already installed/configured.
-- The restored database would already be encrypted.

USE [master]
RESTORE DATABASE [mydb2] 
FROM  DISK = N'C:\backup\mydb.bak' 
WITH  FILE = 1,  
  MOVE N'mydb' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mydb2.mdf',
  MOVE N'mydb_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mydb2_log.ldf',  
  NOUNLOAD,  STATS = 5

GO

-- The remaining commands would be run on a NEW destination server.
-- This server would not have an existing master key 
-- nor would it have the original certificates.

select * from sys.symmetric_keys

select [name]
, [is_master_key_encrypted_by_server]
, [is_encrypted]
from master.sys.databases

-- So we need to set up a bunch of stuff.

USE [master]

-- Notice we have a brand new server with a brand new master key password

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NewPa$$w0rd';
GO

-- This is the critical key component.  
-- Here we re-create the original certificate
-- from the original certificate, private key, and password.
-- Note I had some trouble with SQL telling me that the certificate did not exist or that I did not have permissions to the file
-- It turned out there was an NTFS permission issue on the file.
-- When I copied the files to a new directory instead of attempting to restore from the original directory, 
-- all worked well.
-- So there was an NTFS permission issue, but I do not know exactly what.

CREATE CERTIFICATE [SQL-A]
FROM FILE = 'c:\RESTORE\SQL-A.CER'
WITH PRIVATE KEY (FILE = 'c:\RESTORE\MYDB.PVK',
DECRYPTION BY PASSWORD = 'Pa$$w0rd')
GO

-- And now we can restore the actual database.

RESTORE DATABASE [mydb] 
FROM  DISK = N'C:\RESTORE\mydb.bak' 
WITH  FILE = 1,  
  MOVE N'mydb' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.ALTERNATE\MSSQL\DATA\mydb.mdf',
  MOVE N'mydb_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.ALTERNATE\MSSQL\DATA\mydb_log.ldf',  
  NOUNLOAD,  STATS = 5

GO






*/

How to tell if TDE (encryption) has been enabled on a SQL server

Say you inherited a pile of SQL servers.  How do you tell if a database has ever been encrypted on any of them?

Run this query on each server.  For bonus points, use something like Idera Multi Query so it runs against all the servers at once.  Multi Query is part of Idera's SQL Admin Toolset.

select [name], is_master_key_encrypted_by_server, is_encrypted

from master.sys.databases

go

Another approach is to view the symmetric keys:

select
        [name]
      , [principal_id]
      , [algorithm_desc]
      , [create_date]
from master.sys.symmetric_keys

If a master key exists, you will find one of the [name] fields populated with "##MS_DatabaseMasterKey##"

Fix Time Problems on Multiboot OS X and Windows System

Registry location:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation

Add DWORD:
RealTimeIsUniversal

Value:
1

Reboot to OSX, then back to Windows.