Column Encryption Best Practise

Question

Post reply

Column Encryption Best Practise

planetmatt

SSCrazy

Points: 2207
More actions
September 2, 2019 at 12:30 pm

#3676315

I need to encrypt a column of data.
All tutorials walk you through the standard create DB master key, create certificate, create symmetric key etc.
My questions is this. Once I do that, everyone with access to the server can simply run DECRYPTBYKEY, providing they know the Certificate name which is clearly visible in SSMS.
Would I be better creating a Symmetric Key created with a password or phrase instead of a certificate. Then write a UDF function to wrap up the DECRYPTBYKEY call with the password hardcoded in the UDF but use WITH ENCRYPTION when creating the UDF so the password is no longer visible?
Key would then be stored in something like Keepass outside the server.
What do other people do? What's best practise? All the information on Column encryption deals with getting it working and not the correct security design.
Or, can you limit access to keys and certs to uses and roles?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Sue_H SSC Guru Points: 90860 More actions · Answer 2

Anyone with server access isn't able to use DecryptByKey. They need to open the symmetric key to execute DecryptByKey and additional permissions are required to open the symmetric key as listed in the following documentation:

OPEN SYMMETRIC KEY

Using views to expose the encrypted information can be a good approach. This article outlines some different scenarios for this method:

Using Views to Expose Encrypted Data in SQL Server

Sue

Steve Jones - SSC Editor SSC Guru Points: 736428 More actions · Answer 3

Password management is still an issue. You can use one with a certificate as well, and use a similar schema.

You can also have code create a temp symm key for decryption. This is deterministic if the parameter values are the same. Use a wrapper function/proc and the user pass in the correct value for one of the parameters as a way of ensuring only those that know the correct value get to decrypt.