AES-256 Encryption and Decryption

Question

How to Encrypt and Decrypt a value in MS SQL using AES 256

Amit Mohanty · Answer

Try this CREATE SYMMETRIC KEY SK03 WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = 'abcdefghijklmnopqrstuvwxyzasdefb'; go CREATE TABLE [dbo].[_EncryptionTest]( [OriginalValue] [varchar](max) NULL, [Encryptedvalue] [varbinary](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] go CREATE PROCEDURE [dbo].[_usp_EncryptionTest] @InputString varchar(MAX), @InputString2 varchar(MAX) AS BEGIN SET NOCOUNT ON; OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='abcdefghijklmnopqrstuvwxyzasdefb' INSERT INTO _EncryptionTest (OriginalValue, Encryptedvalue) VALUES ( @InputString ,ENCRYPTBYKEY(Key_GUID('SK03'), CONVERT(VARBINARY(max),@InputString2)) ) CLOSE SYMMETRIC KEY SK03; END go EXEC _usp_EncryptionTest 'Spring is Finally Here! Spring is Finally Here!','Spring is Finally Here! Spring is Finally Here!' go OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='abcdefghijklmnopqrstuvwxyzasdefb' SELECT [OriginalValue] , CONVERT(varchar(MAX), DECRYPTBYKEY([EncryptedValue])) FROM _EncryptionTest CLOSE SYMMETRIC KEY SK03; select * from _EncryptionTest ref: https://dba.stackexchange.com/questions/167920/sql-aes-256-encryption-and-decryption

Puneet Kankar · Answer

Hi Piyush Please check the below links. https://www.geopits.com/blog/sql-encryption.html https://dba.stackexchange.com/questions/167920/sql-aes-256-encryption-and-decryption

AES-256 Encryption and Decryption

Answers (2)