SQL 2014
The Master Key is a symmetric key (encryption & decryption both use the same password).
SQL Server use Master Key to protect the private key part of a certificate/asymmetric key (has public & private key).
The following statement creates a Database Master Key.
USE master;GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<user provided password>';
The Master Key is encrypted by the user provided password. Note that a copy of the master key is also stored in the master database and encrypted by the Service Master Key (created at SQL setup), so SQL Server can automatically decrypt the Master Key when it's used.
"When a database is first attached or restored to a new instance of SQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server. You must use the OPEN MASTER KEY statement to decrypt the database master key (DMK). Once the DMK has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY REGENERATE statement to provision the server with a copy of the DMK, encrypted with the service master key (SMK). (from MSDN)"
OPEN MASTER KEY DECRYPTION BY '<user provided password>';
When create a certificate (generates private public key pair), the private key is automatically encrypted by the Database Master Key. Alternatively, you may specify a password to encrypt the private key part.
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
The certificate plus an algorithm (certificate's public key + algorithm) provides a mechanism for the database to encrypt data. This is given a name Database Encryption Key which is not really a key, but just confusion.
USE AdventureWorks2012;GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128 //{ AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
When turn on Encryption on a database, the data is automatically encrypted.
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
When this is done, if you backup a database and try to restore it from another instance, it will failed because the instance cannot decrypt the data.
===========To Restore An Encrypted Database On A Different Instance===============
1. Export the Database Master Key to a file. The file is also encrypted by a new password.
USE AdventureWorks2012; GO OPEN MASTER KEY DECRYPTION BY PASSWORD = '<user provided password>'; //this is not needed if the Master Key is encrypted by the Server Master Key BACKUP MASTER KEY TO FILE = 'c:\temp\AdventureWorks2012_master_key'
ENCRYPTION BY PASSWORD = '<user password for master key file>'; GO
My understanding here is the original Master Key is decrypted and then the clear text key is encrypted again using a different password.
2. Copy the file to the target server and restore the Master Key to the target database instance.
USE AdventureWorks2012; GO RESTORE MASTER KEY FROM FILE = 'c:\backups\keys\AdventureWorks2012_master_key' DECRYPTION BY PASSWORD = '<user password for master key file>' //the password used to encrypted the file in the previous step
ENCRYPTION BY PASSWORD = '<user provided password 2>'; //a new password for the Master Key on the target instance.
GO