AES Encription (symetric key) with SQL Server

go

--keys for encryption
Create Master Key Encryption by Password='xxxx#'
Create Certificate App_Certificate With Subject='Test Certificate'
Create Symmetric Key App_SymmetricKey With Algorithm=AES_256 encryption by Certificate App_Certificate


go

create procedure EncryptStr
       @str varchar(256),
       @strOUT varbinary(256) output    
as

OPEN SYMMETRIC KEY App_SymmetricKey DECRYPTION BY CERTIFICATE App_Certificate

SET @strOUT = EncryptByKey(Key_GUID('SymmetricKey'), @str)

CLOSE SYMMETRIC KEY App_SymmetricKey

go

create procedure DecryptStr
       @str varbinary(256),
       @strOUT varchar(256) output
as

OPEN SYMMETRIC KEY App_SymmetricKey DECRYPTION BY CERTIFICATE App_Certificate

SET @strOUT = DecryptByKey(@str)

CLOSE SYMMETRIC KEY App_SymmetricKey

go


-- example for use

declare @x varbinary(256)
declare @s varchar(256)
declare @z varchar(256)


set @s = 'aaabbbccc'
--encrypt the string
exec EncryptStr @s, @x out
select @x

--decrypt the string
exec DecryptStr @x,@z out

select @z