Friday, November 29, 2013

SQL Server database encryption with CLR user defined types

There aren't many options available when you want to encrypt sensitive data in your database. Enterprise edition of SQL Server has the Transparent Data Encryption feature which is great but this version of SQL Server costs a lot.

The purpose for encryption in a database is to prevent plain data from being written to disc. This is because when database files or backups are accessed by unauthorized people, they would not be able to read the data. The best solution would be a mechanism that automatically performs encryption/decryption on demand and is transparent for the rest of the system.

In this blog post I will show how to encrypt data in a columns by using .NET types that can be imported to SQL Server. Visual Studio 2012 has a project of type SQL Server Database Project. For SQL Server 2008R2, you need to have Target Framework set to 3.5 and Target Platform set to SQL Server 2008 in the project options. Then add new SQL CLR C# User Defined Type like the one below.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


[SqlUserDefinedType(Format.UserDefined,
    IsByteOrdered = true, MaxByteSize = 500)]
[CLSCompliant(false)]
public struct EncryptedString : INullable, IBinarySerialize
{
  private bool is_Null;
  private string _value;

  public bool IsNull
  {
    get
    {
      return (is_Null);
    }
  }

  public static EncryptedString Null
  {
    get
    {
      EncryptedString es = new EncryptedString();
      es.is_Null = true;
      return es;
    }
  }

  public override string ToString()
  {
    if (this.IsNull)
      return "NULL";
    else
    {
      return _value;
    }
  }

  [SqlMethod(OnNullCall = false)]
  public static EncryptedString Parse(SqlString s)
  {
    if (s.IsNull)
      return Null;

    EncryptedString encryptedString = new EncryptedString();
    encryptedString._value = s.Value;

    return encryptedString;
  }

  /*
   * The methods below handle encryption/decryption (here a dummy string reversing)
   * */
  public void Read(System.IO.BinaryReader r)
  {
    var decrypted = Reverse(r.ReadString());
    _value = decrypted;
  }

  public void Write(System.IO.BinaryWriter w)
  {
    var encrypted = Reverse(_value);
    w.Write(encrypted);
  }

  private static string Reverse(string s)
  {
    char[] charArray = s.ToCharArray();
    Array.Reverse(charArray);
    return new string(charArray);
  }
}


I had to use UserDefined serialization format, because the Native (automatic) serialization can handle only some fixed-size primitive types and not strings. This puts the responsibility to handle the binary format on developer, so you need to write code to implement Read and Write methods for the UDT by implementing the IBinarySerialize interface. (more information here)

Having control over how data is written and read gives the opportunity to enrypt it during writing and decrypt when reading. This is the essential point of this approach – no data is being written to disk without prior encryption and everything happens automatically when you insert, select and update the data.

Once the assembly is compiled this is how you load and use it on server.

--REGISTER ASSEMBLY
CREATE ASSEMBLY ClrEncryption
FROM 'C:\ClrTypes\bin\Debug\ClrEncryption.dll' 
WITH PERMISSION_SET = SAFE;

--REGISTER TYPE
CREATE TYPE dbo.EncryptedString 
EXTERNAL NAME ClrEncryption.[EncryptedString];

--CREATE COLUMN OF THAT TYPE
CREATE TABLE dbo.SecretAgents
(ID INT IDENTITY(1,1) PRIMARY KEY, NickName NVARCHAR(50), RealName EncryptedString)

--ENABLE .NET CODE EXECUTION
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

--INSERT VALUES
INSERT INTO dbo.SecretAgents (NickName, RealName) VALUES ('Jason Bourne', 'David Webb')

--SELECTING RAW (SERIALIZED ENCRYTPED) DATA
SELECT NickName, RealName FROM dbo.SecretAgents

--SELECTING DECRYPTED DATA
SELECT NickName, CAST(RealName AS VARCHAR) AS DecryptedRealName FROM dbo.SecretAgents
--OR
SELECT NickName, RealName.ToString() AS DecryptedRealName FROM dbo.SecretAgents


In this example encryption was as simple as reversing the string. In a real world you should use appropriate encryption algorithm like AES, with the keys stored in a secure environment. To debug CLR types you need to attach to SQL Server process.

No comments:

Post a Comment