Best way to Encrypt a feild in a production db?

Best way to Encrypt a feild in a production db?

Post by Hulica » Sat, 28 Feb 2009 00:51:34


I have a db that is storing SSNs in clear text, I have been trying
some things in dev with mixed results.

Is there a way to encypt this data without having to pass a cert to
insert or select the data in the feilds?

I want to avoid changing all of the associated stored procedures if
it's possible.

Thanks,
 
 
 

Best way to Encrypt a feild in a production db?

Post by TW9oaXQgSy » Sat, 28 Feb 2009 02:14:02

Hmm, I think you'll have to change stored procs where inserts/updates/delets
are taking place.

But question comes down to is how strong encryption you want. If you just
want to make it so not just anyone can read it. You can always use encrypt
by pass phrase but it is limited security...

As stated in ref article:

It is also possible to use symmetric encryption with a pass phrase as the
key by executing the EncryptByPassPhrase function. Note that in this case,
data security depends on the phrase complexity. To reverse the process, you
need to apply the DecryptByPassPhrase function, providing the same pass
phrase (along with encrypted data) as its argument.

DECLARE @cleartext NVARCHAR(100)
DECLARE @encryptedstuff NVARCHAR(100)
DECLARE @decryptedstuff NVARCHAR(100)
SET @cleartext = 'I''m hunting wabbits'
SET @encryptedstuff = EncryptByPassPhrase('l00n3yTun3z', @cleartext)
SELECT @encryptedstuff
SET @decryptedstuff = DecryptByPassphrase('l00n3yTun3z', @encryptedstuff)
SELECT @decryptedstuff


Ref:
http://www.yqcomputer.com/
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://www.yqcomputer.com/

 
 
 

Best way to Encrypt a feild in a production db?

Post by Uri Diman » Mon, 02 Mar 2009 15:49:50