Search code examples
sql-serversql-server-2014

Can SQL Server master key be recreated from password?

I am setting up encrypted backups on SQL Server 2014. I will create a database master key (CREATE MASTER KEY ENCRYPTION BY PASSWORD='...') and then an asymmetric key. Microsoft recommends keeping an off-site backup of the master key.

If I have the password used to create the master key, but not the backed-up master key itself, can I recreate the master key, or would the backups become inaccessible?

Solution

  • No, it cannot.

    The command is really broken up like this:

    CREATE MASTER KEY
                  ENCRYPTION BY PASSWORD = '...'

    The first part creates a new random key, the second part encrypts it for storage in the database. The password is not used to generate the key, it is used to protect it.

    As such, if you lose the key, you cannot recreate it.