Using Always Encrypted with NHibernate

SQL Server 2016 has introduced a new security feature called Always Encrypted. The feature ensures that no sensitive data is stored in plaintext on the SQL server. The ADO.NET data provider makes the encryption process transparent to applications by automatically encrypting and decrypting sensitive data on the client computer. The data provider encrypts the data before sending it to the SQL server. In a similar way, the data provider decrypts the data before serving it to the client application.

Always Encrypted enables encryption of confidential data for storage in the database and prevents database administrators or other third parties from accessing the data. Thanks to the built-in Always Encrypted feature, a usage scenario of an on-premises application accessing data in the cloud becomes more appealing. The burden of purchasing and maintaining hardware is moved to cloud providers without losing the control over the data privacy.

In this blog post, I would like to show the Always Encrypted feature in action. I will create a NHibernate based application that stores sensitive data on the SQL Server.

Setting up the database

Before we start creating the sample application, we need to create encryption keys and a table to store the confidential data.

1. Create a master key

You can use makecert.exe (certificate creation tool) to create a master key.

makecert -r -pe -n "CN=AlwaysEncryptedMasterKey" -b 01/01/2015
-e 01/01/2016 -sky exchange -ss my

This would create a self-signed certificate with an exportable private key and place the certificate into the personal certificate store of the current user.

The master key is only available to the client application. The ADO.NET data provider uses a master key to encrypt column encryption keys.

2. Create a master key definition and column encryption key

Next, you need to tell the SQL server where the master key is located. This is done by creating a column master key definition.

In the SQL Server Management Studio, select your database and go to Security -> Always Encrypted Keys -> Column Master Key Definitions -> New Column Master Key Definition. Choose the previously created certificate and click OK.

It is worth mentioning that the SQL server neither accesses nor stores the master key. The created master key definition just holds a path to the master key. The SQL server sends the specified master key path to the client application when accessing an Always Encrypted table. In this way, the client knows where the master key should be extracted from.

Now, you should create a column encryption key. Go to Security -> Always Encrypted Keys -> Column Encryption Keys -> New Column Encryption Key. Select the created column master key definition and click OK.

It is important that the SQL Server Management Studio has access to the private key associated with the certificate created in the first step. The Management Studio uses the private key to sign the encrypted column encryption key. If no private key can be accessed, you would get the following error message.

The certificate associated with the specified column master key definition is invalid for encrypting a column encryption key, or you do not have permission to access it. Certificate specified in the key path 'xx' does not have a private key to encrypt a column encryption key.

3. Create a table with Always Encrypted columns and stored procedure

The sample application uses a simple table that stores sensitive customer data. The table holds encrypted names and personal ids of customers. Use the following SQL code to create the table.

CREATE TABLE Customers (
  Id INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(60) 
       ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyEncryptionKey,
                       ENCRYPTION_TYPE = RANDOMIZED,
                       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'), 
  PersonalId VARCHAR(8) 
             COLLATE Latin1_General_BIN2 
             ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyEncryptionKey,
                             ENCRYPTION_TYPE = DETERMINISTIC,
                             ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
);

Please note that two different encryption types are applied to the table columns. Column PersonalId utilizes the deterministic encryption method which always produces the same encrypted value for any given plain text and thus allows filtering by equality on encrypted values. Column Name uses the randomized encryption method which is less predictable in its nature and does not allow equality filtering.

Next, you would need to create a stored procedure that is responsible for creating new customers.

CREATE PROCEDURE sp_add_customer @Name NVARCHAR(60), @PersonalId VARCHAR(8) 
AS 
INSERT INTO Customers (Name, PersonalId)
VALUES(@Name, @PersonalId);

Creating the sample application

After the database has been set up, you can start creating a sample application that creates and accesses customer data.

First, you should create a customer entity class and provide mapping to the corresponding database table.

public class Customer
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string PersonalId { get; set; }
}

public class CustomerMap : ClassMapping<Customer>
{
    public CustomerMap()
    {
        Table("Customers");
        Id(x => x.Id);
        Property(m => m.Name, m => m.Type(NHibernateUtil.String));
        Property(m => m.PersonalId, m => m.Type(NHibernateUtil.AnsiString));
    }
}

Next, extend the application with functionality for creating new customers. To do so, you would need to call the stored procedure that we have created before.

using (var session = SessionFactory.OpenSession())
{
    session.CreateSQLQuery("exec sp_add_customer :name, :personalId")
           .SetParameter("name", txtName.Text, TypeFactory.GetStringType(60))
           .SetParameter("personalId", txtPersonalId.Text, TypeFactory.GetAnsiStringType(8))
           .ExecuteUpdate();
}

Please note that the name and personal id of a new customer are provided in the plain text form. No encryption is performed in the code. The ADO.NET data provider is responsible for encrypting the customer related data. Let us check this.

Make sure that your connection string contains the Column Encryption Setting option and start the application.

"Data Source=MyDbServer; Initial Catalog=MyDatabase;
Integrated Security=true; Column Encryption Setting=enabled"

If you add a new customer and inspect the generated SQL statement with SQL Server Profiler, you would see the name and personal id have been encrypted before reaching the SQL server. The ADO.NET framework has encrypted the customer related data before sending it out to the database.

The data of the newly created customer is stored encrypted in the database.

For accessing customer names and personal ids, you would need to build a usual query with QueryOver. No decryption is required in the application itself. Similar to the previous case, the ADO.NET data provider decrypts the data before passing it over to the application.

using (var session = SessionFactory.OpenSession())
{
    using (session.BeginTransaction())
    {
        var customers = session.QueryOver<Customer>()
                               .List<Customer>();
 
        gridCustomers.DataSource = customers;
    }
}

If you need to filter customers by a personal id, just use an additional where condition. As already mentioned, only columns of the deterministic encryption are supported by the equality filtering.

var customers = session.QueryOver<Customer>()
                       .Where(c => c.PersonalId == txtLoadByPersonalId.Text)
                       .List<Customer>();

You can find the full source code of the application in GitHub. Have fun with trying out the Always Encrypted feature!

Install app for SharePoint: There were errors when validating the App Package

While deploying a SharePoint application out of my Visual Studio, I have got the following error message.

Error occurred in deployment step 'Install app for SharePoint': There were errors when validating the App Package.

This message does not really tell you a lot :) It took me a while to figure out what caused the problem. The reason was simple. No start page was specified in my application manifest file. Once I have set an application start page, the error has gone away!

By the way, Visual Studio 2012 supports a verbose logging mode for SharePoint related activities. If the mode is activated, you receive more detailed error messages and it can enable you to solve an issue quicker.

To turn on verbose logging, follow these steps:

1. Run the registry editor (regedit.exe) and navigate to the key HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\11.0\SharePointTools
2. Create a DWORD based value and name it EnableDiagnostics
3. Set the EnableDiagnostics's value to 1

Passing an array as a route value within Html.ActionLink

MVC framework has a limitation when it comes to passing an array object as a route value to such Html helpers as Html.ActionLink, Url.Action and Html.BeginForm. Let's assume you want to render a link to a certain action method and pass in a list of strings. You would probably try to achieve that with the following line of code.

@Html.ActionLink("Click on me", "Index", new { names = new string[] { "foo", "boo", "moo"} })

The problem with this is that the provided array values are not included into the generated URL.

.../Index?names=System.String%5B%5D

You would rather expect the URL to look like this.

.../Index?names=foo&names=boo&names=moo

The expected behavior can be achieved by extending ASP.NET routing. In my previous post, I showed how to append certain parameters to all links generated by the MVC. The described approach can be also used to extend a URL with array based data which comes as a part of route values. To do that, create a custom route and override the GetVirtualPath function as follows.

public class ArrayAwareRoute : Route
{
  public ArrayAwareRoute(string url, IRouteHandler routeHandler)
    : base(url, routeHandler)
  {
  }

  public override VirtualPathData GetVirtualPath(RequestContext requestContext, RouteValueDictionary routeValues)
  {
    NameValueCollection enumerables = new NameValueCollection();

    foreach (KeyValuePair<string, object> routeValue in routeValues)
    {
      IEnumerable<string> values = routeValue.Value as IEnumerable<string>;

      // collects all enumerable route values
      if (values != null)
      {
        foreach (string value in values)
        {
          enumerables.Add(routeValue.Key, value);
        }
      }
    }

    // removes all enumerable route values so they are not processed by the base class
    foreach (string key in enumerables.AllKeys)
    {
      routeValues.Remove(key);
    }

    // lets the base class generate a URL
    VirtualPathData path = base.GetVirtualPath(requestContext, routeValues);

    Uri requestUrl = requestContext.HttpContext.Request.Url;
    if (enumerables.Count > 0 && requestUrl != null && path != null)
    {
      string authority = requestUrl.GetLeftPart(UriPartial.Authority);
      Uri authorityUri = new Uri(authority);
      Uri url = new Uri(authorityUri, path.VirtualPath);
      UriBuilder builder = new UriBuilder(url);

      NameValueCollection queryString = HttpUtility.ParseQueryString(builder.Query);

      // extends the URL's query string with the provided enumerable route values
      queryString.Add(enumerables);

      builder.Query = queryString.ToString();

      path.VirtualPath = builder.Uri.PathAndQuery.TrimStart('/');
    }

    return path;
  }
}

Next, register the created route within your RegisterRoutes method. In the previous post, you can find more details on how to do that.

If you now open your view in the browser, you will notice that MVC renders the URL as expected.

.../Index?names=foo&names=boo&names=moo

Propagate additional URL parameter through all links generated by MVC

If you need to add some parameters to all links generated by MVC, the likely best way to do this is by adjusting ASP.NET routing. Such behavior might be interesting if you want to append some kind of a token to all links in your application. ASP.NET utilizes this technique when cookieless sessions are activated. The framework stores a session ID inside an URL and extracts it back once the server becomes a request for this URL. Another scenario where you can benefit from the parameter propagation is while making links contain a navigation history of a website visitor. The history may be further utilized by a navigation bar, which would show where the user has come from.

If you want to modify URLs before they get rendered in a view, you should create your own route class and extend the GetVirtualPath function with your customization logic.

public class TokenizedRoute : Route
{
  public TokenizedRoute(string url, IRouteHandler routeHandler) : base(url, routeHandler)
  {
  }

  public override VirtualPathData GetVirtualPath(RequestContext requestContext, RouteValueDictionary values)
  {
    values.Add("token", "E0ECE7CB54EA1");

    return base.GetVirtualPath(requestContext, values);
  }
}

Next you should instruct MVC to use the created route. To this end, replace a default route inside your RegisterRoutes method with the tokenized one.

public static void RegisterRoutes(RouteCollection routes)
{
  routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

  TokenizedRoute route = new TokenizedRoute("{controller}/{action}/{id}", new MvcRouteHandler());
  object defaults = new
    {
      controller = "Home",
      action = "Index",
      id = UrlParameter.Optional
    };
  route.Defaults = new RouteValueDictionary(defaults);

  routes.Add("Default", route);
}

If several routes are used in your application, just write a MapRoute extension method that creates a tokenized route and appends it to your route collection. By using this extension method, you would eliminate duplicates in the source code.

To test the previously created route, I have used a simple view with a form and an action link.

<body>
  @using (Html.BeginForm("Save", "Home"))
  {
    <div>
      @Html.LabelFor(m => m.Name)
      @Html.EditorFor(m => m.Name)
    </div>

    <button type="submit">Send</button>
  }
    
  <div>
    @Html.ActionLink("Go to About", "Index", "About")
  </div>
</body>

As you can notice, I have not passed any route values to the Html.BeginForm and Html.ActionLink helpers. However, if you would open the view in browser, you would see that each rendered URL contains a token parameter.

<form action="/Home/Save?token=E0ECE7CB54EA1" method="post">

<a href="/About?token=E0ECE7CB54EA1">Go to About</a>

The tokenized route has done a good job!

It is important to notice that such behavior is to be expected not only from Html.BeginForm and Html.ActionLink helpers, but also from other HTML helpers that utilize the ASP.NET routing mechanism. To the latter belong Html.Action, Url.Action, Url.RouteUrl, Ajax.BeginForm etc.

Handling multiple submit buttons in MVC

In this post I want to show you how to use multiple submit buttons on a single form and how to handle their requests in a controller.

This kind of scenario might be interesting when you need to process form data in two or more different ways. If you, for example, create a post you would probably expect to have two buttons once finished writing it. The first one is to preview your post and the second one is to publish it. Technically you would end up creating a view with a couple of input fields and two submit buttons. The problem with that approach is that the MVC framework can only link one controller action method to an html form out of the box.

However, you can work around this limitation by creating a custom ActionNameSelector attribute. The idea is to mark submit buttons with the names of controller action methods they are related to and to utilize those names while searching for an appropriate action method on the server side.

Here is my view for creating a post:

<!DOCTYPE html>
<html>
<body>
  @using (Html.BeginForm())
  {
    <div>
      Post title:
      @Html.TextBox("title")
    </div>
            
    if (ViewBag.NotificationMessage != null)
    {
    <div>
      @ViewBag.NotificationMessage
    </div>
    }

    <button type="submit" name="action" value="Save">Save my post</button>
    <button type="submit" name="action" value="Publish">Publish my post</button>
  }
</body>
</html>

As you can see, the buttons' value attributes have been set to the names of controller action methods. As next, we create a custom action name selector attribute which is able to pick an appropriate action method.

public class SubmitActionAttribute : ActionNameSelectorAttribute
{
  public override bool IsValidName(ControllerContext controllerContext, string actionName, MethodInfo methodInfo)
  {
    HttpRequestBase request = controllerContext.RequestContext.HttpContext.Request;
    string action = request["action"];

    return methodInfo.Name.Equals(action, StringComparison.InvariantCultureIgnoreCase);
  }
}

And finally, we implement a controller with Save and Publish methods, which are tagged with the previously created attribute.

public class PostController : Controller
{
  public ActionResult Create()
  {
    // this is an entry point to the creation form
    return View();
  }

  [SubmitAction]
  [HttpPost]
  public ActionResult Save(string title)
  {
    ViewBag.NotificationMessage = string.Format("Post {0} has been saved", title);

    return View("Create");
  }

  [SubmitAction]
  [HttpPost]
  public ActionResult Publish(string title)
  {
    ViewBag.NotificationMessage = string.Format("Post {0} has been published", title);

    return View("Create");
  }
}

Voila! Now you are able to handle multiple submit requests inside your MVC controller.