Therefore we regenerated the MASTER KEY it worked fine without any error.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. When we restored the database to the new server for the first time.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 query sys.databases table in the source database and checked the column is_master_key_encrypted_by_server.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.
O An error occurred while decrypting asymmetric key ‘MSSQL’ that was encrypted by the old 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 alter master key regenerate with encryption by password = ‘password’
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. By creating a new database and not overwriting on the existing database We then restored the backup of the database test on other instance for the first time. 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 took the backup of the production database test and wanted to restore the database in the test environment. O create symmetric key MSSQLSYM WITH ALGORITHM = AES_256 encryption by asymmetric key MSSQL. In a test database with data, we then create a symmetric key which is used to encrypt the data. O create asymmetric key MSSQL with algorithm = RSA_2048 O open master key decryption by password=’password’ We then created a asymmetric key which is encrypted by MASTER KEY of the database. O create master key encryption by password=’password’ We created a MASTER KEY for the test database which encrypted using password The encryption follows the following hierarchyġ) The data is encrypted using Symmetric keyĢ) The symmetric key was encrypted using asymmetric keyģ) The asymmetric key was encrypted using the master key of the databaseĤ) The master key of the database was encrypted using user defined password We have a production database in which the data is encrypted.
0 kommentar(er)
