Service Master Key is automatically generated the first time when it is needed. In most cases its created just after SQL Server installed. Because this key is not only required for key or data encryption but also required to encrypt Linked server password and SQL Server credentials. SMK is encrypted by MACHINE KEY (computer specific) and by windows credential of SQL Server’s service account. It means that this key becomes invalid on changing the SQL Server computer or on changing the SQL server service account. But to minimize the management trouble it automatically re-generate SMK when either of these credentials becomes invalid on the basis of remaining valid key. We can also use ALTER SERVICE MASTER KEY statement to alter the service account. But the DBA has to take action when both of these credentials (computer and service account) changes at once.
Service Master Key is the base of complete automatic key management and directly or indirectly encrypts all other keys on that server, it is crucial to take precautions to handle such situations.
So what is the precaution to keep the business running when SMK turns invalid?
And the answer is: have a backup of SMK.
The syntax to take the backup of SMK:
BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file' ENCRYPTION BY PASSWORD = 'password'
For example:
BACKUP SERVICE MASTER KEY TO FILE = 'D:\Key_Backup\SMK' ENCRYPTION BY PASSWORD = 'SQLReality_1'
The password provided here is used to encrypt the service master key in backup file and will require when restoring this SMK. So the complexity and security of this password along with backup file is subject of high concern for successful restore of SMK when needed.
Syntax for restore statement is following:
RESTORE SERVICE MASTER KEY FROM FILE = 'path_to_file' DECRYPTION BY PASSWORD = 'password' [FORCE]
For example:
RESTORE SERVICE MASTER KEY FROM FILE = 'D:\Key_Backup\SMK' DECRYPTION BY PASSWORD = 'SQLReality_1'
Even there are T-SQL statements for ALTER, BACKUP and RESTORE to the Service Master key but there is not CREATE statement to create this key. This can be viewed by below statement:
USE master
GO
SELECT * FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##'
No comments:
Post a Comment