ASP.NET MVC Multitenancy, Part 0 - Subdomain VS URL Path

With multitenancy you will need to decide how you are going to identify your tenants. There are two user friendly approaches, subdomain fictionaltenant.multitenantedapp.com or URL Path multitenantedapp.com/fictionaltenant/. In this post we are going to explore disadvantages and advantages of these two approaches.

Subdomain

Configuration

It is surprisingly easy to set this up for a simple deployment model. This is what you have to do:

Setup DNS record

I went to my domain registrar site and configured all my traffic on *.multitenantedapp.com to point to my Azure Web App domain e.g. *.multitenantedapp.com -> zkwildcardtest.azurewebsites.net.

Setup wildcard hostname

After that all I had to do was configure my Azure Web App to accept traffic for the hostname *.multitenantedapp.com. If you are using a web server such as IIS you will just need to change the IIS hostname binding.

Deploy your app

Just to make sure everything works as it should, I have deployed a simple test app that reads the URI and extracts subdomain from it. I went to tenanta.multitenantedapp.com and this is what I saw:

Why should you use this approach?

Advance estate management

For me advance estate management is the main reason why you would go for this approach. As your application scales you will want to split up your deployment zones. For example, you might want to deploy 50 tenants to zone A and other 50 tenants to zone b. What if some tenants in zone A are being noise neighbours (using your application a lot)? How will you migrate them to the zone C? With subdomain you can use DNS to redirect traffic, subdomain will remain the same, just the DNS mapping will need to change.

Customisation

You can enable customisation, so for example if you own fictionalcompany.com domain you can point files.fictionalcompany.com to fictionalcompany.multitenantedapp.com. This sounds simple but there is more to it. You need register this hostname on your web server. Also if you would like your connection to be secure you will need to get fictionalcompany to give you the subdomain SSL certificate.

Log quality

Here is how the subdomain IIS log looks like:

date: 2018-01-30 
time: 22:25:30
s-sitename: ZKWILDCARDTEST
cs-method: GET
cs-uri-stem: /
cs-uri-query: -
s-port: 80
cs-username: -
c-ip: 102.152.31.182
cs(User-Agent): -
cs(Cookie): -
cs(Referer): -
cs-host: fictionalcompany.multitenantedapp.com TENANT'S NAME APPEARS HERE
sc-status: 200
sc-substatus: 0
sc-win32-status: 0
sc-bytes: 1958
cs-bytes: 1066
time-taken: 0

cs-host (client-server host) actually shows fully qualified subdomain. This is great, as it is very easy to parse and hard to spoof. This can be used for business and security analytics.

URL Path

Configuration

There is not much to URL path setup. You just need to configure your web application's framework mapping. If you are using ASP.NET MVC it would look something like this:

  
namespace Web
{
    public class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            //...

            routes.MapRoute(
                "multi",
                "{tenant}/{controller}/{action}/{id}",
                new {controller = "Dashboard", action = "Index", id = UrlParameter.Optional}
            ).DataTokens.Add("name", "webclient_multitenancy");

        }
    }
}

For full mapping explanation take a look at this article.

With this approach you can introduce proxy layer, that proxy layer can then direct your traffic to different deployment zones. So in a way you can achieve same thing. However you are introducing additional infrastructure, which will require implementation, deployment, hosting and maintenance. If you can avoid it, you should.

Why should you use this approach?

The main benefit is that it is simple to implement and requires no DNS work of any kind. However with this approach you do compromise on advance estate management, customisation and log quality.

Here is how the IIS log would look like with URL path:

   
date: 2018-01-30 
time: 22:25:30
s-sitename: ZKWILDCARDTEST
cs-method: GET
cs-uri-stem: /fictionalcompany/somepage/ TENANT'S NAME APPEARS HERE
cs-uri-query: -
s-port: 80
cs-username: -
c-ip: 102.152.31.182
cs(User-Agent): -
cs(Cookie): -
cs(Referer): -
cs-host: multitenantedapp.com THIS IS NOT SHOWING TENANT'S NAME ANYMORE
sc-status: 200
sc-substatus: 0
sc-win32-status: 0
sc-bytes: 1958
cs-bytes: 1066
time-taken: 0

The main thing to notice is that client server host is now not showing the tenant's name. Now you need to parse cs-uri-stem which is more difficult. Also if your application is a single page application that performs all operations via API, chances are that tenant's name will be in the header which means you will not know which tenant is making the API requests.

Conclusion

If you are starting to work on a large-scale project, I strongly recommend that you give it a bit extra time and invest in the subdomain implementation. It's just so versatile, it will help you scale long term. However if you are working for a small start-up, you don't have the DNS skills in your team and you are not sure where project is going you can just use the URL path to get you off the ground.

Found this useful?
Browse "Multitenancy-Microservice-FederatedIdentity-Example" Repository On Github.

*Note: Code in this article is not production ready and is used for prototyping purposes only. If you have suggestions or feedback please do comment.

ASP.NET MVC Multitenancy, Part 3 - NHibernate Tenant Data Filtering and Shared Database

In part 1 we have established tenant context. In part 2 we have configured authentication. Now we will be getting tenant data out of our RDBMS. Before we start storing and retrieving data it's important that you figure out what kind of multitenancy data segregation model you are going to go for. This is an important point and it really depends on your business requirements. To help you choose check out this Microsoft article: Multi-tenant SaaS database tenancy patterns.

My sample app is called “Stop The Line”, it is very basic. It holds very little data, and I have no idea if it's going to take off or not. I have decided to go with "Multi-tenant app with a single multi-tenant database" pattern. Put simply, this means that all my tenants are going to share the same database and I am going to use foreign key to discriminate between them. This is one the cheapest and fastest ways to implement tenancy.

When it comes to development what I don't want to do is to specify tenant id in each query and I don't want to manually specify tenant id when I create and update my domain objects. Remember the onion architecture, as far as I am concerned tenancy is an infrastructure concern and it should stay out of my domain. After few minutes of searching I have found this great blog post that meets these requirements: Bolt-on Multi-Tenancy in ASP.NET MVC With Unity and NHibernate: Part II – Commingled Data. Let's incorporate this in to my sample app.

Implementation

In my sample app it's possible to query in multitenancy context and without. For example, if a new tenant is signing up for an account then we would not apply the multitenancy context. This is because TenantContext will not exist until tenant was created in the first place.

Also in this implementation I have tried to keep infrastructure decoupled so that it would not be too difficult to move to another database tenancy pattern.

Base Configuration

As there are going to be two different contexts, non-multitenant and multitenant. I have decided to create NHConfiguration base class.

NHConfiguration.cs

   
public abstract class NHConfiguration
{
    public abstract global::NHibernate.Cfg.Configuration Config { get; }
}

Below is the standard configuration that is going to be used for non-multitenant read/write.

Configuration.cs

   
public class Configuration : NHConfiguration
{
    public override NHibernate.Cfg.Configuration Config => config;

    private NHibernate.Cfg.Configuration config;

    public Configuration()
    {
        this.config = Fluently.Configure()
            .Database(MsSqlConfiguration.MsSql2012
                .ConnectionString(c => c.FromConnectionStringWithKey("default"))
#if DEBUG
                .ShowSql()
                .FormatSql()
#endif
                .AdoNetBatchSize(50)
            ).Mappings(m =>
                m.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly())
            )
            .ExposeConfiguration(c => SchemaMetadataUpdater.QuoteTableAndColumns(c))
            .BuildConfiguration();
    }
}

This configuration will get injected into the Unit of Work. If you are new to this, then you should read Unit Of Work Abstraction For NHibernate or Entity Framework C# Example. I have omitted the class implementation.

   
public class NHUnitOfWork : IUnitOfWork
{
    //..
    private NHConfiguration nhConfiguration;

    public NHUnitOfWork(NHConfiguration nhConfiguration)
    {
        this.nhConfiguration = nhConfiguration;
        //..
    }
    //..
}

Unfortunately with this solution you will still need to add the private "tenantId" field to your domain classes.

   
public class Some 
{
    //..
    private Guid tenantId;
}

The good news is that you don't have to do with it anything inside your domain. It will be handled by infrastructure. The bad news is that it still there. I have spent few hours trying to remove it, I have used some reflection and proxy class generation techniques, unfortunately to no avail. If you find an elegant solution please do share it.

Multitenant Writes

This is where standard configuration is expanded and interceptor is set. Interceptor will be used when some object is updated or saved.

ConfigurationMultiTenancy.cs

   
public class ConfigurationMultiTenancy : Configuration
{
    public ConfigurationMultiTenancy(NHSharedDatabaseMultiTenancyInterceptor interceptor)
    {
        this.Config.SetInterceptor(interceptor);
    }
}

Above you will notice that NHSharedDatabaseMultiTenancyInterceptor is injected in to ConfigurationMultiTenancy. Here is the actual interceptor.

NHSharedDatabaseMultiTenancyInterceptor.cs

   
public class NHSharedDatabaseMultiTenancyInterceptor : EmptyInterceptor
{
    readonly TenantContext tenantContext;

    public NHSharedDatabaseMultiTenancyInterceptor(TenantContext tenantContext)
    {
        this.tenantContext = tenantContext;
    }

    public override bool OnSave(object entity, object id, object[] state, string[] propertyNames, IType[] types)
    {
        int index = Array.IndexOf(propertyNames, "tenantId");

        if (index == -1)
            return false;

        state[index] = this.tenantContext.ID;

        entity.GetType()
                .GetField("tenantId", BindingFlags.Instance | BindingFlags.NonPublic)
                .SetValue(entity, tenantContext.ID);

        return base.OnSave(entity, id, state, propertyNames, types);
    }
}

Multitenant Reads

Configuring reads is much simpler. You just need to enable a filter and provide the parameter. First you need to define the actual filter.

MultitenantFilter.cs

   
public class MultitenantFilter : FilterDefinition
{
    public MultitenantFilter()
    {
        WithName("MultitenantFilter").AddParameter("TenantId", NHibernateUtil.Guid);
    }
}

Then you need to enable it.

NHUnitOfWorkMultitenancy.cs

   
public class NHUnitOfWorkMultitenancy : NHUnitOfWork
{
    public NHUnitOfWorkMultitenancy(NHConfiguration nhConfiguration, TenantContext tenantContext) 
        : base(nhConfiguration)
    {
        this.Session.EnableFilter("MultitenantFilter").SetParameter("TenantId", tenantContext.ID);
    }
}

Finally you just need to apply it.

   
public class SomeMap : ClassMap<Some>
{
    public SomeMap()
    {
        //..
        //This is used for writes
        Map(Reveal.Member<Some>("tenantId")).Not.Nullable();

        //This is used for reads
        ApplyFilter<MultitenantFilter>("TenantId = :TenantId");
    }
}

Want to see this in action? Check out
"Multitenancy-Microservice-FederatedIdentity-Example" Repository On Github.

*Note: Code in this article is not production ready and is used for prototyping purposes only. If you have suggestions or feedback please do comment.

ASP.NET MVC Multitenancy, Part 2 - OpenID Connect

In part 1 we have established tenant context. Most business applications need authentication. I have decided to use OpenID Connect, luckily for me it comes with awesome middleware and it's very easy to configure. Here is how you would do it:

public class Startup
{
    public void Configuration(IAppBuilder app)
    {
        //...
        app.UseOpenIdConnectAuthentication(new OpenIdConnectAuthenticationOptions
        {
            ClientId = "clientid",
            Authority = 'authority",
            RedirectUri = "http://mysite.com/"
        });
    }
}

However, not so fast, for each tenant you will need to configure client id, authority, url, etc. This also needs to be done at runtime as you don't want to ship new code every time new tenant registers to use your software. I have looked around for a solution and it turns out that this is a known problem, just take a look at the The Grand Auth Redesign. After a surprising amount of searching I have stumbled upon this amazing Multi-tenant middleware pipelines in ASP.NET Core blog post by Ben Foster. This solution is so damn good. Please read it. Unfortunately it did not work for me out of the box I had to port it over from ASP.NET Core to ASP.NET.

Hopefully by blogging and linking to Ben's article it will be easier for others to find it.

Example Usage

public class Startup
{
    public void Configuration(IAppBuilder app)
    {
        //..
        app.UsePerTenant((tenantContext, appBranch) =>
        {
            appBranch.SetDefaultSignInAsAuthenticationType(CookieAuthenticationDefaults.AuthenticationType);

            appBranch.UseCookieAuthentication(new CookieAuthenticationOptions
            { 
                CookieName = $"OAuthCookie.{tenantContext.FriendlyName}"
            });

            appBranch.UseOpenIdConnectAuthentication(new OpenIdConnectAuthenticationOptions
            {
                ClientId = tenantContext.AuthClientId,
                Authority = tenantContext.AuthAuthority,
                RedirectUri = $"http://localhost:2295/{tenantContext.FriendlyName}/"
            });
        });
    }
}

ASP.NET Implementation

TenantPipelineMiddleware.cs

   
/// <summary>
/// Workaround for the known OpenID multitenancy issue https://github.com/aspnet/Security/issues/1179
/// based on http://benfoster.io/blog/aspnet-core-multi-tenant-middleware-pipelines and https://weblogs.asp.net/imranbaloch/conditional-middleware-in-aspnet-core designs 
/// </summary>
public class TenantPipelineMiddleware : OwinMiddleware
{
    readonly IAppBuilder rootApp;
    readonly Action<TenantContext, IAppBuilder> newBranchAppConfig;
    readonly ConcurrentDictionary<TenantContext, Lazy<Func<IDictionary<string, object>, Task>>> branches;

    public TenantPipelineMiddleware(OwinMiddleware next, IAppBuilder rootApp, Action<TenantContext, IAppBuilder> newBranchAppConfig)
        : base(next)
    {
        this.rootApp = rootApp;
        this.newBranchAppConfig = newBranchAppConfig;
        this.branches = new ConcurrentDictionary<TenantContext, Lazy<Func<IDictionary<string, object>, Task>>>();
    }

    public override async Task Invoke(IOwinContext context)
    {
        TenantContext tenantContext = context.GetTenantContext();

        if (tenantContext == null || tenantContext.IsEmpty())
        {
            await this.Next.Invoke(context);
            return;
        }

        Lazy<Func<IDictionary<string, object>, Task>> branch = 
            branches.GetOrAdd(tenantContext, new Lazy<Func<IDictionary<string, object>, Task>>(() =>
            {
                IAppBuilder newAppBuilderBranch = rootApp.New();
                newBranchAppConfig(tenantContext, newAppBuilderBranch);
                newAppBuilderBranch.Run((oc) => this.Next.Invoke(oc));
                return newAppBuilderBranch.Build();
            }));

        await branch.Value(context.Environment);
    }
}

OwinContextExtensions.cs

   
public static class OwinContextExtensions
{
    static string tenantContextKey = "tenantcontext";

    public static TenantContext GetTenantContext(this IOwinContext context)
    {
        if (context.Environment.ContainsKey(tenantContextKey))
        {
            return (TenantContext)context.Environment[tenantContextKey];
        }
        return null;
    }
}

AppBuilderExtensions.cs

   
public static class AppBuilderExtensions
{
    public static IAppBuilder UsePerTenant(this IAppBuilder app, Action<TenantContext, IAppBuilder> newBranchAppConfig)
    {
        return app.Use<TenantPipelineMiddleware>(app, newBranchAppConfig);
    }
}

Found this useful?
Browse "Multitenancy-Microservice-FederatedIdentity-Example" Repository On Github.

*Note: Code in this article is not production ready and is used for prototyping purposes only. If you have suggestions or feedback please do comment.

Cloud Architecture & Engineering, B2B Revolution and B2C Evolution

Few years ago I was having a conversation with one of my friends about cloud, SaaS and multitenancy architecture. I was telling him how different it was to normal “hosted” app architecture. That we can setup new business tenant with custom url in seconds. He has said, well, instant tenant provisioning and custom URLs are not new. So what’s so special about it? Blogger and WordPress have been doing it for years. At that point in time I have struggled to answer his question, there was too much to say, ideas in my head were too raw. In this article I am going to explain why cloud, SaaS and multitenancy is revolutionary.

Before we start, let’s get few definitions out of the way:

Model	Description	Example
B2B, Business Applications	Business sells to businesses	Oracle, Visual Studio Team Services, Sage & Salesforce
B2C, Consumer Applications	Business sells to consumers	Spotify, Netflix & Blogger.

Your solution’s technical requirements will differ a lot depending on what business model you follow, we will be compare typical B2C and B2B requirements shortly.

Term	Somewhat My Definition
Software as a Service (SaaS)	This is where you don’t care about software versioning, upgrades, deployments, where it sits, etc. You just pay for it and use it. If you are using Spotify, you are using SaaS.
Cloud	Before cloud we had hosting companies, where you could rent a VM and host your site. Now we have Cloud, which is the same thing but it’s just much smarter and comes with lots of tools. Companies such as Microsoft offer prepackaged services that seamlessly scale your apps horizontally and all you have to do is just upload your binaries via FTP to a folder.
Multitenancy	Traditionally each company would get their own “installation”, with multitenancy there is only one installation and everyone is seamlessly using it.

These concepts are very important as they will feed of each other, as I will illustrate this later on.
People use cloud, multitenancy and SaaS as synonyms and this is a mistake (I make this mistake as well).

B2C, Consumer Applications

When it comes to selling directly to customers things are easier, there are a lot less hoops to jump through. Here are some technical requirements that you might need to follow when you build B2C app:

Requirement	Description
Availability	Consumer facing sites, need to be highly available, if they are not, this can cause reputation damage and loss in revenue.
Authentication	Consumers need to be able to login using some form of login, Google, Facebook, etc.
PCI DSS	When customers make payments it’s important that card data is used & stored securely.
Recovery Point & Time Objectives	Similiar to availability it’s critical that if data was corrupted or upgrade has gone wrong that app can recover quickly with as much data as possible.

B2C architecture before cloud:

B2C architecture after cloud:

This list is not complete, however the point is that typical B2C apps don’t have lots of technical requirements enforced upon them from consumers. This is why B2C service is so much cheaper, how much do you pay for Netflix and Spotify?

B2B, Business Applications

Here are some technical requirements that businesses often enforce on to other businesses:

Requirement	Description
Corporate Identity Integration (Single Sign On Authentication)	Medium/Large size companies have their own identity apps such as AzureAD, they don’t want to use your built in authentication mechanism if they can enable Single Sign On. This is where Federated identity comes in.
Audit	They would like assurance and full trail of what’s happening inside the system, so they will ask for audit.
Data restore & Backup	If company makes a mistake in their environment they will want to restore their data back to how it was.
Integration	Often they have their own systems and they will want to integrate their existing applications with your application.
Security clearance	Depending on the industry your business customers might want your ops staff to be security cleared, if this is not possible then sensitive data needs to be hidden or encrypted.
Escrow	Businesses need to ensure businesses continuity, to do this they need to be able keep going even your business will go out of business.
ISO 27001	Key management, Antivirus, Firewalls, Segregation of duties, Pen testing, etc.
Reporting	Access to data to be able to report on it.
Customisation & Configuration	Data retention, authorisation, workflow, etc.
Availability	Business sites don’t necessarily need to be as highly available as in a lot of industries people work 9am-5pm.
PCI DSS	When businesses make payments it’s important that card data is used & stored securely.
Recovery Point & Time	It’s critical that if data was corrupted or upgrade has gone wrong that app can recover quickly, business finance or patient data can’t be lost, huge amount of productivity can be lost if this not designed correctly.
Support	Be able to troubleshoot issues quickly.
On-prem installation	Company is able to install your software in their own environment.

This list is not complete, however if you compare B2B to B2C it’s easy to see that there is a lot more to it. So when companies charge other companies a lot more for their services we can appreciate why this happens.

B2B architecture before cloud and multitenancy:

B2B architecture after cloud and multitenancy:

So where is the revolution?

Consumer facing sites were doing SaaS with multitenancy for years. Websites like YouTube and Ebay have been scaling their solutions for a long time. Cloud has just made it easier for them to scale and in turn it has reduced their hosting cost. Business applications were doing SaaS for a long time, however multitenancy and cloud was just not a thing. Most service providers painfully installed software for each customer manually, then, they upgraded each customer one at the time and they monitored each customer one at the time. You get the picture. Customer provisioning took weeks and maybe months. Multitenancy offers something truly awesome, it removes slow per customer installation and upgrade problem and in turn it dramatically speeds up the software delivery and customer onboarding process. Cloud removes the infrastructure maintenance and provisioning burden. Together they simply transform business in a remarkable way. B2B apps can finally compete with B2C apps in terms of feature delivery speed and cost.

Conclusion

Let’s come back to the conversation with my friend. He thought that nothing was new here because he was thinking of B2C evolution. While I was thinking of B2B where multitenancy and cloud is transforming businesses and revolutionising business applications.

Azure App Services Web App Antivirus - Concept

Introduction

Good web application antivirus solution needs to meet the following acceptance criteria:

• Real-time inbound and outbound file scanning
• Low latency, sub second performance
• Transparent, no need to make changes to your web app
• Requires no maintenance and is easy to setup

If you are using PaaS service such as Azure Web Apps your antivirus options are:

Option	Limitation	Installation Effort	Maintenance Effort	Cost	Meets criteria
Firewall with ICAP integration	Expense and ongoing maintenance	High	Medium	High	Yes
Send files to the antivirus API from your app	Extra unnecessary development work	Medium/High	Medium	Low	No
Background service that scans your files	Not real time, high possibility that viruses will be uploaded and downloaded	Medium	Low	Low	No
Reverse proxy binary content forwarding	Scans only uploads	Medium	Low	Low	No
IIS Filter (this solution)	PoC, not production ready	Medium	Low	Low	Yes

In depth exploration of different antivirus options is beyond the scope this article.

IIS Filter Solution, inspired by ModSecurity

Sounds interesting?
Explore “Azure-Web-Apps-Antivirus” source code and instructions on Github.

This article will only cover the interesting parts of the solution, if you have questions please do comment.

AVFilter, File Upload/Download Filter

Filtering uploads and downloads is relatively straight forward thanks to IIS http modules, read more about them here.

Filtering Uploads

Here is an example of how you can detect file upload inside the http module:

 private void Context_PreRequestHandlerExecute(object sender, EventArgs e)
  {
      HttpApplication httpApplication = (HttpApplication)sender;
      HttpRequest httpRequest = httpApplication.Context.Request;
      
      if (httpRequest.RequestType == WebRequestMethods.Http.Post)
      {
          if (httpRequest.Files.Count != 0)
          {
              //Check files
          }
      }
  }

Take a look at the full implementation here.

Filtering Downloads

When it comes to intercepting downloads it’s not as simple. Unfortunately outgoing traffic is written to the System.Web.HttpResponseStreamFilterSink and this stream is write only, so you can’t read it. So the only option that I and Google can think of is to proxy the outgoing stream. To make this happen I have created proxy class that changes write only steam to write/read stream, I have conveniently called it ReadWriteProxyStream, take a look at the implementation here.

Now that we have ReadWriteProxyStream class, we need to hook it up response filter, this is done when web request is first received:

    private void Context_PreRequestHandlerExecute(object sender, EventArgs e)
    {
        HttpApplication httpApplication = (HttpApplication)sender;
        
        httpApplication.Context.Response.Filter = new ReadWriteProxyStream(httpApplication.Context.Response.Filter);
    }

Take a look at the full implementation here.

Before web response is returned back to user we need to check it, we can do this by reading the stream:

    private void Context_PreSendRequestContent(object sender, EventArgs e)
    {
        HttpApplication httpApp = (HttpApplication)sender;
        HttpResponse httpResponse = httpApp.Context.Response;
        
        if(httpResponse.ContentType == "application/octet-stream")
        {
            ReadWriteProxyStream filter = httpResponse.Filter as ReadWriteProxyStream;

            if (filter != null)
            {
                byte[] data = new byte[filter.Length];
                filter.Position = 0;
                filter.Read(data, 0, data.Length);

                //Check files
            }
        }
    }

Take a look at the full implementation here.

AVFileReceiver, Virus Scanning

For this proof of concept I have used Symantec virus scanner which comes with CLI, and to test it I have used EICAR test virus. EICAR test virus is harmless, it can’t actually do anything to your machine.

Symantec CLI will scan the specified file synchronously, so if the file is no longer there you know that it was not safe:

  using (Process process = Process.Start(
        "C:\\Program Files (x86)\\Symantec\\Symantec Endpoint Protection\\DoScan.exe",
        "/ScanFile " + filePath))
    {
        process.WaitForExit();
    }

    if (!File.Exists(filePath))
        throw new Exception("File was not safe!")

Take a look at the full implementation here.

Found this useful?
Explore “Azure-Web-Apps-Antivirus” source code and instructions on Github.

Final Thoughts

If you like this concept then please do share and rate this blog post. If this post will generate enough interest I will productionise this concept. However, if you just can’t wait, then here is what you need to do to make this production ready:

• Secure communication between AVFilter and AVFileReceiver
• Secure AVFileReceiver access (authentication, network security group, etc)
• Reduce VM maintenance through automation, use PowerShell files provided and place Cloud Service inside availability and upgrade group
• Reduce memory consumption by intercepting binary responses only
• Make debugging easier by logging exceptions
• Improve performance by forwarding binary content using MTOM encoding to the AVFileReceiver and remove AVFilter temp storage
• Improve scalability and responsiveness by using Async in the AVFileReceiver and AVFilter

Basic SaaS conversion guide, migrate your application to the Azure Cloud

A lot of organisations are currently converting their applications to SaaS. Unfortunately not much is written about “conversion & migration” (that I actually found useful), and I could have done with an article like this few months ago.

Objective of this article is to give you high level pragmatic overview of what you will need to think about, and do, when your company decides to convert / migrate your app to the Cloud.
This article will expose the unknown unknowns, force you to rethink your assumptions, ask you some tough provoking questions and offer some tips so that you can give your company better estimates and an idea of what involved.

Before we start, I would like to let you know that I was involved in the B2B SaaS conversion project, and migrated to Microsoft Azure. I will be drawing from my personal experience a lot, by doing so, hopefully I will be able to pass on some real world experience.

This article is broken into two parts:

• Business section is for executives and software architects, it introduces you to some of Cloud concepts, it’s suppose to make you think about the wider company, costs, why you are migrating and what’s going to be involved.
• Technical section is for software architects and developers, it’s suppose to make you think about how you can go about the migration and what technical details you will need to consider.

Business: Costs, Why and Impact.

1. Understand the business objectives

Converting your app to SaaS is not going to be cheap, it can take from few weeks to number of months (depending on the product size). So before you start working on this, understand why the business wants to migrate.

Few good reasons to migrate:

• Removal of existing data center (cost saving) 
• International expansion (cost saving) 
• A lot of redundant compute that is used only during peak times (cost saving) 
• Cloud service provider provides additional services that can complement your product (innovation)

It’s vital that you understand the business objectives and deadlines, otherwise you will not be able to align your technical solution with the business requirements.

2. Understand the terminology

Here are some terms you need to get to know:

Term	Wikipedia Definition
Multi Tenancy	The term “software multitenancy” refers to a software architecture in which a single instance of software runs on a server and serves multiple tenants. A tenant is a group of users who share a common access with specific privileges to the software instance.
IaaS (Infrastructure as a Service)	“Infrastructure as a Service (IaaS) is a form of cloud computing that provides virtualized computing resources over the Internet. IaaS is one of three main categories of cloud computing services, alongside Software as a Service (SaaS) and Platform as a Service (PaaS).”
PaaS (Platform as a Service)	“Platform as a service (PaaS) is a category of cloud computing services that provides a platform allowing customers to develop, run, and manage applications without the complexity of building and maintaining the infrastructure typically associated with developing and launching an app.”
SaaS (Software as a Service)	Software as a service (SaaS; pronounced /sæs/) is a software licensing and delivery model in which software is licensed on a subscription basis and is centrally hosted. It is sometimes referred to as “on-demand software”. SaaS is typically accessed by users using a thin client via a web browser.

Here is a picture that explains IaaS, PaaS and SaaS very well:

3. Migration Approaches

Migration Approach	Description
Lift and shift	Take your existing services and just migrate it to the IaaS (VMs), and in the future redesign your application for the Cloud.
Multi tenancy	Redesign your application so that it supports multi tenancy, however for now ignore other benefits. This migration approach might use IaaS with combination of PaaS. For example you might host your application in a VM, but use Azure SQL.
Cloud native	Redesign your application completely so that it’s Cloud native, it’s has built in multi tenancy and uses PaaS services. For example you will use Azure Web Apps, Azure SQL and Azure Storage.

What approach you are going to take will depend on skills within the organisation, and what if any, products that you already have in the Cloud and how much time you have before you need to hit the deadline.

I am not going to focus on lift and shift in this article as it’s just taking what you already have and moving it to the Cloud, strictly speaking that is not SaaS, it is “Hosted” on the Cloud.

4. Identify technology that you will need

Chances are your topology will be a combination of:

Service		Technology Required
Front-end	ASP.NET MVC, Angular, etc	IIS with .NET Framework
Back-end	Java, .NET, Node.JS, etc	Compute node that can host JVM, or WCF and can listen on a port
Persistence	SQL Server, MySQL, etc	SQL Server 2016, Azure Storage
Networking	Network segregation	Sub networks, VNET, Web.Config IP Security, Access Control List

Understand what you actually need, don’t assume you need the same things. Challenge old assumptions, ask questions like:

• Do you really need all of that network segregation? 
• Do you really need to have front-end and back-end hosted on different machines? By hosting more services on a single machine you can increase compute density and save money. Need to scale? Scale the whole thing horizontally. 
• Do we really need to use relational data store for everything? What about NoSQL? By storing binary data in Azure Storage you will reduce Azure SQL compute (DTUs) which will save you a lot of money long term. 
• In this new world, do you still really need to support different on-prem platforms? SQL Server and MySQL? How about Linux and Windows? By not doing this, you can save on testing, hosting costs, etc.

5. Scalability

The greatest thing about Cloud is scalability. You can scale effortlessly horizontally (if you architect your application correctly). How will your application respond to this? What is currently the bottleneck in your architecture? What will you need to scale? Back-end? Front-end? Both? What about persistence?

Find out where the bottlenecks are now, so that when you redesign your application you can focus on them. Ideally you should be able to scale anything in your architecture horizontally, however time and budget might not allow you to do this, so you should focus on the bottlenecks.

6. Service Level Agreements (SLA) & Disaster Recovery

Services on the Cloud go down often, and often it’s fairly random what goes down, please do take a look at this Azure Status History.

The good news is that there is plenty of redundancy in the Cloud and you can just fail over or re-setup your services and you will be up and running again.

Something to think about:

• What kind of SLA you will need, 99.9%? Is that per month or a year? 
• What kind of Recovery Point Objectives will you need to reach? 
• What kind of Recovery Time Objectives
• will you need to reach? 
• How much downtime did your prefered Cloud service provider has experienced recently? For example Azure had around 11 hours of downtime this year (2016). 
• If you store some data in relational database, some in NoSQL, some on the queue, when your services all go down, how are you going to restore your backups? 
• How will you handle data mismatch e.g. Azure SQL being out of sync with Azure Storage?

Higher SLA more likely you will need to move towards active-active architecture, but it’s complex, requires maintenance and lots of testing. Do you really need it? Will active-cold do?

Please check out this great Microsoft article: Designing resilient applications for Azure.

7. Security & Operations

Security and Operations requirements are different on the Cloud. Tooling is not as mature, especially if you are looking to use PaaS services, and tooling that is available out of the box takes time to get used to. For example Azure App Insights, Security Center, etc.

Find out what security controls will need to be in place, find out what logs and telemetry ops team will need to do their job.

8. Runtime cost model

Start putting together runtime cost model for different Cloud service providers. At this stage you know very little about these services, however you should start estimating the runtime costs. Cloud service providers normally provider tools to help you with this, for example Azure provides Azure Pricing Calculator.

9. Staff Training

Now that you have a rough idea what service provider you will choose, start looking into training courses. Training can be official e.g. staff can get Cloud Platform MCSA certification, or it can be more unofficial. However, no matter what you do, I encourage you to give your staff time to play around with different services, put together few PoCs, read few books e.g. Developing Microsoft Azure Solututions, watch some Azure videos e.g. Azure Friday.

This training will require some time, if you haven’t got time, consider consultancy.

10. Consultancy

If you would like to get some help there are number of things you can do with Azure: 

• Hire Azure migration consultants 
• Contact “Microsoft Developer eXperience” team, they might be able to help 
• you with migration by investing in your project (free consultation & PoCs). 
• Sign up for Microsoft “Premier Support for Developers”, you will get a dedicated Application Development Manager who will be able to assist you and guide you and your team.

I strongly advise you to grow talent internally and not solely rely on consultants. Hybrid approach works best, get some consultancy and get them to coach your internal team.

Technical: How are you going to do it?

1. Multi tenancy data segregation

Hosted applications tend to be installed on a machine and used by one company, however SaaS applications are installed and used by lots of different companies. With sharing comes great responsibility, there are number of ways that you can handle multi tenancy data segregation: 

• Shared Database, Shared Schema (Foreign Keys partition approach) 
• Shared Database, Separate Schema 
• Separate Database

What approach you go for depends on your requirements. Microsoft has written an article Multi-Tenant Data Architecture, please read it.

What is my take? If your app is very simple (To Do List, Basic document store, etc), go for Foreign Key partition. If you are app is more complicated e.g. finance or medical system I would go for the separate database / separate persistence. Separate persistence will reduce the chance of data cross tenant data leak, make tenant deletion simpler and more importantly it will makes ops easier, you can upgrade their persistence individually, you can back them up more often if you need to, and most likely you will not need to worry about sharding. However, separate persistence approach is not simple to implement, it requires a lot of development.

2. Login Experience

Now that users access your application there are number of ways for them to access their 

tenant area: 

• Dedicated URL 
• Recognise Possible Tenants On Login

On the Cloud user experience will need to change, because many businesses are using it 

at the same time you need to somehow know who is who. There are number of ways you can do this:

Dedicated URL

You can give your tenants dedicated URL so that they can login into their app. For example you can give them access to: yourapp.com/customername or customername.yourapp.com.

However, this approach will require you to send out an email to the tenant, informing them 

what their URL is. If they forget the URL they will end up contacting your support team.

Recognise Possible Tenants On Login

Tenant goes to yourapp.com and logs in. When they login they presented with possible tenants that they can access.

With this approach tenants don’t need to remember their URL, however you need to introduce extra step before they login and you need to scan all possible tenants to see if the logged in user exists. This is more work, not to mention, what if your customer wants to use AzureAD? How do you know which AzureAD should be used? Now you will need to introduce mapping tables, extra screens, etc.

3. Application state, is your application already stateless?

If you need your application to scale out then your compute instances should not keep any state in memory or disk. Your application needs to be stateless.

However, if temporarily you need to keep some basic state, like session state then you are in luck, Microsoft Azure allows you to use ARR (Application Request Routing) so that all client requests get forwarded to the same instance every time. This should be a temporary solution as you will end up overloading single instance as it will take a while for cookies to expire and spread the load to other instances.

4. What cloud services should you use?

This largely comes down to the security and performance requirements. 

Azure Web Apps are great, however they don’t support VNETs, which means you can’t stop OSI level 3 traffic to Front-end and Back-end. However you can use Web.Config IP Security to restrict access to front-end, back-end and Azure SQL supports Access Control List (ACL). Azure Web Apps also don’t scale very well vertically, so if you have lots of heavy compute it might make more sense economically for you to use Service Fabric or Cloud Services so that you can scale vertically and horizontally.

However, Azure Web Apps require no maintenance, they are easy to use, they scale almost immediately and they are very cheap.

What is my take? Always go with PaaS services and avoid IaaS as much as you can. Let Azure do all of the operating system and service patching / maintenance. Go to IaaS only if you have no other choice.

5. Telemetry & Logging

If you are going to use pure PaaS services then most like you will not be able to use your traditional ops tools for telemetry. The good news is that ops tooling on the Cloud is getting better.

Azure has a tool called App Insights it allows you to: 

• Store / view your application logs (info, warnings, exceptions, etc). You will need to change your logging appender to make this work. 
• Analyse availability 
• Analyse app dependencies 
• Analyse performance 
• Analyse failures 
• Analyze CPU / RAM / IO (IaaS only)

Also there is a tool called Azure Security Center, it works very well if you have VMs in your subscription, it will guide you and give you best practices. It also has built in machine learning so it analyses your infrastructure and if it will see something unusual it will flag it up and and notify your ops team. It’s very handy.

6. Always test with many instances

You should be running your application with at least 2 compute instances. Last thing you want to happen is to come to the release date and scale out and find out that your application is not scaling. This means anything that can be scaled horizontally should be scaled so you are always testing.

7. Authentication

Multi tenancy makes authentication hard, it makes it even harder if your application is B2B. Why? Well because all businesses have different authentication requirements, they might have AD and they want to integrate it with your app and enable Single Sign On, they might be a hip start-up and they will want to use Facebook to login into your app. This should not be overlooked, you might need to find open source authentication server or use service such as AzureAD.

What is my take? Find an open source authentication server and integrate with it. Avoid AzureAD unless your customers pay for it. AzureAD is very expensive, basic tier cost around £0.60 per user and Premium 2 tier costs around £5.00 per user!

8. Global Config

Hosted applications tend to be installed, so they come with installers. These installers change app keys in the config. For example if your app integrates with Google Maps your installer might ask your customer to put in the Google Map app keys to enable Google Maps features. Now in the Cloud world this is very different, customers don’t care about this, they just want to login and use your software. So these keys are going to be the same for everyone, this means that most of these keys will need to be migrated out of the database and config files to App Settings (Environment variables).

9. Relational Database Upgrades

After all these years the hardest things to update are still databases. This is especially the case if you go for the database per tenant approach. You release your software, services are now running using the new version, now you decide to upgrade all database schemas, what if one of them fails? Do you roll back the entire release? When do you upgrade the databases? Do you ping every tenant and upgrade them? Do you do it when they login?

There are some tools that can help you with database upgrades: 

• Flyway (Java)
• DB Up (.NET)

These tools automatically patch your databases to the correct version level.

However how you roll back, hot fix and upgrade your tenants is still up to you and your organisation.

What is my take? Use tools like Flyway, Evolutionary Database Design approach and roll forward only. Find a problem, fix it and release the hotfix.

10. Encryption

As you are storing your customers data in someone else’s data center chances are you will need to rereview your encryption strategy. Cloud providers are aware of this and to make your life easier they have released some features to assist you, for example here are few great encryption features from Azure: 

• Encryption At Rest (Azure SQL, Azure Storage ) 
• Always Encrypted for Azure SQL Database and SQL Server
• Row Level Security for Azure SQL Database and SQL Server

Once again, you need to rereview your requirements and think about what will work for you and your organisation. You don’t want to encrypt everything using Always Encrypted because you will not be able to do partial searches and your application’s performance will be impacted.

Please be aware that Always Encrypted feature will most likely not prevent SQL Injection attack, and you might not be able to use some of the new features from Microsoft if you use this technology, for example Temporal Tables, they currently don’t support Always Encrypted.

11. Key Store

If you are using a key store, you will need to update it to support multi tenancy.

12. Fault tolerance

There is a lot of noise out there about fault tolerance on the Cloud, you need to be aware 

of these three things: 

• Integration error handling 
• Retry strategies 
• Circuit Breaker

Integration Error Handling

I throughly recommend that you do integration error handling failure analysis. 

If cache goes down, what happens? If Azure Service Bus goes down what will happen? It’s important that you design your app with failure in mind. So if cache goes down your app still works, it will just run a bit slower. If Azure Service Bus goes down, your app will continue to work but messages will not get processed.

Retry Strategy (Optional)

Retry strategy can help you with transient failure. In the early cloud days I’ve been told that these errors were common due to the infrastructure instability and noisy neighbour issues. These errors are a lot less common these days, however they still do happen. Good news is that Azure Storage SDK comes with retry strategy by default and you can use ReliableSqlConnection instead of SqlConnection to get the retry strategy when you connect to Azure SQL.

A custom retry policy was used to implement exponential backoff while also logging each 

retry attempt. During the upload of nearly a million files averaging four mb in size around 1.8% of files required at least one try - Cloud Architecture Patterns - Using Microsoft Azure

You don’t have to implement this when you migrate, you can accept that some of the users might get a transient failure. As long as you have integration error handling in place and you keep your app transactionally consistent your users can just retry manually.

Circuit breaker (Optional)

Martin Fowler Circuit Breaker

13. Say goodbye to two phase commit

On the PaaS Cloud you can’t have two phase commmit, it’s a concious CAP theorem trade off. This means no two phase commit for .NET or Java or whatever. This is not an Azure limitation.

Please take a second and think about how this will impact your application.

14. Point in time backups

Azure SQL comes with point in time backup, however Azure Storage doesn’t have point in time backup. So you either need to implement your own or use tool such as CherrySafe.

15. Accept Technical Debt

During the migration you will find more and more things that you would like to improve. 

However, the business reality is going to be that you will need to accept some technical debt. This is a good thing, you need to get product out there, see how it behaves and focus on the areas that actually need improvement. Make a list of things that you want to improve, get the most important things done before you ship, and during the subsequent releases just remove as much technical debt as you can.

What you think needs rewriting ASAP might change, as you convert your application you will find bigger more pressing things that you must improve, so don’t focus and commit to fixing the trivial things prematurely.

16. New Persistence Options (Optional)

As you migrate you should seriously think about persistence. Ask yourself the following questions: 

• Do you really need to store everything in the relational database? 
• Can you store binary files in the Azure Storage? 
• What about JSON documents? Why not store them the DocumentDB?

This will increase migration complexity, so you might want to delay this and do this in the future. That’s understandable. But just so you know there are lots of reasons why you should do this.

Azure SQL is not cheap and DTUs are limited. Less you talk to the Azure SQL the better.  Streaming binary files to and from Azure SQL is expensive, it takes up storage space and DTUs, so why do it? Azure Storage is very cheap and it comes with Read-Access Geo Redundant Storage, so incase of a DR you can failover.

17. Monolith, Service-based or Microservice Architecture (Optional)

If you are migrating I will say this, don’t attempt at the same time to migrate over to Microservices. Do one thing, just get your application to the Cloud, enable multi tenancy, give business some value. After you have achieved your conversion milestone I would consider breaking your app down into smaller deployment modules.

Why? Because it’s complex, here are just a few things that you will need to think about: 

• Data Ownership - How do different modules share data? Who owns the actual data? 
• Integration - How are you going to integrate module A and B? 
• API to API communication? 
• Queues? 
• Database Integration? 
• Testing, how are you going to test the integration? Are you going to automate it? 
• Scalability - How are you going to scale different modules? 
• Transactions - In the Cloud there is no two phase commit, how are going to work around this?

18. Infrastructure As Code (Optional)

Now that you are going to the Cloud you need to look after one more thing. Your infrastructure provisioning will now be a config file. Someone will need to maintain this.

If you are using Azure you will define your infrastructure using Azure Resource Manager and PowerShell.

This is optional because you could set up your infrastructure manually (I don’t recommend this).

19. Gradual Release (Optional)

If you deploy your SaaS app and something goes wrong you will not want to take all of your customers down at the same time. This means you should consider having several installations. For example, you can have different URLs for different installations app.yourdomain.com and app2.yourapp.com. So when you deploy to app.yourdomain.com, if all goes well then you can promote your app to app2.yourapp.com. This is optional because you could just deploy out of hours to app.yourapp.com.

20. Deployment Pipeline (Optional)

You will need a deployment pipeline if you want to have confidence in your deployments. Don’t underestimate this step, depending on what services you are going to use and how complex your pipeline is, it can take weeks to set this up. Chances are you will need to change your config files, transform them for different installations, tokenize them. Deploy to staging slots and VIP switch the production, add error handling, establish promotion process, etc.

There are plenty of tools that can help you with this, I’ve been using Visual Studio Team Services to orchestrate builds and deployments.

21. Avoid Vendor Lock In (Optional)

Build your application in a such way that you can migrate with relative ease to another cloud provider. I know that no matter what you do, it will not be an easy journey,  however, if you actually think about it and design with it in mind, it might take 1-3 months to migrate from Azure to AWS and not 1 year. How? When you redesign your application abstract infrastructure components out so there are no direct dependencies on Azure and Azure SDKs.

Creating Custom Key Store Provider for SQL Always Encrypted (Without Key Vault Example PoC)

Recently we had to implement custom key store provider for always encrypted. We wanted it to access our own key store to retrieve the master key and to decrypt the column key.  It was not very clear how this can be achieved. So I've decided to produce a PoC and write an article about it.

Setup your C# application

Step 1:

Make sure your project is set to .NET Framework 4.6.

Step 2:

Implement your own custom store provider by extending  the SqlColumnEncryptionKeyStoreProvider and overriding the two methods:

 public class MyOwnCustomKeyStoreProvider : SqlColumnEncryptionKeyStoreProvider
    {

        string masterKeyThatWillNotBeHardcodedInYourApp = "someMasterKey";
        byte[] saltThatWillNotBeHardcodedInYourApp = UTF8Encoding.UTF8.GetBytes("someSalt");

        //This will constantly get used
        public override byte[] DecryptColumnEncryptionKey(string masterKeyPath, string encryptionAlgorithm, byte[] encryptedColumnEncryptionKey)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                using (RijndaelManaged AES = new RijndaelManaged())
                {
                    AES.KeySize = 256;
                    AES.BlockSize = 128;

                    Rfc2898DeriveBytes keyBytes = new Rfc2898DeriveBytes(
                            masterKeyThatWillNotBeHardcodedInYourApp, 
                            saltThatWillNotBeHardcodedInYourApp, 
                            1000
                       );
                    AES.Key = keyBytes.GetBytes(AES.KeySize / 8);
                    AES.IV = keyBytes.GetBytes(AES.BlockSize / 8);

                    AES.Mode = CipherMode.CBC;

                    using (CryptoStream cs = new CryptoStream(ms, AES.CreateDecryptor(), CryptoStreamMode.Write))
                    {
                        cs.Write(encryptedColumnEncryptionKey, 0, encryptedColumnEncryptionKey.Length);
                        cs.Close();
                    }
                    encryptedColumnEncryptionKey = ms.ToArray();
                }
            }

            return encryptedColumnEncryptionKey;
        }

        //This will never get used by the app, I've used it just to encrypt the column key
        public override byte[] EncryptColumnEncryptionKey(string masterKeyPath, string encryptionAlgorithm, byte[] columnEncryptionKey)
        {
            byte[] encryptedBytes = null;
            using (MemoryStream ms = new MemoryStream())
            {
                using (RijndaelManaged AES = new RijndaelManaged())
                {
                    AES.KeySize = 256;
                    AES.BlockSize = 128;

                    Rfc2898DeriveBytes keyBytes = new Rfc2898DeriveBytes(
                            masterKeyThatWillNotBeHardcodedInYourApp,
                            saltThatWillNotBeHardcodedInYourApp,
                            1000
                       );

                    AES.Key = keyBytes.GetBytes(AES.KeySize / 8);
                    AES.IV = keyBytes.GetBytes(AES.BlockSize / 8);

                    AES.Mode = CipherMode.CBC;

                    using (CryptoStream cs = new CryptoStream(ms, AES.CreateEncryptor(), CryptoStreamMode.Write))
                    {
                        cs.Write(columnEncryptionKey, 0, columnEncryptionKey.Length);
                        cs.Close();
                    }
                    encryptedBytes = ms.ToArray();
                }
            }

            return encryptedBytes;
        }
    }

Step 3:

Register your provider with the SqlConnection:

            //Register your encryption key strategies 
            Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providerStrategies =
                new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();

            providerStrategies.Add("MY_OWN_CUSTOM_KEY_STORE_PROVIDER", new MyOwnCustomKeyStoreProvider());

            SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providerStrategies);

Step 4:

Now, pay attention. Make sure that your connection is configured correctly, I've spent several hours trying to figure out why my setup was not working. It was all because I did not include "Column Encryption Setting=Enabled" in the connection string:

 new SqlConnection("Server=tcp:some.database.windows.net,1433;Database=testing;User ID=testing@testing;Password=Password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;Column Encryption Setting=Enabled")

If you  don't include Column Encryption Setting=Enabled, you will get unhelpful exception like this:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Operand type clash: nvarchar is incompatible with nvarchar(11) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'MO_CEK1', column_encryption_key_database_name = 'sometest')

Incorrect parameter encryption metadata was received from the client. The error occurred during the invocation of the batch and therefore the client can refresh the parameter encryption metadata by calling sp_describe_parameter_encryption and retry.

Setup your database

Step 1:

Define your custom key store provider:

 CREATE COLUMN MASTER KEY [MO_CMKSP] --Stands for My Own Custom Key Store Provider
 WITH ( KEY_STORE_PROVIDER_NAME = 'MY_OWN_CUSTOM_KEY_STORE_PROVIDER', 
 KEY_PATH = 'MyKeyStoreWillNotUseThis')

Step 2:

Define the column encryption key that will get unwrapped by your own custom key store provider. Encrypted value needs to be some random value that gets encrypted by your master key and stored here as a hexadecimal:

 CREATE COLUMN ENCRYPTION KEY [MO_CEK1] -- Stands for My Own Column Encryption Key 1
 WITH VALUES
 (
  COLUMN_MASTER_KEY = [MO_CMKSP],
  ALGORITHM = 'RSA_OAEP',
  ENCRYPTED_VALUE = 0x29128e12266a71dd098bc3223b3bbf293a275b2ec8c13f97515f54dd7d2a54af46f37071e0e16e777d73f4a743ddb991
 )

Step 3:

Encrypt columns by specifying the column encryption key:

 CREATE TABLE [dbo].[Employee](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2
  ENCRYPTED WITH (
   COLUMN_ENCRYPTION_KEY = [MO_CEK1],
   ENCRYPTION_TYPE = Deterministic,
   ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
  ) NOT NULL,
  [Salary][int] 
  ENCRYPTED WITH (
   COLUMN_ENCRYPTION_KEY = [MO_CEK1],
   ENCRYPTION_TYPE = RANDOMIZED,
   ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
  ) NOT NULL,
  PRIMARY KEY CLUSTERED
  (
  [Id] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]

 CREATE TABLE [dbo].[EmployeeExtraInformation](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [EyeColor] [nvarchar](11) NOT NULL,
  [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2
  ENCRYPTED WITH (
   COLUMN_ENCRYPTION_KEY = [MO_CEK1],
   ENCRYPTION_TYPE = Deterministic,
   ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
  ) NOT NULL,
  PRIMARY KEY CLUSTERED
  (
  [Id] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]

PoC Code

Program.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace CustomKeyStoreProvider
{
    class Program
    {
        static void Main(string[] args)
        {
            //Register your encryption key strategies 
            Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providerStrategies =
                new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();

            providerStrategies.Add("MY_OWN_CUSTOM_KEY_STORE_PROVIDER", new MyOwnCustomKeyStoreProvider());


            //Apparently this works transparently with the Hibernate and the Entity Framework!
            SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providerStrategies);

            using (SqlConnection connection = new SqlConnection({Your connection string};Column Encryption Setting=Enabled))
            {
                connection.Open();
                string ssn;
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "INSERT INTO [dbo].[Employee] VALUES (@ssn, @salary)";
                    Random rand = new Random();
                    ssn = string.Format(@"{0:d3}-{1:d2}-{2:d4}", rand.Next(0, 1000), rand.Next(0, 100), rand.Next(0, 10000));
                    command.Parameters.AddWithValue("@ssn", ssn);
                    command.Parameters.AddWithValue("@salary", 18000);
                    command.ExecuteNonQuery();
                }

                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "INSERT INTO [dbo].[EmployeeExtraInformation] (eyecolor, ssn) VALUES (@eyecolor, @ssn)";
                    command.Parameters.AddWithValue("@eyecolor", "blue");
                    command.Parameters.AddWithValue("@ssn", ssn);
                    command.ExecuteNonQuery();
                }

                //Show stored data unencrypted 
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT [id], [ssn], [salary] FROM [dbo].[Employee]";
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if(reader.HasRows)
                        {
                            Console.WriteLine("-- Showing all rows:");
                            while (reader.Read())
                            {
                                Console.WriteLine($"id : {reader["id"]}, ssn : {reader["ssn"]}, salary : {reader["salary"]}");
                            }
                        }
                    }
                }

                //Equals search, this actually works
                using(SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT [id], [ssn], [salary] FROM [dbo].[Employee] WHERE [ssn] = @ssn";
                    command.Parameters.AddWithValue("@ssn", ssn);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            Console.WriteLine($"-- Showing found record for ssn {ssn}:");
                            while (reader.Read())
                            {
                                Console.WriteLine($"id : {reader["id"]}, ssn : {reader["ssn"]}, salary : {reader["salary"]}");
                            }
                        }
                    }
                }

                //Inner Join, this works as well
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = @"SELECT [dbo].[Employee].[salary], [dbo].[Employee].[ssn], [dbo].[EmployeeExtraInformation].[eyecolor] FROM [dbo].[Employee] 
                                                    INNER JOIN [dbo].[EmployeeExtraInformation] ON [dbo].[Employee].[ssn] = [dbo].[EmployeeExtraInformation].[ssn]";

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            Console.WriteLine($"-- Showing all records inner joined:");
                            while (reader.Read())
                            {
                                Console.WriteLine($"eyecolor : {reader["eyecolor"]}, ssn : {reader["ssn"]}, salary : {reader["salary"]}");
                            }
                        }
                    }
                }

                try
                {
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "SELECT [id], [ssn], [salary] FROM [dbo].[Employee] WHERE [ssn] like @ssn";
                        command.Parameters.AddWithValue("@ssn", ssn);

                        command.ExecuteReader();
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("-- As expected, can't search on ssn using like:");
                    Console.WriteLine(ex.Message);
                }

                try
                {
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "SELECT [id], [ssn], [salary] FROM [dbo].[Employee] WHERE [salary] = @salary";
                        command.Parameters.AddWithValue("@salary", 18000);

                        command.ExecuteReader();
                    }
                }
                catch(Exception ex)
                {
                    Console.WriteLine("-- As expected, can't search on salary, it is a randomized field:");
                    Console.WriteLine(ex.Message);
                }

                connection.Close();
            }

            Console.ReadLine(); 
        }
    }
}

MyOwnCustomKeyStoreProvider.cs

using System.Data.SqlClient;
using System.IO;
using System.Security.Cryptography;
using System.Text;

namespace CustomKeyStoreProvider
{
    public class MyOwnCustomKeyStoreProvider : SqlColumnEncryptionKeyStoreProvider
    {

        string masterKeyThatWillNotBeHardcodedInYourApp = "someMasterKey";
        byte[] saltThatWillNotBeHardcodedInYourApp = UTF8Encoding.UTF8.GetBytes("someSalt");

        //This will constantly get used
        public override byte[] DecryptColumnEncryptionKey(string masterKeyPath, string encryptionAlgorithm, byte[] encryptedColumnEncryptionKey)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                using (RijndaelManaged AES = new RijndaelManaged())
                {
                    AES.KeySize = 256;
                    AES.BlockSize = 128;

                    Rfc2898DeriveBytes keyBytes = new Rfc2898DeriveBytes(
                            masterKeyThatWillNotBeHardcodedInYourApp, 
                            saltThatWillNotBeHardcodedInYourApp, 
                            1000
                       );
                    AES.Key = keyBytes.GetBytes(AES.KeySize / 8);
                    AES.IV = keyBytes.GetBytes(AES.BlockSize / 8);

                    AES.Mode = CipherMode.CBC;

                    using (CryptoStream cs = new CryptoStream(ms, AES.CreateDecryptor(), CryptoStreamMode.Write))
                    {
                        cs.Write(encryptedColumnEncryptionKey, 0, encryptedColumnEncryptionKey.Length);
                        cs.Close();
                    }
                    encryptedColumnEncryptionKey = ms.ToArray();
                }
            }

            return encryptedColumnEncryptionKey;
        }

        //This will never get used by the app, I've used it just to encrypt the column key
        public override byte[] EncryptColumnEncryptionKey(string masterKeyPath, string encryptionAlgorithm, byte[] columnEncryptionKey)
        {
            byte[] encryptedBytes = null;
            using (MemoryStream ms = new MemoryStream())
            {
                using (RijndaelManaged AES = new RijndaelManaged())
                {
                    AES.KeySize = 256;
                    AES.BlockSize = 128;

                    Rfc2898DeriveBytes keyBytes = new Rfc2898DeriveBytes(
                            masterKeyThatWillNotBeHardcodedInYourApp,
                            saltThatWillNotBeHardcodedInYourApp,
                            1000
                       );

                    AES.Key = keyBytes.GetBytes(AES.KeySize / 8);
                    AES.IV = keyBytes.GetBytes(AES.BlockSize / 8);

                    AES.Mode = CipherMode.CBC;

                    using (CryptoStream cs = new CryptoStream(ms, AES.CreateEncryptor(), CryptoStreamMode.Write))
                    {
                        cs.Write(columnEncryptionKey, 0, columnEncryptionKey.Length);
                        cs.Close();
                    }
                    encryptedBytes = ms.ToArray();
                }
            }

            return encryptedBytes;
        }
    }
}

Setup.sql

CREATE COLUMN MASTER KEY [MO_CMKSP] --Stands for My Own Custom Key Store Provider
 WITH ( KEY_STORE_PROVIDER_NAME = 'MY_OWN_CUSTOM_KEY_STORE_PROVIDER', 
 KEY_PATH = 'MyKeyStoreWillNotUseThis')

GO 

CREATE COLUMN ENCRYPTION KEY [MO_CEK1] -- Stands for My Own Column Encryption Key 1
 WITH VALUES
 (
  COLUMN_MASTER_KEY = [MO_CMKSP],
  ALGORITHM = 'RSA_OAEP',
  ENCRYPTED_VALUE = 0x29128e12266a71dd098bc3223b3bbf293a275b2ec8c13f97515f54dd7d2a54af46f37071e0e16e777d73f4a743ddb991
 )
GO

CREATE TABLE [dbo].[Employee](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2
  ENCRYPTED WITH (
   COLUMN_ENCRYPTION_KEY = [MO_CEK1],
   ENCRYPTION_TYPE = Deterministic,
   ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
  ) NOT NULL,
  [Salary][int] 
  ENCRYPTED WITH (
   COLUMN_ENCRYPTION_KEY = [MO_CEK1],
   ENCRYPTION_TYPE = RANDOMIZED,
   ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
  ) NOT NULL,
  PRIMARY KEY CLUSTERED
  (
  [Id] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]

 CREATE TABLE [dbo].[EmployeeExtraInformation](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [EyeColor] [nvarchar](11) NOT NULL,
  [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2
  ENCRYPTED WITH (
   COLUMN_ENCRYPTION_KEY = [MO_CEK1],
   ENCRYPTION_TYPE = Deterministic,
   ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
  ) NOT NULL,
  PRIMARY KEY CLUSTERED
  (
  [Id] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]

Useful links:

• Always Encrypted Database Engine
• Creating Custom Azure Key Vault Key Store Provider for Always Encrypted 
• Always Encrypted works transparently with NHibernate and Entity Framework, however there are limitations

*Note: Code in this article is not production ready and is used for prototyping purposes only. If you have suggestions or feedback please do comment. 

CodeProject