SQLServer2008 : Obfuscation or scrambling

Is it possible to obfuscate or scramble a column in SQLServer 2008 R2 without hav开发者_StackOverflow中文版ing to use encryption or some highly ineffecient custom made function that does substrings?

Why not use encryption/hashing/encoding in your application and post the results into SQL? That's pretty standard.

If you must do it in SQL Server, you may want to look at using CLR functions within SQL Server.

Not that I know of, encrypt the data coming in and out from the column with a 3rd party app like Java or .NET. If you do it within SQL then the person who steals your backup can decrypt is since he has access to the function

try something like this: obfuscate or encrypt some plain text data in PHP

Something to research a bit further here.

SQL Server 2005+ has the function - ENCRYPTBYKEY and its mate DECRYPTBYKEY

EncryptByKey Params:

• key_GUID - the GUID of the key to be used to encrypt the cleartext. uniqueidentifier. This requires you to have a SYMMETRIC KEY open on the server.
• cleartex - some text to be encrypted

Returns a varbinary up to 8000.

If you wanted to leverage these methods, you could create a column of type varbinary up to 8000, and save the output here.

I'd be interested to hear comments on real world usages of this function, and any anecdotes on its performance.