April 25, 2019 at 12:00 am
Comments posted to this topic are about the item Using Backup Encryption with an External Certificate
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 25, 2019 at 2:21 pm
This is a great article, and an excellent way of outlining the process.
Given a cloud-based application, do you know if it's at all possible to encrypt data (whether in a backup or cell-level encryption) where only the users can see the unencrypted data, but those who are hosting the application (and have sa access to the databases and keys) cannot decrypt it?
Thanks again for this article!
April 25, 2019 at 2:34 pm
Yes, at the very least, you can use temporary keys for data encryption. The app creates the temp key, does decrpytion/encryption, keys get dropped when session closes. The paranoid would note the key exists in memory while the user is connected, but practically, this limits the hoster from getting access.
You can also use TDE encryption with self-supplied certs in Azure. I assume in AWS, et al, as well.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 25, 2019 at 2:39 pm
Thanks so much Steve for your quick response! It's actually hosted in a private vendor for now, but will move to an Azure VM. TDE encryption isn't an option currently because of the SQL Server license being used (standard).
I love the idea of the temp keys for encryption in the app though! We'd have to enhance the application to do that, but that's certainly an option. One last question- in that case, we'd need the app to do the decryption before running any reports as well then, right?
Thanks again!
April 25, 2019 at 2:45 pm
With a temp key, the decryption happens inside SQL Server. If you use a .NET namepsace, then you'd be doing the decryption on the client.
If I had to enhance the app, I'd lean towards Always Encrypted in SQL2019+, or having the encrypt/decrypt done on the client in .NET. The problems with either temp keys or .NET is that it limits indexing and searching. You can use partial hash buckets to make this better, which I talked about here (if you have access):
Maintaining High Performance When Using Encryption, PASS Summit 2014
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 25, 2019 at 3:03 pm
Thanks again Steve, that's great information. I don't have access to that link, how can I get it?
Thanks again for being so responsive and helpful!
April 25, 2019 at 4:43 pm
Not sure, have to join PASS and then might be a charge or maybe it's not allowed? Contact them.
I'll try to write an article on the process. Always Encrypted with SQL 2019 and Secure Enclaves is a better choice now if you can do that way. Chris Bell turned me on to the technique, and his Partial Plaintext Value is what he calls it: https://wateroxconsulting.com/archives/optimizing-protected-indexes/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 30, 2019 at 9:13 pm
Hi,
It's a great article. I have a question for you. How do you manage the key if it's expired? The renewal with the same Cert name will produce the new thumbprint and will be unable to restore the database which was backup with previous Cert.
August 20, 2019 at 7:53 pm
You alter the key to use the new server certificate.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply