Greetings, we are undertaking a project to begin column level encryption of our data at rest. Our environment is windows and SQL Server 2012, we have transactional replication set up in order to report out of SSRS on the replicated data. We use Active Directory accounts exclusivley to access the data within our applications, and plan to implment functions to encrypt/decrypt the data . In addition, we have a number of what we call lower environments which are used for development, Quality Assurance, and a Staging or prod approval environment which we restore production backups to frequently. 

I have a handle on what needs to happen to encrypt, replicate, decrypt the data by setting up identical symmetric keys on the servers. We are looking at using certificates to encrypt the key rather than passwords and this is where my questions comes in. What are the benefits/downside of using  self signed certs or should I use a cert from a provider given our replicated environment as well as all of our lower environments. We restore production data for testing (part of this effort will be to mask the sensitive data, but not initially, we will use the same encrypt/decrypt functions) frequently and I am wondering what I am going to hit crossing environments with these restores. I see that it can be done by creating the identical symmetric keys but I was wondering if there are other things I should be considering here.

Thanks for any info/comments you have.

Using self-signed certificates should be fine,but you will need to keep track of what are the valid certificates (especially if more than one) and audit any creation/extraction of the certificates/private key.

I would also recommend auditing access to the private key to detect potentially malicious access to the private key, and to detect any unauthorized modification of the certificates and/or the data-encrypting keys.

The only advantage of using a self-signed certificate is ease to script it, but using any other certificate should be equally simple after deployment.

I hope this information helps.

-Raul Garcia

 SQL Server Se

Thanks Raul, as far as auditing access to the key would that be done within SQL then I take it? 

Regarding my questions across environments do you have any comments? I am wondering about restoring DBMK's and possbily the SMK across the servers, if that is a suggested approach.

Thanks again

Yes, SQL auditing should be available on all SKUs, although on non-Enterprise SKUs it may not be as granular as in Enterprise SKUs, you should still be able to track access to these objects. For detailed information, please visit SQL Server Audit topic in BOL (https://msdn.microsoft.com/en-us/library/cc280386.aspx)

I would typically not recommend synchronizing DBMK or SMKs unless it is really necessary as it makes key rotation a more complex (and potentially error-prone) process. Although if managed properly, there should be no problem.

I would strongly recommend against sharing the same DBMK or SMK between systems that handle data with different security classification or sensibility.

I hope thsi helps,

-Raul Garcia

  SQL Server Security