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)
SET @decryptedstuff = DecryptByPassphrase('l00n3yTun3z', @encryptedstuff)
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005