Service Master Key, Database Master Key, Certificate , Asymmetric Key, and Symmetric key
When a database master key is created, a copy of it is encrypted by a password and stored in the database. The other copy of it is encrypted by the Service Master Key and stored in the MASTER database.
USE AData
CREATE MASTER KEY ENCRYPTION BY PASSWORD='v@nada1';
USE AdventureWorks
CREATE MASTER KEY ENCRYPTION BY PASSWORD='v@nada1';
When a Database Master Key is needed to encrypt or decrypt a certificate, the Database Master Key is automatically opened from MASTER database using Service Master Key. It is not necessary to use the OPEN MASTER KEY statement to decrypt the Database Master Key.
To see which database's has its copy of master key encrypted by the Service Master Key, you could run the statement:
use adata
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
With above statement, the MASTER database does not have a copy of the database master key of aData encrypted by KEY derived from SQL Server Service account and its password.
I create another database BData without the Database Master Key.
In BData, you cannot create a certificate because it needs the Database Master Key to encrypt it. However, you can use the passphrase to encrypt a text. The following batch has no problem:
USE BData
DECLARE @txt nvarchar(100),@key nvarchar(100)
DECLARE @encryptedData varbinary(200)
DECLARE @decryptedData nvarchar(100)
SET @txt='what are you doing?'
SET @key='V@ncouver'
SET @encryptedData=EncryptByPassPhrase(@key,@txt)
SET @decryptedData=DecryptByPassPhrase(@key,@encryptedData)
SELECT @encryptedData as 'Encrypted Text', @decryptedData as 'Decrypted Text'
=====
=====
Run the following batch --no problem--Database Master Key is automatically retrieved from the copy of MASTER database
USE AdventureWorks
CREATE CERTIFICATE C1 WITH SUBJECT='CDI COLLEGE'
GO
DECLARE @txt nvarchar(100),@key nvarchar(100)
DECLARE @encryptedData varbinary(200)
DECLARE @decryptedData nvarchar(100)
SET @txt='what are you doing?'
SET @encryptedData=EncryptByCert(Cert_id('C1'),@txt)
SET @decryptedData=DecryptByCert(Cert_id('C1'),@encryptedData)
SELECT @encryptedData as 'Encrypted Text', @decryptedData as 'Decrypted Text'
===
===
You must explicitly open the Database Master Key to create the Certificate and encrypt and decrypt the data.
USE AData
OPEN MASTER KEY DECRYPTION BY PASSWORD='v@nada1'
GO
CREATE CERTIFICATE C1 WITH SUBJECT='CDI COLLEGE'
GO
DECLARE @txt nvarchar(100),@key nvarchar(100)
DECLARE @encryptedData varbinary(200)
DECLARE @decryptedData nvarchar(100)
SET @txt='what are you doing?'
SET @encryptedData=EncryptByCert(Cert_id('C1'),@txt)
SET @decryptedData=DecryptByCert(Cert_id('C1'),@encryptedData)
SELECT @encryptedData as 'Encrypted Text', @decryptedData as 'Decrypted Text'
CLOSE MASTER KEY
=====
=====
If you detach a database from instance A and attach it to instance B, and you want a copy of database master key to be stored in MASTER database, you run the following:
use AData
OPEN MASTER KEY DECRYPTION BY PASSWORD='c@nada1'
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
SELECT [name],is_master_key_encrypted_by_server FROM sys.databases
GO