How to use Azure Sql Always Encrypted with Dapper ORM?

Question

Is there any possibility to use DapperORM with (Azure) SQL 2016 Always Encrypted? Is there any documentation how to use and best practises?

especially how to deal with the new parameter property input direction and input length?

Answer 1

The following links will help you get started on Always Encrypted.

• Getting started with Always Encrypted
• Parameterization For Always Encrypted

I have not used Dapper ORM, but, as long as you have the ability to enable Always Encrypted using the connection string and to parameterize your literals you should be fine.

Here is a quick sample that I tried. Note Column Encryption Setting=Enabled in the connection string which enables Always Encrypted for the given connection.

private static readonly string connectionString = @"Data Source=.;Initial Catalog=dbName;Integrated Security=true;Connection Timeout=60;Column Encryption Setting=Enabled";

static void Main(string[] args)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();

        int id = 1;
        var dog = conn.Query<Dog>("select age, name, weight, idcol from  DogTable where idcol = @Id", new { Id = id });
    }
}

public class Dog
{
    public int? age { get; set; }
    public int idcol { get; set; }
    public string name { get; set; }
    public float? weight { get; set; }

    public int IgnoredProperty { get { return 1; } }
}

Answer 2

To add to Nikhil's answer, it is possible and the resources he cites are helpful, however the parameterization article might be a little misleading, I'll include a snippet that I just got working in case it helps. You do not have to use a DECLARE @SSN... in your sql like you would in SSMS, the blow works just fine for me once I fixed my type issues (see paragraph after the code block).

string sql = "SELECT * from dob.ExampleTable WHERE IsActive = 1 SSN = @SSN";

var results = await this.DbConnection.QueryAsync(sql, new
{
    SSN = 999999999
});

Also, keep an eye out for mismatched data types. I was not able to make char or varchar work as the target type for my SSN column using Dapper (I was able to use straight ADO.NET configuring the column explicitly as CHAR or VARCHAR but Dapper seems to assume NVARCHAR(4000) for all string fields, which resulted in an error using Dapper). NVARCHAR and NCHAR worked fine. I think it has something to do with the fact that when you encrypt the column, it changes the "real" datatype to NCHAR(50) and something about the conversion of dapper from nvarchar(4000)=>CHAR(9)=>NCHAR(50) wasn't working, but only in Dapper.