When I want to use SQL encryption, what I really want encrypt columns of data. SQL Server doesn't offer an "encryption" attribute for columns, so what we should do is to manually encrypt the data on insert/update and then decrypt the data when selecting. Let's see how it works:-
CREATE TABLE test
(
ID INT not null IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
normalText VARCHAR(16) not null,
EncryptedText VARBINARY(68) not null
);
GO
--passphrase approach
DECLARE @s VARCHAR(16);
SET @s = 'Ashutosh';
INSERT test(normalText, EncryptedText) values (@s, EncryptByPassPhrase('MyPassPhrase', @s));
GO
--verify we can decrypt data
SELECT ID, normalText,
cast(DecryptByPassPhrase('MyPassPhrase', EncryptedText) as varchar(16)) as "Decrypted",
EncryptedText --just to verify the data really is encrypted
FROM test;
GO
The simplest way to encrypt/decrypt data is with the EncryptByPassphrase() and DecryptByPassPhrase() functions.