public string DecryptFirstCreditCardNo() { var result = string.Empty; try { EnsureEncryptedCreditCardNoColExists(); _adventureWorksContext = new AdventureWorks2017Entities(); System.Collections.Generic.IEnumerable <CreditCard> res = _adventureWorksContext.Database.SqlQuery <CreditCard>("SELECT TOP 1 * FROM Sales.CreditCard WHERE CardNumberEncrypted IS NOT NULL"); var cardNo = res.FirstOrDefault().CardNumber; var dmlOpenCert = "OPEN SYMMETRIC KEY CreditCardNoKey DECRYPTION BY CERTIFICATE CreditCardNoCert;"; var dmlDecrypt = "SELECT CONVERT(nvarchar, DecryptByKey(CardNumberEncrypted, 1, HashBytes('SHA1', " + "CONVERT(varbinary, CreditCardID)))) AS 'CardNumberDec' FROM Sales.CreditCard WHERE CardNumber = '" + cardNo + "';"; var dmlCloseCert = "CLOSE SYMMETRIC KEY CreditCardNoKey;"; // Carsten Thomsen 07/06/2018: Surprisingly this works, with the combination of DML and a query. I got lucky as I was running out of luck // with EF closing the connection after each query or DML, meaning the Cert would be closed too, effectively // preventing encryption and decryption. result = _adventureWorksContext.Database.SqlQuery <string>(dmlOpenCert + dmlDecrypt + dmlCloseCert).FirstOrDefault(); } catch (Exception e) { Console.WriteLine(e.Message); } finally { _adventureWorksContext.Dispose(); } return(result); }
public byte[] UpdateEncryptedCreditCardNo(string cardNo) { var result = new byte[0]; try { EnsureEncryptedCreditCardNoColExists(); _adventureWorksContext = new AdventureWorks2017Entities(); var dmlOpenCert = "OPEN SYMMETRIC KEY CreditCardNoKey DECRYPTION BY CERTIFICATE CreditCardNoCert;"; var dmlCloseCert = "CLOSE SYMMETRIC KEY CreditCardNoKey;"; var dmlUpdate = "UPDATE Sales.CreditCard SET CardNumberEncrypted = EncryptByKey(Key_GUID('CreditCardNoKey'), CardNumber, 1, HashBytes('SHA1', CONVERT(varbinary, CreditCardID))) WHERE CardNumber = '" + cardNo + "';"; var sqlSelect = "SELECT CardNumberEncrypted FROM Sales.CreditCard WHERE CardNumber = '" + cardNo + "';"; result = _adventureWorksContext.Database.SqlQuery <byte[]>(dmlOpenCert + dmlUpdate + sqlSelect + dmlCloseCert).FirstOrDefault(); } catch (Exception e) { Console.WriteLine(e.Message); } finally { _adventureWorksContext.Dispose(); } return(result); }