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