-------------Create the master key for the DB

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = 'your master key here';

--Commands completed successfully.

CREATE CERTIFICATE EMUEPROD

WITH SUBJECT = 'EMUEPROD Users and Passwords';

--Commands completed successfully.

CREATE SYMMETRIC KEY EMUEPROD_KEY01

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE EMUEPROD;

--Commands completed successfully.

----------------------Inserting Data (example) Need to Open and Close Cert------------------------------

OPEN SYMMETRIC KEY EMUEPROD_KEY01

DECRYPTION BY CERTIFICATE EMUEPROD;

--Commands completed successfully.

INSERT INTO [dbo].[SignOnInfo]([SignOn],[Password],[TerminalID],[InUse],[LastUsed],[RandomNumber])VALUES(

EncryptByKey(Key_GUID('EMUEPROD_KEY01'), 'richlemmermann'),EncryptByKey(Key_GUID('EMUEPROD_KEY01'), 'password'),'1','0',GETDATE(),99999)

CLOSE SYMMETRIC KEY EMUEPROD_KEY01;

----------------------To Query the keys. Need to Open and Close Cert------------------------------

OPEN SYMMETRIC KEY EMUEPROD_KEY01

DECRYPTION BY CERTIFICATE EMUEPROD

-- Now list the original ID, the encrypted ID, and the

-- decrypted ciphertext. If the decryption worked, the original

-- and the decrypted ID will match.

SELECT [SignOn] AS 'Encrypted UserName',

cast(DECRYPTBYKEY([SignOn]) as varchar(100) ) AS 'Decrypted UserName' ,

[SignOn] AS 'Encrypted Password',

cast(DECRYPTBYKEY([Password]) as varchar(100) ) AS 'Password'

FROM [EMUEProd].[dbo].[SignOnInfo]

CLOSE SYMMETRIC KEY EMUEPROD_KEY01;

Instructions:

Column size issue:

https://stackoverflow.com/questions/19468302/decryptbykey-returns-null-sql-server-2012/20198577#20198577

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver15

https://stackoverflow.com/questions/19468302/decryptbykey-returns-null-sql-server-2012/20198577#20198577