Alternatively, create a Service Master Key (SMK) based on an exported server certficate from your MMC console - export as a pfx with a password. Using that certificate, engage in the following steps
CREATE MASTER KEY ENCRYPTION BY PASSWORD='Password1234!@#$'
From you exported certificate, run the batch file
@echo off
echo Create a new password at the pop-up.
echo This is NOT the same as the one you provided above.
echo You'll be asked to create, confirm and re-enter the password (total 3 times)
echo Creating Certificate (CER) and Key (PVK).
"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.1A\Bin\x64\makecert.exe" -sv %1.pvk -n "CN=%2" %1.cer
echo On prompt, again (4th and last time) provide the password you just created.
echo Merging the two files into a PFX now.
"C:\Program Files (x86)\Windows Kits\8.0\bin\x64\pvk2pfx.exe" -pvk %1.pvk -spc %1.cer -pfx %1.pfx -po %3
CREATE YOUR ASSYMETRIC KEY
CREATE CERTIFICATE MYDB_ASYMMETRIC_CA_KEY FROM FILE='C:\MYDB_ASSYMETRIC_CA_KEY.cer'
WITH PRIVATE KEY(FILE='C:\ MYDB_ASSYMETRIC_CA_KEY.pvk'
DECRYPTION BY PASSWORD='Password1234!@#$');
For redundancy, check that your master key is in place... it is a must
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD='Password1234!@#$'
NOW CREATE THE ACTUAL KEY
CREATE CERTIFICATE MYDB_ASYMMETRIC_CA_KEY FROM FILE='c:\MYDB_ASYMMETRIC_CA_KEY.cer'
WITH PRIVATE KEY (FILE='C:\MYDB_ASYMMETRIC_CA_KEY.pvk',
DECRYPTION BY PASSWORD='Password1234!@#$');
ALWAYS ALWAYS Backup your certificate in the event you need to move to another server. You'll need it to decrypt your backup files
BACKUP CERTIFICATE MYDB_ASYMMETRIC_CA_KEY TO FILE='MYDB_ASYMMETRIC_CA_KEY_BACKUP.bkp'
WITH PRIVATE KEY(FILE='MYDB_ASYMMETRIC_CA_KEY_BACKUP.bkp',
ENCRYPTION BY PASSWORD='Password1234!@#$');
CREATE Database Level Encryption to wrangle in your certificate
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM=AES_256
ENCRYPTION BY SERVER CERTIFICATE MYDB_ASYMMETRIC_CA_KEY
Check the steps above before going to the next step as from here on in, your database will be encrypted with an Assymentric Key - if you haven't backed it up (the key) and you try to restore from another server, it won't be possible without all the above steps in place.
On another database - for example a Staging Database - Restore the Master key from the certificate.
You have your Service Master Key in place, to encrypt the database, you need to encrypt the Database Master Key as this allows the export of the database to another server using the certificate you've created above.
TESTING THE Certificate by importing to another server
RESTORE MASTER KEY FROM FILE='C:\ MYDB_ASSYMETRIC_CA_KEY_BACKUP'
DECRYPTION BY PASSWORD='Password1234!@#$', ENCRYPTION BY PASSWORD='Password1234!@#$'
,[FORCE]
Check to see if the Content exists on the alternate server (this will work on the primary as well)
USE [AUTHORIZATION];
IF NOT EXISTS ( SELECT
1
FROM
sys.dm_database_encryption_keys
WHERE
DB_NAME(database_id) = DB_NAME() )
SELECT
DB_NAME() AS [Database Name]
,'No database encryption key present, no encryption' AS [Encryption
State]
ELSE
SELECT
DB_NAME(database_id) AS [Database Name]
,CASE encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
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 State]
FROM
sys.dm_database_encryption_keys
WHERE
DB_NAME(database_id) = DB_NAME();
