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
LVL 8
ITSysTechSenior Systems AdministratorAsked:
Who is Participating?
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
You don't have the option of TDE with standard. You can encrypt with Always Encrypted or with cell encryption. Those are the builtin ways.
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Assumption: You want to encrypt the database at rest (I.e. as it is stored on the disk)
Do you have SQL Server 2016 Standard or Enterprise edition?  Transparent Data Encryption is the easiest to implement and administer, and is a robust technology, but it is an Enterprise feature.  And yes, it is always possible to lose the encryption key, especially in the event of a disaster.  Make certain to include safe/secure backups of the encryption key(s) as part of your recovery plan.
1
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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).
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ITSysTechSenior Systems AdministratorAuthor Commented:
We are using SQL Server 2016 Standard so the Transparent Data Encryption might not work for us.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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?
0
 
ITSysTechSenior Systems AdministratorAuthor Commented:
We are looking at "always encrypted" as our method.  I've heard using encrypt the database at rest is insecure in transit.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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).
1
 
ITSysTechSenior Systems AdministratorAuthor Commented:
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
0
 
ITSysTechSenior Systems AdministratorAuthor Commented:
Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.