The proper key hierarchy is the Service Master Key protects the Database Master Key, the Database Master Key protects either an asymmetric key or certificate, the asymmetric key or certificate can be used for encryption or can be used to protect a symmetric key which is used for encryption. The keys are created in that order. The system creates and protects the SMK, and the DMK and subsequent keys are created by the user.
The design of the key hierarchy in SQL Server protects both the data and keys from compromise. Remember that with a symmetric key, the same key is used to encrypt and decrypt data, or in this case other keys. The main purpose of introducing an asymmetric key or certificate into the hierarchy protected by the Database Master Key is to prevent an attack on the DMK and SMK from inside of the database. If someone malicious wanted to obtain the DMK unencrypted, then that person would need to do one of two things. Either decrypt the service master key and use it to decrypt the DMK, which is not possible because only the database engine can use the Data Protection API to do this, or attempt to decrypt from the top of the hierarchy down, which would be possible if there were not an asymmetric key or certificate in the way.
All of the signed or encrypted bits of the symmetric keys in SQL Server are in a system table named sys.crypt_properties in each database, including the encryption of the Service Master Key in the master database. There is no system table that contains the private key for either of the asymmetric key types. If all keys in a hierarchy were symmetric keys, then the SMK would encrypt the DMK and the DMK would encrypt the symmetric key that would encrypt the data. Because of the way that symmetric keys work, that would also mean that, if someone opens the symmetric key for the data, then it can theoretically decrypt the DMK and the decrypted DMK can be saved by a malicious user or used to decrypt the SMK because the same key is used for encryption and decryption. This is why an asymmetric key or certificate is required to be an integrated part of the encryption hierarchy.
Without a doubt, a new design is needed for step 2. I would suggest that, instead of passing the Database Master Key password, which typically remains fairly constant and is not changed frequently, step 2 would back up the Database Master Key using a randomly generated password of sufficient length. The password can then be encrypted at the source, passed to the destination server encrypted, then decrypted and used to restore the Database Master Key. For this design, you will need to add a procedure, a certificate and a view to the source server msdb database and a procedure and a certificate to the desitination server msdb database. You will also need to create a share folder on the destination server with write permissions granted to the prod server's SQL Agent service account. The objects involved are:
- ProdServer.msdb.vwGetRandomPass - view that generates random long
passwords
- ProdServer.msdb.DMKEncryptionCertificate - certificate to encrypt DMK
password
- ProdServer.msdb.usp_BackupDMK - retreieves a random password from the
view vwGetRandomPass, uses it to back up the DMK to the share,
encrypt the password using the certificate DMKEncryptionCertificate,
return the encrypted password as an output varbinary variable
- TargetServer.msdb.DMKEncryptionCertificate - certificate to decrypt
DMK password restored from a backup of the cert and private key on
the prod server
- TargetServer.msdb.usp_restoreDMK - accepts varbinary parameter
password, decrypts password using DMKEncryptionCertificate, restore
DMK from share using decrypted password, upon successful restore
delete the DMK file
Step 2 would consist of calling the prod server procedure, followed by a call to the TargetServer procedure to complete the restore. You can use a linked server, osql call or other method to call the procedure on the target server.
For even further security, you can drop the private key on the Production Server after backing it up. That way only the destination server can decrypt the password. The password will also be different and unpredictable every day. The other benefit is that the DMK would be deleted every time and would only exist on the share for the duration of step 2, which should be a matter of seconds.
This can be done successfully, however, I would also ask if it should be done. If this data is so sensitive that it needs to be encrypted, then should it be available outside of your production system? If you decide against it, then you could just drop the certificate and symmetric key in the target database and create new ones with the same name to avoid exceptions. Any call using these would return null. I've included the view below:
/*
generates a random 128 character string from all
valid password characters except single quote
*/
CREATE VIEW dbo.vwGetRandomPass
AS
WITH s1
AS (
SELECT TOP 32 CHAR(number) AS chr
FROM master..spt_values
WHERE number BETWEEN 48
AND 57 -- number characters
ORDER BY newid()
)
,s2
AS (
SELECT TOP 32 CHAR(number) AS chr
FROM master..spt_values
WHERE number BETWEEN 65
AND 90 -- Upper letters
ORDER BY newid()
)
,s3
AS (
SELECT TOP 32 CHAR(number) AS chr
FROM master..spt_values
WHERE number BETWEEN 97
AND 122 -- Lower letters
ORDER BY newid()
)
,s4
AS (
SELECT TOP 32 CHAR(number) AS chr
FROM master..spt_values
WHERE number BETWEEN 33
AND 38
OR number BETWEEN 40
AND 47 -- sign characters
OR number BETWEEN 58
AND 64
OR number BETWEEN 91
AND 96
OR number BETWEEN 123
AND 126
ORDER BY newid()
)
,final
AS (
SELECT chr
FROM s1
UNION ALL
SELECT chr
FROM s2
UNION ALL
SELECT chr
FROM s3
UNION ALL
SELECT chr
FROM s4
)
SELECT pass = (
SELECT chr AS [text()]
FROM final
ORDER BY newid()
FOR XML path('')
)
GO
Best Answer
You can do both at the same time, which is preferable.
The big two are: