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);
}