Issue: -There is already a master key in the database. Please drop it before performing this statement.
------------------------------------------------------------------------------------------------------------------------USE Master ;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Singapore@123';
GO
Issue:-
Msg 15578, Level 16, State 1, Line 1
There is already a master key in the database. Please drop it before performing this statement.
hence Executing this command
let's see what happen
USE Master;
go
drop Master Key
Go
Msg 15580, Level 16, State 1, Line 1
Cannot drop master key because certificate 'TDECert' is encrypted by it.
it looks we have TDECert Certificate which is encrypted by this master key, Analogy is you have kept your certificate in a Wood drawer. your certificate is protected with this wooden drawer, hence you can not drop this master key.
hence the next step would be to drop the certificate How?
USE Master;
go
drop certificate TDECert
go
In my case this statement executed successfully.
hence you can now drop master key.
Use master;
go
drop master key
go
-- This Command will execute successfully.. if previous drop certificate has ----executed successfully then..
it means your certificate is not bound to any database encryption key.
otherwise you will get error..
Msg 3716, Level 16, State 15, Line 1
The certificate ‘TDECert’ cannot be dropped because it is bound to one or more database encryption key.
This message is very clear and it states that the certificate cannot be dropped as its related to the database Encryption key.
then Execute this command
use Master
Go
Drop database Encryption key
Go;
it will give error
or
we will get error:-
Msg 33102, Level 16, State 7, Line 1
Cannot encrypt a system database. Database encryption operations cannot be performed for 'master', 'model', 'tempdb', 'msdb', or 'resource' databases.
this message is very loud & clear that we can not execute this command in system database.
Msg 33105, Level 16, State 1, Line 1
Cannot drop the database encryption key because it is currently in use. Database encryption needs to be turned off to be able to drop the database encryption key.
it looks we have to turn off encryption before dropping database encryption key..
hence search for which database encryption is enabled and for that database you can turn Encryption off.
/*This Script will give you result output for which database TDE is enabled.
---------------------------------------------------------------------------------------------------------
USE master;
GO
SELECT
db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm,
dm.key_length
FROM
sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id;
GO
----------------------------------------------------------------------------------------------------------------
Use Master
Go
Alter database TestDatabase Set encryption off
Go
-- Command completed successfully.
Now we can successfully drop encryption key for that database
use TestDatabase
go
drop database encryption key
go
Now you can drop in below order..
1. Encryption key -- DROP Database encryption key -- on user database
2. Certificate -- DROP Certificate TDECert -- on master database
3. master key -- DROP Master Key -- on master database
Use Testdatabase
Go
DROP Database encryption key;
go
use master
go
select * from sys.certificates;
--get certificate which you have to drop
DROP Certificate TDECert;
DROP Master Key;
In this way, we can clear our test/Lab environment.
Thank you very much for reading this and if time allows leave Comment.
Extra Reading :- http://www.sqlservercentral.com/articles/Encryption/108750/
