This is a continuation of a previous post, in which I discussed the service master key (SMK) and the database master keys. I mentioned in that post that a new encryption will be added to the SMK and I will describe it in this article. Also, a few things have changed since I wrote my previous article, so I’ll provide updates here.
So, the SMK used to be encrypted through DPAPI using the service account credentials. Because this encryption could be invalidated by a service account change, a second encryption was added, which is done using DPAPI and the machine credentials – I will refer to this new encryption as the machine key encryption and to the original encryption as the service account encryption. So the SMK always has these two encryptions: the service account encryption and the machine key encryption. Each of these encryptions can become invalid in a specific scenario: a service account change will invalidate the service account encryption and a cluster failover will invalidate the machine key encryption. On server startup, these two encryptions are verified and if one is determined to be invalid, it gets recreated based on the other valid encryption. While having these two encryptions means that it’s extremely unlikely that the SMK could become undecryptable, I still want to reiterate the advice from my previous post about keeping a backup file of the current SMK at all times.
Because the SMK is verified during server startup, it will be created the first time the server will be started, which normally happens during setup time. In the unlikely event that both encryptions of the SMK are invalid, the server will make an errorlog entry with message 15466 – An error occurred during decryption. So the presence of such a message in the errorlog indicates that the SMK is undecryptable. While the message itself is generic, its presence in the errorlog can only happen because of this specific reason. If such a scenario would arise, the SMK can be restored from a backup using RESTORE SERVICE MASTER KEY with the FORCE option; the FORCE option will be necessary to bypass the decryption of the SMK – because in this case it cannot be performed.
The presence of the two encryptions and the way they get validated during server startup is the mechanism that enables transparent re-encryption of the SMK during service account changes; my previous observation that this is achieved through backup/restore of the SMK is outdated.
I mostly discussed the case of the SMK because this key is the most important key in the SQL Server key hierarchy and also because most of the statements that apply to the SMK also work similarly for the DbMK. One important difference between the SMK and the DbMK is that the DbMK can be encrypted using several passwords (also see the key encryption overview I posted recently). Furthermore, the SMK encryption of the DbMK can be dropped, so that the DbMK can only be used after it is explicitly opened using the OPEN MASTER KEY statement – the SMK encryption can be dropped with the ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY statement. The encryptions of the SMK cannot be dropped – they are managed by the server.
A DbMK must always have a password encryption – the last password encryption cannot be dropped for a DbMK. When moving databases from one server to another, the SMK encryption of the DbMK will have to be readded manually, if it is desired. For this purpose, the DbMK must first be opened using one of its password encryptions, then the ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY statement can be used to add the SMK encryption.
I hope you will find this information useful in managing the master keys and troubleshooting issues with them.