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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ITSysTechSenior Systems AdministratorAuthor Commented:
We are using SQL Server 2016 Standard so the Transparent Data Encryption might not work for us.
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ITSysTechSenior Systems AdministratorAuthor Commented:
Thanks for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.