We have a (SQL Agent)job that backs up our production db nightly(and restores to a test db server) and we're adding encryption to some of the columns in our tables. I created a master key and bound it to the Service Master Key:
Create MASTER KEY Encryption By Password = 'MyReallyStrongPW'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY CLOSE MASTER KEY
I also created my certificate:
CREATE CERTIFICATE [My_Cert] With Subject = 'My Certificate'
CREATE SYMMETRIC KEY My_Symm_Key WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE [MY_Cert];
The same account
domain\MyBigSqlAccount
is running both instances of sql (prod & test).
The only way to be able to see the data(on test) is to run this:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyReallyStringPW'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY CLOSE MASTER KEY
and then this would work:
OPEN SYMMETRIC KEY My_Symm_Key DECRYPTION BY CERTIFICATE [My_Cert]
Select Convert(varchar, DecryptByKey(EncryptedColumn)) as DecryptedCol From SomeTable
The only way I've been able to get this to work is to put the password into plain text as a step in the sql job
step 1: restore DB, step 2: use password to bind Master Key to Service Master Key on test server.
Which doesn't seem too wise to store password in plain text(or password to decrypt files which contains the passwords).
Sql Server 2012 for both.
Related (Q1b)Simple Implementation of SQL Server 2008 Encryption
The question is how do I get around storing my passwords in plain text and still use encryption for both prod & text?
Best Answer
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:
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: