CREATE TABLE encrypted_login_details(

ServerName varchar(15)

, username varchar(10)

, password varbinary(100)

)

go

--EncryptByPassPhrase:

INSERT INTO encrypted_login_details(ServerName,username,password) VALUES('MyServer','MyUserName',EncryptByPassPhrase('SSIS Is the future','ThisIsThePassword@1234'))

/*

-Syntax: ENCRYPTBYPASSPHRASE('PASSPHRASE','text')

In this statement, PASSPHRASE specifies the data string to be used to derive an encryption key,

and 'text' data type should be VarBinary.

*/

SELECT * FROM encrypted_login_details

--DECRYPTBYPASSPHRASE:

SELECT ServerName,username, Password FROM encrypted_login_details;

SELECT ServerName,username, DECRYPTBYPASSPHRASE ('SSIS Is the future',password) as Password FROM encrypted_login_details;

SELECT ServerName,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('SSIS Is the future',password)) FROM encrypted_login_details

WHERE username = 'ADMIN';

SELECT ServerName, Username, CONVERT(VARCHAR(10), DECRYPTBYPASSPHRASE('SSIS Is the future',Password)) FROM dbo.Encrypted_Login_Details WHERE USERNAME = 'MyUserName'