VARBINARY.sql

--Create a certificate that will encrypt the symmetric key CREATE CERTIFICATE PasswordCert ENCRYPTION BY PASSWORD = 'MySecurePa$$word' WITH SUBJECT = 'Cert for securing passwords table' ; --Create a symmetric key that will encrypt the password CREATE SYMMETRIC KEY PasswordKey WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE PasswordCert ; --Create a table to store the password CREATE TABLE dbo.Passwords ( Password VARBINARY(256) ); --Open the symmetric key, so that it can be used OPEN SYMMETRIC KEY PasswordKey DECRYPTION BY CERTIFICATE PasswordCert WITH PASSWORD = 'MySecurePa$$word' ; --Encrypt a password and insert it into the table INSERT INTO dbo.Passwords SELECT ENCRYPTBYKEY(KEY_GUID('PasswordKey'), 'Pa$$w0rd') ; --Decrypt and read the password --The first column in the result set shows the password as the decrypted value but still binary format --The second column in the result set shows the password decrypted and converted back to a character string SELECT DECRYPTBYKEY(Password) AS 'Decrypted Password In Binary' , CAST(DECRYPTBYKEY(Password) AS CHAR(8)) AS 'Decrypted Password As Character String' FROM dbo.Passwords --Close the symmetric key CLOSE SYMMETRIC KEY PasswordKey ;