Decrypting the column level SQL encrypted values using C#

Question

I have been started reading and experimenting the encryption and decryption techniques in C#

I have tried Data Encryption and Decryption in SQL Server 2008 using the information available in the following link

http://www.c-sharpcorner.com/UploadFile/chinnasrihari/data-encryption-and-decryption-in-sql-server-2008/

By using following query (from example of link) I can able to decrypt a value using SQL queries

OPEN SYMMETRIC KEY Sym_password
DECRYPTION BY CERTIFICATE Cert_Password WITH PASSWORD = 'Password!2';
SELECT CAST(DECRYPTBYKEY([Password]) as varchar(200))
FROM Security
CLOSE SYMMETRIC KEY Sym_password;

How can I do the same operation from my C# application?

In my MVC application, I'm using email address as my username to login (password is hashed by default using simple membership). Please suggest me the best ways to encrypt the email field in SQL level and decrypt those values in C#(application level)

When a user enters his username (email) in application, How can I encrypt that value in application side and compare it with the encrypted value in the SQL records.

Answer 1

CREATE TABLE [Users] (
    UserID int identity(1,1) primary key,
    [Login] varchar(32) unique,
    [Email] varchar(32) unique,
    [Password] varbinary(256) not null,
    [BackupCode] varbinary(256) not null,
    ModifiedDate datetime default (getdate()));

DECLARE @EncryptionKey nvarchar(32) = '007London' ;
DECLARE @Password varchar(32) = 'LoveDanger&Romance' ;
DECLARE @Code varchar(32) = 'GoNawazGo' ;

Insert Query(encryption):

INSERT [Users] ([Login], [Email], [Password], [BackupCode])
SELECT 'JamesBond', '[email protected]',
    EncryptByPassPhrase(@EncryptionKey, @Password),
    EncryptByPassPhrase(@EncryptionKey, @Code),

Select Query(decryption):

 SELECT *, 
     DecryptedPassword = Convert(varchar(32),
     DecryptByPassPhrase(@EncryptionKey, [Password])),
     [Password],
     DecryptedCode = Convert(varchar(32),
     DecryptByPassPhrase(@EncryptionKey, [BackupCode])),
     [BackupCode],
     FROM [Users]

Answer 2

Select Query(decryption):

Convert(varchar(32) should be Convert(nvarchar(32)

In nvarchar the code points for standard ASCII letters are the same as for ASCII but padded out with a 0x00 byte.