May 20, 2020 at 9:07 pm
Hi,
How would you encrypt data from multiple tables and save in a temp tables using a script (tsql)? please share general logic in sql 2016 version. Should we use always on encryption? or something else and then also how do you decrypt the data from the same temp table and save those in the permanent table?
Regards
May 21, 2020 at 7:54 pm
If it was me, I would look at encrypting by certificate OR by passphrase.
To see some TSQL on it:
Use those examples but instead of displaying to screen, store it in a temp table. To pull it out, use decryptbypassphrase function and put it in a permanent table.
Sijmilar logic can be used to encrypt by certificate; you just need to create a certificate first.
You could use the always encrypted approach as well if you prefer, but it is a different beast. I have never used always on encryption myself and I am not entirely sure you can apply that to a temporary table. you can read more about Always Encrypted here:
Also, a good read on Always Encrypted:
https://www.wintellect.com/6-things-you-should-know-about-sql-server-2016-always-on-encryption/
Advantage of encryption by certificate method is as long as you can access the certificate, you decrypt the data. Just make sure you have a good backup of that certificate or you may end up losing the encrypted data.
The advantage of encryption by passphrase is you can use that as long as you know the passphrase. No extra things to back up. Disadvantage is passphrases are possible to brute force and building up some dynamic SQL to try passwords for decryption is not THAT hard to do. Longer the passphrase, the harder it gets to brute force, but given enough time, a passphrase is going to be faster for a computer to crack than a certificate.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 26, 2020 at 7:56 pm
Couple of good points there so great help, thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply