how to encrypt data

I have a database table with several columns of data that I would like to encrypt.  How do you encrypt data in a table to hide it in a database?  What is the process to accomplish this?

Thanks for any help.
-Dman100-Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
chaauConnect With a Mentor Commented:
You can use ENCRYPTBYPASSPHRASE function to encrypt data in the selected columns:
-- First get the passphrase from the user.
DECLARE @PassphraseEnteredByUser nvarchar(128);
SET @PassphraseEnteredByUser 
    = 'A little learning is a dangerous thing!';

-- Update the record for the user's credit card.
-- In this case, the record is number 3681.
UPDATE Sales.CreditCard
SET CardNumber_EncryptedbyPassphrase = EncryptByPassPhrase(@PassphraseEnteredByUser
    , CardNumber, 1, CONVERT( varbinary, CreditCardID))
WHERE CreditCardID = '3681';

Open in new window

You need then to use DECRYPTBYPASSPHRASE to decrypt the data back:
-- Get the pass phrase from the user.
DECLARE @PassphraseEnteredByUser nvarchar(128);
SET @PassphraseEnteredByUser 
= 'A little learning is a dangerous thing!';

-- Decrypt the encrypted record.
SELECT CardNumber, CardNumber_EncryptedbyPassphrase 
    AS 'Encrypted card number', CONVERT(nvarchar,
    DecryptByPassphrase(@PassphraseEnteredByUser, CardNumber_EncryptedbyPassphrase, 1 
    , CONVERT(varbinary, CreditCardID)))
    AS 'Decrypted card number' FROM Sales.CreditCard 
    WHERE CreditCardID = '3681';

Open in new window

0
 
-Dman100-Author Commented:
Thank you!
0
All Courses

From novice to tech pro — start learning today.