Encrypt and Decrypt in SQL Server: Part 2

Introduction

I highly recommend reading Encrypt & Decrypt in SQL Server Part-1 before this article.

In this article we will discuss Database Master Key.



In order 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 that are present in the database. To enable the automatic decryption of the master key, a copy of the key is encrypted by using the service master key and stored in the database and in the master. Now let's see how to check whether it is encrypted by the service master key or not. For that execute the following statement.

SELECT is_master_key_encrypted_by_server,* FROM sys.databases



The Is_master_key_encrypted_by_server column value of the TestDB database specifies that the master key is encrypted by the service 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 then it is very difficult to decrypt those keys and the data that are encrypted 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, but 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 next article we will generate a certificate and encrypt and decrypt the string using that certificate.