-------------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';
GO
--Commands completed successfully.
CREATE SYMMETRIC KEY EMUEPROD_KEY01
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE EMUEPROD;
GO
--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
GO
-- 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