Data can be encrypted and stored in SQL server using different encryption algorightms and in different ways. Encryption is useful and highly recommended when information like SSN or credit card number is stored in database.
Here is a simple method of encrypting and storing text in a table. This method uses EncryptByPassPhrase and DecryptByPassPhrase routines. EncryptByPassPhrase takes 2 arguments, password and text to be encrypted and returns encrypted text. DecryptByPassPhrase is similar to EncryptByPassPhrase, but just does the opposite. Takes a password and ecrypted text, and returns clear text. The maximum size of the text to be encrypted can go up to 8000 bytes.
–Create Test table
Create Table SecretData(Data varbinary(MAX))
– Procedure to encrypt data.
Create Procedure EncryptData (@Password varchar(10),@Data varchar(20))
as
Insert SecretData
Select EncryptByPassPhrase(@Password, @Data )
– Procedure to decrypt data.
Create Procedure DecryptData (@Password varchar(10))
as
Select Convert(varchar(20),DecryptByPassPhrase(@Password, Data ))
From SecretData
Lets test these procedures now:
–Call EncryptData to insert data.
EncryptData ‘Pa$$’,'This is my data’
–Check data
Select * From SecretData
–Call Decrypt to retrieve data
DecryptData ‘Pa$$’
Please leave your comments.
