Tag: cell level

SQL Server Cell/Column/Field level encryption handled from your application(s)

SQL Server supports a number of encryption mechanisms, including Always Encrypted. This is automatic encryption handled by the database engine, which is pretty cool. However, what if you want your applications to handle the encryption and decryption when needed? This is where cell level encryption comes in, and it is supported by SQL Server which is a good thing.

I’ve used the AdventureWorks2017 example database for the encryption shown later, which you can find here, or using a direct download link. I have enabled SQL Server and Windows Authentication on the server, and I’m using SQL Server Authentication for this post. I have a login named enc, as well as a user named enc, using the dbo schema.

SQL Server has a number of Transact-SQL commands, which you can read more about here. Now, why am I writing about this, seeing as you can just use what is shown on those pages? Using Transact-SQL is very simple, but how can you use it from your own applications? Well, I needed to use the encryption from a C# application using Entity Framework 5.0. The latter was a requirement!

Right, let’s start by enabling the database to handle the encryption/decryption. In the Sales.CreditCard table, there is a CardNumber column, which I want encrypted, to keep prying eyes from reading it. So in a query window (I’m using SSMS, SQL Server Management Studio), run the following query:

-- Connect to the right database
USE AdventureWorks2017
GO

-- Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeSillyPassword@2018'
GO

-- Create certificate
CREATE CERTIFICATE CreditCardNoCert WITH SUBJECT = 'Credit card Numbers'
GO

-- Create symmetric key
CREATE SYMMETRIC KEY CreditCardNoKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE CreditCardNoCert
GO

-- Grant permissions
GRANT VIEW DEFINITION ON CERTIFICATE::CreditCardNoCert TO "enc"
GO

GRANT VIEW DEFINITION ON SYMMETRIC KEY::CreditCardNoKey TO "enc"
GO

GRANT CONTROL ON CERTIFICATE::CreditCardNoCert TO "enc"
GO

-- Add new column to hold encrypted value
ALTER TABLE Sales.CreditCard
ADD CardNumberEncrypted varbinary(8000)
GO

You can encrypt the CardNumber column for all existing rows, using the below T-SQL and the symmetric key. Notice how the encrypted value is copied from the original column, CardNumber, to the newly created column, CardNumberEncrypted. Notice how the symmetric key is being opened and closed.

OPEN SYMMETRIC KEY CreditCardNoKey
DECRYPTION BY CERTIFICATE CreditCardNoCert

UPDATE Sales.CreditCard
SET CardNumberEncrypted = EncryptByKey(Key_GUID('CreditCardNoKey'), CardNumber, 1, HashBytes('SHA1', CONVERT(varbinary, CreditCardID)));
GO

CLOSE SYMMETRIC KEY CreditCardNoKey
GO

As a means of protection, you could set the CardNumber column to an empty string, or you can delete it, and rename the CardNumberEncrypted column to CardNumber.

Now that we have the encryption in place, we need to look at how you can decrypt CardNumberEncrypted.

OPEN SYMMETRIC KEY CreditCardNoKey
DECRYPTION BY CERTIFICATE CreditCardNoCert

SELECT CardNumberEncrypted AS 'CardNumberEncrypted', CONVERT(nvarchar, DecryptByKey(CardNumberEncrypted, 1, HASHBYTES('SHA1', CONVERT(varbinary, CreditCardID)))) AS 'CardNumberDecrypted' FROM Sales.CreditCard;

CLOSE SYMMETRIC KEY CreditCardNoKey
GO

So far so good, now we need to handle the encryption/decryption from an application using C#. I have created an Operations class, shown below.

using System;
using System.Linq;
using System.Text;

namespace EF-SqlEncryption {
  public class Operations {
    private AdventureWorks2017Entities _adventureWorksContext = null;

    public void EnsureEncryptedCreditCardNoColExists() {
      using (_adventureWorksContext = new AdventureWorks2017Entities()){
        try {
          _adventureWorksContext.Database.ExecuteSqlCommand("ALTER TABLE Sales.CreditCard ADD CardNumberEncrypted varbinary(128)");
          Console.WriteLine("Column CardNumberEncrypted created");
        }
        catch (Exception){
          Console.WriteLine("Column CardNumberEncrypted already exists");
        }
      }
    }

    public string GetFirstCreditCardNo() {
      string result = string.Empty;

      using (_adventureWorksContext = new AdventureWorks2017Entities()) {
        var ccs = _adventureWorksContext.CreditCards.FirstOrDefault();
        result = ccs.CardNumber + " - ";
        if (ccs.CardNumberEncrypted != null)
          result += ByteArrayStringRepresentation(ccs.CardNumberEncrypted);
      }

      return result;
    }

    public byte[] EncryptCreditCardNo(string cardNo) {
      var result = new byte[0];

      try {
        _adventureWorksContext = new AdventureWorks2017Entities();
        var dmlOpenCert = "OPEN SYMMETRIC KEY CreditCardNoKey DECRYPTION BY CERTIFICATE CreditCardNoCert;";
        var dmlCloseCert = "CLOSE SYMMETRIC KEY CreditCardNoKey;";
        var dmlEncrypt = "SELECT CardNumberEncrypted = EncryptByKey(Key_GUID('CreditCardNoKey'), CardNumber, 1, HashBytes('SHA1', CONVERT(varbinary, CreditCardID))) FROM Sales.CreditCard WHERE CardNumber = '" + cardNo + "';";
        result = _adventureWorksContext.Database.SqlQuery<byte[]>(dmlOpenCert + dmlEncrypt + dmlCloseCert).FirstOrDefault();
      }
      catch (Exception e) {
        Console.WriteLine(e.Message);
      }
      finally {
        _adventureWorksContext.Dispose();
      }

      return result;
    }

    public byte[] UpdateEncryptedCreditCardNo(string cardNo) {
      byte[] 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;
    }

    public string DecryptFirstCreditCardNo() {
      var result = string.Empty;

      try {
        EnsureEncryptedCreditCardNoColExists();

        _adventureWorksContext = new AdventureWorks2017Entities();
        var res = _adventureWorksContext.Database.SqlQuery("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: This works, with the combination of DML and a query. EF is closing the connection after each query or DML, meaning the Cert would be closed too, effectively preventing encryption and decryption.
        result = _adventureWorksContext.Database.SqlQuery(dmlOpenCert + dmlDecrypt + dmlCloseCert).FirstOrDefault();
      }
      catch (Exception e) {
        Console.WriteLine(e.Message);
      }
      finally {
        _adventureWorksContext.Dispose();
      }

      return result;
    }

    public string DecryptCreditCardNo(string cardNo) {
      var result = string.Empty;

      try {
        EnsureEncryptedCreditCardNoColExists();
        _adventureWorksContext = new AdventureWorks2017Entities();
        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;";
        result = _adventureWorksContext.Database.SqlQuery(dmlOpenCert + dmlDecrypt + dmlCloseCert).FirstOrDefault();
      }
      catch (Exception e) {
        Console.WriteLine(e.Message);
      }
      finally {
        _adventureWorksContext.Dispose();
      }

      return result;
    }

    public static string ByteArrayStringRepresentation(byte[] ba) {
      var bytesStringRepresentation = new StringBuilder(ba.Length * 2);
      bytesStringRepresentation.Append("0x");

      foreach (byte b in ba) {
        bytesStringRepresentation.AppendFormat("{0:x2}", b);
      }

      return bytesStringRepresentation.ToString().ToUpper();
    }
  }
}

Here are the AdventureWorks2017 model and code first classes.

Advertisements