Hi all,
is it possible to obfuscate or scramble a column in SQLServer 2008 R2 without having to use encryption or some highly ineffecient custom made function that does substrings ? :)
greetings, Tim
+3
A:
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.
Oded
2010-08-11 13:44:15
we have a large set of data that needs to be inserted, the encryption is a bottleneck, scrambling in code is probably the closest we will get :(
Tim Mahy
2010-08-11 14:05:00
+1
A:
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
SQLMenace
2010-08-11 13:45:42
And if it's a password, don't encrypt it at all, just store the salted hash.
Steven Sudit
2010-08-11 13:53:54
A:
try something like this: http://stackoverflow.com/questions/1043621/obfuscate-or-encrypt-some-plain-text-data-in-php
KM
2010-08-11 13:53:23
A:
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 KEYopen 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.
p.campbell
2010-08-11 14:08:52