Link to home
Start Free TrialLog in
Avatar of ITSysTech
ITSysTech

asked on

Encrypting our current SQL databases on MSSQL 2016

We are currently using Windows 2012 R2 with Sql Server 2016 and would like to begin encrypting our databases in order to protect our data. Previously we didn't have a reason to encrypt the data because it didn't contain sensitive data. What is the best way to go about doing this with our current databases? In addition, what are the possibilities of losing the encryption key etc., and not being able to recover the data?
Cheers
SOLUTION
Avatar of Rich Weissler
Rich Weissler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I have a blog post that shows how to implement TDE (although it goes further with mirroring as well) https://dbaduck.com/2015/06/24/sql-server-tde-on-mirror-and-log-shipping/

That should help you get started.  You MUST protect the certificate you create with backing it up and securing the password to open the backup.  If you lose the Certificate, you will lose the database if anything happens to the database. You cannot drop the certificate while the database is online, so you are protected during running.  But if you want to restore backups, you will need to have the certificate backup to restore it to the server you are restoring to (if it is not the source server).
Avatar of ITSysTech
ITSysTech

ASKER

We are using SQL Server 2016 Standard so the Transparent Data Encryption might not work for us.
That is true. TDE is only in Enterprise Edition.

How do you want to encrypt your database then?  Are you meaning encrypting some of the data?  Always Encrypted? Cell Encryption with Symmetric Keys?
We are looking at "always encrypted" as our method.  I've heard using encrypt the database at rest is insecure in transit.
That is correct. Always Encrypted is an option for doing that.  It takes some pretty complex setup, but nothing that you cannot do.

As long as you are using the new version of ADO.NET or at least the modern one, you should be good.  Be careful of the roles (separated DBA and inclusive DBA) for your encryption isolation.

What more do you need for this question?  It still will be necessary to ensure that you have your keys protected (certificate and keys in the database).
I made a mistake we would use at rest so if someone copied the database they would not be able to see the data. What other options do we have besides TDE with SQL Server 2016 Standard? Thanks
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the help.