Creating a new master key with a known password

Last Updated : Apr 04, 2022 |

About this task

If a master key is already created on the SQL server, an error is displayed if a user attempts to create another master key. If you do not know the password for the existing master key, you must decrypt all databases, then drop all the certificates associated with the master key and create a new master key with a known password.

You need to recreate any of the certificates that were dropped but are still required for encryption. Then, re-encrypt the databases with the new certificates.

Note:

Ensure that you store the master key password in a secure location for future use.

Procedure

  1. To view all certificates, run the following command: 
    SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date
FROM sys.certificates;
  2. To drop a certificate, run the following command: 
    DROP CERTIFICATE <nameOfCert>

    If the certificate cannot be dropped because it is in use with a database encryption key, drop the database encryption key.

  3. To drop the database encryption key, run the following command: 
    USE <name of database >
GO
DROP DATABASE ENCRYPTION KEY 
GO

    If a database encryption key cannot be dropped, turn off encryption for that database.

    Note:

    Turning off encryption can take a significant amount of time. For example, decrypting 100GB of data can take approximately one hour.

  4. To turn off encryption for the database, run the following command: 
    ALTER DATABASE <name of database > SET ENCRYPTION OFF;
  5. To drop the master key, run the following command: 
    DROP MASTER KEY