We have a production database in which the data is encrypted. The encryption follows the following hierarchy
1) The data is encrypted using Symmetric key
2) The symmetric key was encrypted using asymmetric key
3) The asymmetric key was encrypted using the master key of the database
4) The master key of the database was encrypted using user defined password
We created a MASTER KEY for the test database which encrypted using password
o Use test
o create master key encryption by password=’password’
· We then created a asymmetric key which is encrypted by MASTER KEY of the database
o Use test
o open master key decryption by password=’password’
o create asymmetric key MSSQL with algorithm = RSA_2048
o go
· In a test database with data, we then create a symmetric key which is used to encrypt the data
o Use test
o create symmetric key MSSQLSYM WITH ALGORITHM = AES_256 encryption by asymmetric key MSSQL.
o go
· We can check the presence of the keys
o Select * from sys.symmetric_keys
o Select * from sys.asymmetric_keys
· We took the backup of the production database test and wanted to restore the database in the test environment.
· However we wanted to change the password with which the master key is encrypted on the test environment so that password of the original database is not available to the developer who use the database in test environment.
· We then restored the backup of the database test on other instance for the first time. By creating a new database and not overwriting on the existing database
· When we restore the backup in the new instance we want to regenerate the master key of the database so we fire the following commands
o open master key decryption by password=’password’
o alter master key regenerate with encryption by password = ‘password’
o close master key
· However when we take a backup again from the original server and restore it on the new server by overwriting on the existing database when we fire the same set commands to regenerate the master key we received the following commands
o open master key decryption by password=’password’
o alter master key regenerate with encryption by password = ‘password’
o Msg 15320, Level 16, State 2, Line 1
o An error occurred while decrypting asymmetric key ‘MSSQL’ that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable.
· When we query sys.databases table in the source database and checked the column is_master_key_encrypted_by_server. We found that it was set 1 which means the Master key was encrypted using password along with the service master key of the sql server.
· When we restored the database to the new server for the first time. When we checked the is_master_key_encrypted_by_server column from the sys.database we found the column value was 0 which indicates that MASTER KEY was encrypted only using password and not the SERVICE MASTER KEY of the sql server.
· Therefore we regenerated the MASTER KEY it worked fine without any error. Also after regenerating the master key when we check the table sys.databases again we find that column is_master_key_encrypted_by_server is set to 1 which means that the regeneration encrypts the master key by password and the SERVICE MASTER KEY of the TEST sql server instance
· However when we restore the database on the existing database by overwrting on it. The master key is encrypted using password and SERVICE MASTER KEY so the regeneration fails.
· So in order to regenerate the master key of the database we first need to drop the encryption of the service master key using the following command
· alter master key drop encryption by service master key
· and after dropping the encryption of service master key explicitly when we regenerate the master key using the same command which is used by cx it works fine
· alter master key regenerate with encryption by password = ‘password’
