How to Encrypt a Column in sql server 2000/2005

Tags:
Encryption
SQL Server
How to Encrypt a Column in SQL Server 2000/2005 i.e I need something like this:

@OriginalValue = 'Original Value' @EncryptedValue @Key int set @Key = '123#$2hj$dfgh*67kjugd45j45s4agd' --any unique key value for encrypting and decrypting set @EncryptedValue = fn_Encrypt(@OriginalValue, @Key) insert into table_Master (name_Master) values (@EncryptedValue) for selecting select fn_Decrypt(name_Master, @Key) from table_Master 


How can you do this? Can you please help me?
ASKED: February 20, 2008  6:13 AM
UPDATED: September 9, 2013  4:18 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

In SQL 2005 encryption is built into the database platform. This is the first version of SQL Server which includes encryption as a part of the platform. If you are using SQL Server 2000 you will need to use third party extended stored procedures to encrypt and decrypt the data.

Here is some basic code for encrypting data by using a symmetric key in SQL Server 2005.

OPEN SYMMETRIC KEY PrivateData DECRYPTION BY PASSWORD ='Password'

CREATE TABLE UserData
(Data VARBINARY(255))

DECLARE @Key_GUID UNIQUEIDENTIFIER

SELECT @Key_GUID = Key_GUID
FROM sys.symmetric_keys
WHERE Name = 'PrivateData'
INSERT INTO UserData
(Data)
VALUES
(EncryptByKey(@Key_Guid, 'RawData', 1))

SELECT Data
FROM UserData

SELECT CONVERT(VARCHAR(20), DecryptByKey(UserData.Data, 1)) AS Data
FROM UserData

DROP TABLE UserData

CLOSE SYMMETRIC KEY PrivateData

If you don’t want to use a symmetric key (using a key is recommended over a pass phrase) you can use the EncryptByPassPhrase function to encrypt the data. Here is some sample code from Books OnLine.

USE AdventureWorks;
GO
-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
ADD CardNumber_EncryptedbyPassphrase varbinary(256);
GO
-- 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';
GO

To decrypt the data you would then use something like this (again from Books OnLine).

USE AdventureWorks;
-- 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';
GO

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following