Certificate

MSSQL Configure Database Encryption

Configure database encryption and backup keys en certificates. This manual shows the complete chain from Service Master Key to the Database Encryption Key. Please remember to at least backup the Server Certificate (marked: “[*** IMPORTANT ***]”) and store these files in a secure location. Without this certificate you cannot access your data when it’s moved or restored to another server. You should substitute all (random generated) passwords with your own.

STEP 1 – Backup and restore (for verification purposes) Service Master Key.

USE master;
GO

-- Backup Service Master Key...
BACKUP SERVICE MASTER KEY TO FILE = 'X:\Path\To\ServiceMaster.key' 
ENCRYPTION BY PASSWORD = 'ooxuRai4shaid0AvnieLoh6t'

-- Restore Service Master Key...
RESTORE SERVICE MASTER KEY FROM FILE = 'X:\Path\To\ServiceMaster.key' 
DECRYPTION BY PASSWORD = 'ooxuRai4shaid0AvnieLoh6t' FORCE

STEP 2 – Create, backup and restore (for verification purposes) Database Master Key and Server Certificate.

USE master;
GO

-- Create Database Master Key...
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah';
GO

-- Backup Database Master Key...
BACKUP MASTER KEY TO FILE = 'X:\Path\To\Master.key'
ENCRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah'
GO

-- Drop Database Master Key for restore test...
DROP MASTER KEY
GO

-- Restore Database Master Key...
RESTORE MASTER KEY FROM FILE = 'X:\Path\To\Master.key' 
DECRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah'
ENCRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah'
GO

-- Open key and let Service Master Key decrypt (for transparent encryption)...
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah'
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

-- Create Server Certificate...
CREATE CERTIFICATE ServerCertDefault WITH SUBJECT = 'Server Certificate Default'
GO

-- Backup Server Certificate and Key [*** IMPORTANT ***]...
BACKUP CERTIFICATE ServerCertDefault TO FILE = 'X:\Path\To\ServerCertDefault.cer'
WITH PRIVATE KEY ( FILE = 'X:\Path\To\ServerCertDefault.key', 
ENCRYPTION BY PASSWORD = 'Eeh3aeb0EiTh2ohZneil8ueS' );
GO

-- Remove Server Certificate for restore test....
DROP CERTIFICATE ServerCertDefault
GO

-- Restore Server Certificate with Key...
CREATE CERTIFICATE ServerCertDefault 
FROM FILE = 'X:\Path\To\ServerCertDefault.cer'
WITH PRIVATE KEY ( FILE = 'X:\Path\To\ServerCertDefault.key' , 
DECRYPTION BY PASSWORD = 'Eeh3aeb0EiTh2ohZneil8ueS' );
GO

STEP 3 – Turn on database encryption in database.

USE [Database]
GO

-- Create Database Encryption Key...
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ServerCertDefault
GO

-- Enable Encryption...
ALTER DATABASE [Database] SET ENCRYPTION ON
GO

-- Check if database encryption is turned on...
SELECT name, is_encrypted FROM sys.databases WHERE is_encrypted = 1

Results:
name                                                           is_encrypted
-------------------------------------------------------------- ------------
DatabaseName                                                   1

OpenSSL Certificate Commands & Examples

Below script creates al selfgesigned certificate from a private key and removes the password from the key so you can you can use the key-pair in Apache.

# Create Key and Certificate Signing Request (option -des3 creates a triple des encrypted key)...
openssl genrsa -des3 -out server.key 4096
openssl req -new -key server.key -out server.csr

# Remove password from key and sign certificate with key... 
cp server.key server.key.org
openssl rsa -in server.key.org -out server.key
openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt

For ease you can add all key and signing options to a config file. This way you can also add a subjectAlternate to the certificate.

>openssl req -new -config server.cnf -key server.key -out server.csr

# server.cnf #
[ req ]
default_bits = 4096
prompt = no
encrypt_key = no
distinguished_name = dn
req_extensions = req_ext

[ dn ]
C = NL
O = Totietoot
CN = examplefqdn.totietoot.nl

[ req_ext ]
subjectAltName = DNS:examplefqdn.totietoot.nl, DNS:examplealtname.totietoot.nl

Use the following command to convert the key-pair to pkcs12 format.

openssl pkcs12 -export -in server.crt -inkey server.key [-name tomcat] -out server.p12 -CAfile chain.pem -caname root -chain

Convert pkcs12 file to java keystore (jks):

keytool -importkeystore -deststorepass <password> -destkeypass <password> -destkeystore server.jks -srckeystore server.p12 -srcstoretype PKCS12 -srcstorepass <password> -alias <name>

Decode a certificate request or a x509 certificate:

openssl req -in server.csr -noout -text
openssl x509 -in server.crt -noout -text

Convert a PFX file to PEM-format (single file)…

openssl pkcs12 -in server.pfx -out key-n-certs.pem -nodes

Check certificate and connection using openssl…

openssl s_client -showcerts -connect f.q.d.n:1234

Convert certificate (PEM) to public key…

openssl x509 -inform pem -in certificate.cer -pubkey -noout > pubkey.pem

Add a (CA) certificate to the JAVA CACerts certificate truststore…

"C:\Java\jdk1.8.0_121\bin\keytool" -import -alias ADCERT-CA-1 -keystore "C:\Java\jdk1.8.0_121\jre\lib\security\cacerts" -trustcacerts -file ADCERT-CA-1.cer