Introduction
Before this article, I highly recommend reading Encrypt & Decrypt in SQL Server Part-1.
In this article, we will discuss Database Master Key.
![Database Master Key]()
To create a Database Master Key, first, create the database "TestDB" in SQL Server and execute the following commands.
USE TestDB GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '@k$h@yPatel'
The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys in the database. To enable the automatic decryption of the master key, a copy of the key is encrypted using the service master key and stored in the database and the master. Now let's see how to check whether the service master key encrypts it. For that, execute the following statement.
SELECT is_master_key_encrypted_by_server,* FROM sys.databases
![Encrypt-Decrypt-in-SQL-Server1.jpg]()
The Is_master_key_encrypted_by_server column value of the TestDB database specifies that the service master key encrypts the master key.
We can change this setting by altering the master key.
Alter Database Master Key
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
We can reset the above setting by executing the following command. Since we drop encryption by the service master key, we must explicitly open the database master key with a password.
OPEN MASTER KEY DECRYPTION BY PASSWORD = '@k$h@yPatel'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Back up Database Master Key
USE TestDB
GO
BACKUP MASTER KEY TO FILE = 'D:\TestDB.DMK'
ENCRYPTION BY PASSWORD='@k$h@yPatel'
The database master key is used to encrypt other keys and certificates. If this key is deleted or corrupted, it is tough to decrypt those keys, and the encrypted data using those keys may be lost, so it is advisable to take a backup of the database master key.
We can restore the database master key by executing the following statement. If there is no master key available in the database, then the following statement creates a new master key. Still, the only difference is that it is not encrypted automatically with the service master key.
Restore Database Master Key
RESTORE MASTER KEY FROM FILE = 'D:\TestDB.DMK'
DECRYPTION BY PASSWORD = '@k$h@yPatel'
ENCRYPTION BY PASSWORD='@k$h@yPatelC#'
"Encryption by password" specifies the password used to encrypt the database master key after it has been loaded into the database.
Conclusion
In the following article, we will generate a certificate and encrypt and decrypt the string using that certificate.
recommend reading Encrypt & Decrypt in SQL Server Part-1
Next >> Encrypt and Decrypt in SQL Server: Part 3