Example of Decryption and Encryption in SQL Server

Example of Decrypting an encrypted column in SQL Server:

First, in case you don't remember the name of the certificate you used before:

SELECT name, key_length, algorithm_desc, create_date, modify_date
FROM sys.symmetric_keys;

SELECT name, algorithm_desc
FROM sys.asymmetric_keys;

SELECT name, subject, start_date, expiry_date
FROM sys.certificates



OPEN SYMMETRIC KEY MysummetricKey_11
   DECRYPTION BY CERTIFICATE Mycertificate;
GO

SELECT [UsernameEmail], [Password_Encrypted]  
    AS 'Encrypted',
    CONVERT(nvarchar, DecryptByKey([Password_Encrypted]))  
    AS 'Decrypted'
    FROM [RegisteredUserTable];
GO

also see:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data



How to encrypt:

-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY CreditCards_Key11  
   DECRYPTION BY CERTIFICATE Sales09;  

-- Encrypt the value in column CardNumber using the  
-- symmetric key CreditCards_Key11.  
-- Save the result in column CardNumber_Encrypted.    
UPDATE Sales.CreditCard  
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')  
    , CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary  
    , CreditCardID)));  
GO