When you are creating the crypto object, you can specify encryption by password. I did say you could break the chain at any level. If you want to restore the keychain and SQL Server being able to automatically open the database master key, you have to then ALTER MASTER KEY and then ADD ENCRYPTION BY SERVICE MASTER KEY. You have to OPEN MASTER KEY with the password to get access to it. That’s why knowing the database master key is important. Typically we see the break when we restore a database to a new server. SQL Server uses it to decrypt the database master key, which then in turn is used to decrypt the certificate or asymmetric key, which is then used to decrypt the symmetric key. To what you say about it being used for encryption, in a typical use case, yes, the service master key is important. The restore command for the SMK is RESTORE SERVICE MASTER KEY.Īgreed. That way if you actually need it, you have it. Also remember that the SMK can change (for example when you change service accounts) so make sure to take these backups on a regular basis also. “Better safe than sorry.” Back it up, store it with your regular backups (making sure you have the password stored somewhere securely in separate location). Not at all.)īasically I like to follow the golden rule. Setting aside the possibility of someone setting up encryption and not telling you (don’t laugh, I’m betting it’s happened to at least one person reading this) did you know that passwords used in a linked server are encrypted? This means that if you have a disaster and you are using linked servers you had best hope that either the SMK is backed up, or you have all of the passwords used in the linked servers. Now you may be thinking “But I’m not using encryption, why should I worry about this?” Well you may be using encryption and not realize it. So if you are using encryption then you absolutely need to backup this key for DR purposes.īacking up the SMK (Service Master Key) is pretty simple using the BACKUP SERVICE MASTER KEY command: BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file' This means that any certificate or key will be encrypted using, in part, the Service Master Key. What I do know however, is that the Service Master Key is the top of the encryption chain on an instance. I’m by no means an expert in SQL Server encryption.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |