SQL Encryption question

isames
isames used Ask the Experts™
on
Can anyone explain the difference between encryptbykey, encryption, encryptbypassphrase, and encryptbycert?

I basically want to know when is it ideal to use one method versus the other.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Commented:
Just for the reference:

by Key
https://msdn.microsoft.com/en-us/library/ms174361.aspx

By passphrase
https://msdn.microsoft.com/en-us/library/ms190357.aspx
By cert
https://msdn.microsoft.com/en-us/library/ms188061.aspx

The key is info in the sql server.
The passphrase uses external INPUT.

You can also use cert based encryption.

You can encrypt the whole database. You can encrypt individual columns

Most deals with who controls access to the parameters as well as DR backup of references used for encryption......

I.e when the app uses passphrase, the app has to maintain the information use to encrypt to decrypt.

It might be a consideration for the setup/DR plans/backup plans..

Presumably if you are engaging in encrypting data with either, you should make sure your SQL connection forces encryption since all of these transmit the data in clear text.

You could encrypt data within the APP inserting encrypted data...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial