Encryption 2005- User defined function for encryption and decryption.

Question

Hi,

I have created two user defined functions for encryption and decryption using passphrase mechanism. When I call encryption function, each time I am getting the different values for the same input. While I searching a particular value, it takes long time to retrieve due to calling decryption function for each row.

Could you please let me know the best way to encrypt and decrypt using user defined functions.

Below is the query which is taking long time.

SELECT ID FROM table WITH (NOLOCK)
                     WHERE dbo.DecodeFunction(column) = 'value'

When I try to use symetric or asymetric encryption, I am not able to put "OPEN SYMETRIC KEY" code in a function. So, I am using PassPhrase mechanism.

Could you please suggest me.

Thank you,

Raja.

---

Thanks, Raja

Answer 1

When I call encryption function, each time I am getting the different values for the same input.

That's a feature. It encryption was deterministic, an attacker could encrypt your name, to see if your is in the data.

Could you please let me know the best way to encrypt and decrypt using user defined functions.

The best way is not to do it. Not only does decryption take a lot of time. It's also impossible for SQL Server to use an index, so it has decrypt all rows in the table to see if there is a match on the value.

If you need to search on an encrypted value, the best is also store a deterministic hash on the value, or part of the value, and then index the column with the hash.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

To complement Erland’s answer, I am including a few links that may be of interest for you.

The first one is describing in more detail a sample that uses the technique that Erland mentioned, using a keyed-hash for indexing. NOTE: The algorithms used in this old article are obsolete, but the general technique is still quite useful: http://blogs.msdn.com/b/raulga/archive/2006/03/11/549754.aspx

The second link is for a sample code in C# I wrote, but hopefully the information under project description will be useful. http://sqlcolumnencryption.codeplex.com/

Finally, I would like to include a few links related to a new feature we are introducing in SQL Server 2016, and it is available on the public CTP: Always Encrypted. We will appreciate any feedback on this feature:

• https://msdn.microsoft.com/en-us/library/mt163865%28v=sql.130%29.aspx
• http://blogs.msdn.com/b/sqlsecurity/archive/tags/always+encrypted/

I hope this information also helps

-Raul Garcia

 SQL Security

---

This posting is provided "AS IS" with no warranties, and confers no rights.