The SQL Server encryption key hierarchy includes a single DMK for each database. The DMK directly encrypts asymmetric keys and certificates that can be used to encrypt symmetric keys. Symmetric keys are used, in turn, to encrypt other symmetric keys and data.
Unlike the SMK, which is generated automatically the first time it is needed, a DMK must be created explicitly with the CREATE MASTER KEY statement. SQL Server includes the following T-SQL statements to manage DMKs:
• CREATE MASTER KEY: Creates a DMK within a database. A password must be supplied to encrypt the DMK in the database when it is created.
• ALTER MASTER KEY: Allows you to regenerate your DMK or to change how the DMK is secured by adding or removing encryption by password or SMK. If you regenerate the DMK, all the keys it protects will be decrypted and re-encrypted.
• DROP MASTER KEY: Drops the DMK from the current database. If any private keys in the current database are protected by the DMK, the DROP statement will fail.
• BACKUP MASTER KEY: Backs up the DMK to a file. You must specify a password, which will be used to encrypt the DMK in the file.
• RESTORE MASTER KEY: Restores the DMK from a file. You must supply the same password you used when backing up the DMK for a RESTORE operation to succeed. You must also supply a second password to encrypt the DMK in the database after it is restored. During the restore process, SQL Server attempts to decrypt and re-encrypt all keys protected by the DMK.
• OPEN MASTER KEY: Opens the DMK so that it can be used for encryption and decryption. The DMK must be open in order for any encryption or decryption operation to succeed, although SQL Server can implicitly open your DMK when it’s protected by the SMK.
• CLOSE MASTER KEY: Closes a DMK that was explicitly opened using OPEN MASTER KEY after you are finished using it for encryption and decryption.
The ALTER MASTER KEY and RESTORE MASTER KEY statements attempt to regenerate the hierarchy of encryption keys that the DMK protects. That is to say, these statements try to automatically decrypt and re-encrypt all encryption keys below the DMK in the hierarchy. If any of these decryptions fail, the entire ALTER or RESTORE statement will fail. The FORCE option can be used to force an ALTER or RESTORE statement to complete regardless of errors. But be warned: the FORCE option should be used only as a last resort, since it always results in data loss.
All DMK management statements require CONTROL permission on the database, and they must be executed in the context of the current database.
The following statement creates a DMK in the AdventureWorks database:
USE AdventureWorks;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'Avx3$5*!';
You should back up all of your DMKs and store them in safe locations as soon as you create them. You can back up the DMK with a statement like the following:
BACKUP MASTER KEY TO FILE = N'c:\MK\AwMasterKeyBackup.bak'
ENCRYPTION BY PASSWORD = N'#%e3)Fr';
GO
If you ever need to restore the DMK, use the RESTORE MASTER KEY statement, as follows:
RESTORE MASTER KEY FROM FILE = 'c:\MK\AwMasterKeyBackup.bak'
DECRYPTION BY PASSWORD = N'#%e3)Fr'
ENCRYPTION BY PASSWORD = N'Avx3$5*!';
GO
When restoring a DMK, you need to supply the same password in the DECRYPTION BY PASSWORD clause that you used when you performed the BACKUP operation.
SQL Server 2008 provides two methods of securing DMKs. Using the first method requires you to explicitly supply a password when you create, alter, or restore your DMK. This password will be used to encrypt the DMK and store it in the database. If you encrypt your DMK with a password, you must supply the same password every time you need to access the keys the DMK protects. This also means you need to use the OPEN MASTER KEY and CLOSE MASTER KEY statements to explicitly open and close the DMK.
By default, SQL Server also provides a second method of securing your DMKs. When you create a DMK, it is automatically encrypted using the SMK and Triple DES algorithm, with copies stored in both the current database and the master database. This allows SQL Server to automatically open and close your DMK when it is needed, without the need for you to supply a password.
On the plus side, this automatic SMK-based security makes development easier, since you don’t need to use explicit OPEN MASTER KEY and CLOSE MASTER KEY statements to open and close the DMK every time you encrypt your data. You also don’t need to worry about managing, storing, and/or transmitting a password to SQL Server every time you want to perform an encryption or a decryption operation. The downside to this method (and you knew there would be one) is that every sysadmin can decrypt the DMK. In many businesses, this could be the deciding factor against using this feature.
You can use the ALTER MASTER KEY statement to turn off automatic encryption of your DMK by SMK, as in the following T-SQL code:
USE AdventureWorks;
GO
ALTER MASTER KEY
DROP ENCRYPTION BY SERVICE MASTER KEY;
Dropping the DMK is as simple as executing the DROP statement:
DROP MASTER KEY;
The OPEN MASTER KEY and CLOSE MASTER KEY statements are used to open and close the DMK so that it can be used to encrypt and decrypt the other keys and certificates that it protects. These keys can then be used to encrypt and decrypt symmetric keys and data. As we noted, SQL Server can implicitly open and close your DMK if it is not encrypted by the SMK.
Source of Information : Apress Accelerated SQL Server 2008
Unlike the SMK, which is generated automatically the first time it is needed, a DMK must be created explicitly with the CREATE MASTER KEY statement. SQL Server includes the following T-SQL statements to manage DMKs:
• CREATE MASTER KEY: Creates a DMK within a database. A password must be supplied to encrypt the DMK in the database when it is created.
• ALTER MASTER KEY: Allows you to regenerate your DMK or to change how the DMK is secured by adding or removing encryption by password or SMK. If you regenerate the DMK, all the keys it protects will be decrypted and re-encrypted.
• DROP MASTER KEY: Drops the DMK from the current database. If any private keys in the current database are protected by the DMK, the DROP statement will fail.
• BACKUP MASTER KEY: Backs up the DMK to a file. You must specify a password, which will be used to encrypt the DMK in the file.
• RESTORE MASTER KEY: Restores the DMK from a file. You must supply the same password you used when backing up the DMK for a RESTORE operation to succeed. You must also supply a second password to encrypt the DMK in the database after it is restored. During the restore process, SQL Server attempts to decrypt and re-encrypt all keys protected by the DMK.
• OPEN MASTER KEY: Opens the DMK so that it can be used for encryption and decryption. The DMK must be open in order for any encryption or decryption operation to succeed, although SQL Server can implicitly open your DMK when it’s protected by the SMK.
• CLOSE MASTER KEY: Closes a DMK that was explicitly opened using OPEN MASTER KEY after you are finished using it for encryption and decryption.
The ALTER MASTER KEY and RESTORE MASTER KEY statements attempt to regenerate the hierarchy of encryption keys that the DMK protects. That is to say, these statements try to automatically decrypt and re-encrypt all encryption keys below the DMK in the hierarchy. If any of these decryptions fail, the entire ALTER or RESTORE statement will fail. The FORCE option can be used to force an ALTER or RESTORE statement to complete regardless of errors. But be warned: the FORCE option should be used only as a last resort, since it always results in data loss.
All DMK management statements require CONTROL permission on the database, and they must be executed in the context of the current database.
The following statement creates a DMK in the AdventureWorks database:
USE AdventureWorks;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'Avx3$5*!';
You should back up all of your DMKs and store them in safe locations as soon as you create them. You can back up the DMK with a statement like the following:
BACKUP MASTER KEY TO FILE = N'c:\MK\AwMasterKeyBackup.bak'
ENCRYPTION BY PASSWORD = N'#%e3)Fr';
GO
If you ever need to restore the DMK, use the RESTORE MASTER KEY statement, as follows:
RESTORE MASTER KEY FROM FILE = 'c:\MK\AwMasterKeyBackup.bak'
DECRYPTION BY PASSWORD = N'#%e3)Fr'
ENCRYPTION BY PASSWORD = N'Avx3$5*!';
GO
When restoring a DMK, you need to supply the same password in the DECRYPTION BY PASSWORD clause that you used when you performed the BACKUP operation.
SQL Server 2008 provides two methods of securing DMKs. Using the first method requires you to explicitly supply a password when you create, alter, or restore your DMK. This password will be used to encrypt the DMK and store it in the database. If you encrypt your DMK with a password, you must supply the same password every time you need to access the keys the DMK protects. This also means you need to use the OPEN MASTER KEY and CLOSE MASTER KEY statements to explicitly open and close the DMK.
By default, SQL Server also provides a second method of securing your DMKs. When you create a DMK, it is automatically encrypted using the SMK and Triple DES algorithm, with copies stored in both the current database and the master database. This allows SQL Server to automatically open and close your DMK when it is needed, without the need for you to supply a password.
On the plus side, this automatic SMK-based security makes development easier, since you don’t need to use explicit OPEN MASTER KEY and CLOSE MASTER KEY statements to open and close the DMK every time you encrypt your data. You also don’t need to worry about managing, storing, and/or transmitting a password to SQL Server every time you want to perform an encryption or a decryption operation. The downside to this method (and you knew there would be one) is that every sysadmin can decrypt the DMK. In many businesses, this could be the deciding factor against using this feature.
You can use the ALTER MASTER KEY statement to turn off automatic encryption of your DMK by SMK, as in the following T-SQL code:
USE AdventureWorks;
GO
ALTER MASTER KEY
DROP ENCRYPTION BY SERVICE MASTER KEY;
Dropping the DMK is as simple as executing the DROP statement:
DROP MASTER KEY;
The OPEN MASTER KEY and CLOSE MASTER KEY statements are used to open and close the DMK so that it can be used to encrypt and decrypt the other keys and certificates that it protects. These keys can then be used to encrypt and decrypt symmetric keys and data. As we noted, SQL Server can implicitly open and close your DMK if it is not encrypted by the SMK.
Source of Information : Apress Accelerated SQL Server 2008
|
|
0 comments
Post a Comment