Question

How to Encrypt a Column in sql server 2000/2005

SQL Server 2000/2005, Encryption

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 to do this
can u plz help me...

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