Database per Tenant

Multi-tenant web applications can be constructed using one of the two methods:

• Tenants co-exist in the same database. Data is segregated based on the user identity.
• Each tenant has a private database.

Code On Time web applications offer excellent support for multi-tenant data segregation.

Let’s discuss how to implement a multi-tenant application with private databases.

Internet users arrive to your web application protected with ASP.NET Membership. If the user is not authenticated then there is no way to know, which private database is the final destination for this user. Therefore you either need to implement a single database of user accounts (for example an ASP.NET Membership database) or rely on the elements of the user name (for example the domain portion of the email address) to authenticate the user.

The second solution is more complex when it comes to implementing the user authentication. We will consider the first scenario and assume that the main database of the application has ASP.NET Membership installed already. Follow instructions at http://codeontime.com/learn/sample-applications/northwind to create an application with the membership feature.

The main application database will have the application tables and views. We will call it a “master” database. You will develop your application using the master database. Create additional databases with the same schema but do not include the membership infrastructure in them.

Private Database Derived From DNS Records

Deploy your application. Create CNAME records in the DNS configuration of your Internet domain for each client that must have a private database. Point the CNAME record of each client to the name of your web application. Instruct your clients to sign in the application using their dedicated domain name defined in the CNAME record.

For example, if your main application name is myapp.contoso.com then a client with a dedicate database can access your application as clientname.contoso.com. Also make sure that the name of the master database is myapp.constoso.com and the private database names for the clients use the format clientname.contoso.com.

Unauthenticated users will be authenticated against the master database membership. If you do nothing else then the authenticated users will see the contents of the master database while navigating through the application pages.

Implement the following class in your application to redirect authenticated users to the private database.

C#:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
namespace MyCompany.Data
{
    public partial class ConnectionStringSettingsFactory
    {
        protected override ConnectionStringSettings CreateSettings(string connectionStringName)
        {
            ConnectionStringSettings settings = base.CreateSettings(connectionStringName);
            // if the user is not authenticated then use the default database
            if (HttpContext.Current.User.Identity.IsAuthenticated &&
                HttpContext.Current.Request.Url.Host.Contains("contoso.com"))
            {
                SqlConnectionStringBuilder csb = 
                    new SqlConnectionStringBuilder(settings.ConnectionString);
                csb.InitialCatalog = HttpContext.Current.Request.Url.Host;
                settings = new ConnectionStringSettings(null, csb.ToString(), 
                    settings.ProviderName);
            }
            return settings;
        }
    }
}

Visual Basic:

Imports Microsoft.VisualBasic
Imports MyCompany.Data
Imports System.Data.SqlClient

Namespace MyCompany.Data
    Partial Public Class ConnectionStringSettingsFactory
        Protected Overrides Function CreateSettings(
            connectionStringName As String) As System.Configuration.ConnectionStringSettings
            Dim settings As ConnectionStringSettings = 
                MyBase.CreateSettings(connectionStringName)
            If (HttpContext.Current.User.Identity.IsAuthenticated AndAlso
                HttpContext.Current.Request.Url.Host.Contains(".contoso.com")) Then
                Dim csb As SqlConnectionStringBuilder =
                    New SqlConnectionStringBuilder(settings.ConnectionString)
                csb.InitialCatalog = HttpContext.Current.Request.Url.Host
                settings = New ConnectionStringSettings(Nothing, csb.ToString(), 
                    settings.ProviderName)                
            End If
            Return settings
        End Function
    End Class
End Namespace

This code inspects the name of the host specified in the URL of the current request. If the authenticated user is trying to access the production deployment and “*.contoso.com” is detected in the host name then the code will create a database-specific connection string and change the database name to the name of the host.

Our example uses Microsoft SQL Server database. That is why we are creating SqlConnectionStringBuilder class instance. Use the class that matches your back-end database. The name of the SQL Server database is specified in InitialiCatalog property. Implementations of connection string builder for other database engines may use a different property for the same purpose.

If your clients do have a web presence then you can instruct them to create their private CNAME Records that point to your application. Make sure to use the client’s CNAME Record as the name of the private database instance. Your clients will be able to access your web application as myapp.clientdomain.com where “clientdomain” is the client’s own domain name.

Private Database Derived From User identity

You can also implement a table in the master database that will associate the user accounts with the private databases available in the app. This table will effectively play the role of the DNS for your own application. Users will access the application via the same URL.

create table UserDatabases
(
    UserName varchar(50),
    DatabaseName varchar(50),
    primary key (UserName, DatabaseName)
)

The following code will lookup the UserDatabases table for authenticated users and adjust the connection string accordingly.

C#:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
namespace MyCompany.Data
{
    public partial class ConnectionStringSettingsFactory
    {
        protected override ConnectionStringSettings CreateSettings(string connectionStringName)
        {
            ConnectionStringSettings settings = base.CreateSettings(connectionStringName);
            // if the user is not authenticated then use the default database
            if (HttpContext.Current.User.Identity.IsAuthenticated)
            {
                SqlConnectionStringBuilder csb =
                    new SqlConnectionStringBuilder(settings.ConnectionString);
                using (SqlConnection connection = new SqlConnection(settings.ConnectionString))
                {
                    connection.Open();
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText =
                        "select DatabaseName from UserDatabases where UserName=@UserName";
                    SqlParameter p = command.CreateParameter();
                    command.Parameters.Add(p);
                    p.ParameterName = "@UserName";
                    p.Value = HttpContext.Current.User.Identity.Name;
                    csb.InitialCatalog = Convert.ToString(command.ExecuteScalar());
                }
                csb.InitialCatalog = HttpContext.Current.Request.Url.Host;
                settings = new ConnectionStringSettings(null, csb.ToString(), 
                    settings.ProviderName);
            }
            return settings;
        }
    }
}

Visual Basic:

Imports Microsoft.VisualBasic
Imports MyCompany.Data
Imports System.Data.SqlClient

Namespace MyCompany.Data
    Partial Public Class ConnectionStringSettingsFactory
        Protected Overrides Function CreateSettings(
            connectionStringName As String) As System.Configuration.ConnectionStringSettings
            Dim settings As ConnectionStringSettings =
                MyBase.CreateSettings(connectionStringName)
            If (HttpContext.Current.User.Identity.IsAuthenticated) Then
                Dim csb As SqlConnectionStringBuilder =
                    New SqlConnectionStringBuilder(settings.ConnectionString)
                Using connection As SqlConnection =
                        New SqlConnection(settings.ConnectionString)
                    connection.Open()
                    Dim command As SqlCommand = connection.CreateCommand()
                    command.CommandText =
                        "select DatabaseName from UserDatabases where UserName=@UserName"
                    Dim p As SqlParameter = command.CreateParameter()
                    p.ParameterName = "@UserName"
                    p.Value = HttpContext.Current.User.Identity.Name
                    csb.InitialCatalog = Convert.ToString(command.ExecuteNonQuery())
                End Using
                csb.InitialCatalog = HttpContext.Current.Request.Url.Host
                settings = New ConnectionStringSettings(Nothing, csb.ToString(),
                    settings.ProviderName)
            End If
            Return settings
        End Function
    End Class
End Namespace

Notice that we are using the native ADO.NET classes that are specific to SQL Server: SqlConnection and SqlCommand. Code On Time web applications can take advantage of SqlText class that wraps the ADO.NET components in a compact database-independent implementation. The class is the component of the framework of the generated web application and takes advantage of ConnectionStringSettingsFactory. We have to use the native ADO.NET classes to prevent the re-entrance in the ConnectionStringSettingsFactory. Adjust the sample with the native ADO.NET classes that match your database backend if you are not programming with Microsoft SQL Server.