Setting Connection Strings In .Net Core Web API With Multiple Tenant SQL DB

This blog explains how to retrieve different connection strings on every HTTP request based on the header value and use it to connect the SQL database with EF Core contexts to perform the required operation.

I just wanted to share with you all about an idea I came across while working with multiple tenant DB based web applications where each consumer/subscriber of the application will have a separate database to maintain their data. We all know that it's pretty easy to set up a connection string for your Web API and use the same value every time from the appSettings.json file whenever your application gets a request to process.

 

But it will not help in this case where you would not have any idea about the connection string of the database. You are going to connect at the time of coding or compilation. Now, we have got to know that it is a challenging task to achieve but after reading this blog, I am sure you won't feel the same way.

 

Let's get to the point and get our hands dirty with some code!!

1. Create your .NET Core 2.1/2.2 Web API with a controller and an action method in it.
   

   [Route("api/[controller]")]
   [ApiController]
   public class BlogsController: ControllerBase {
       // GET api/values
       [HttpGet]
       public ActionResult < string > Get() {
           return "value1";
       }
       // GET api/values/5
       [HttpGet("{id}")]
       public ActionResult < string > Get(int id) {
           return "value";
       }
   }

2. Make sure you have created a DbContext class using Entity Framework Core (both code first/ Db first approach will work).
   

   public partial class BlogDbContext: DbContext {
       public BlogDbContext() {}
       public BlogDbContext(DbContextOptions < BlogDbContext > options): base(options) {}
       public BlogDbContext(string connectionString): base(GetOptions(connectionString)) {}
   }

3. Create a repository class to perform the basic CRUD operations with DbContext class (this is just an additional step to create a well designed application and it can be skipped when your application has other designs).
   

   public class BlogRepository: IBlogRepository {
       private BlogDbContext context;
       public BlogRepository(BlogDbContext dbContext) {
           context = dbContext;
       }
       public void CreateBlog(List < Blog > blogs) {
           context.Blogs.Add(blogs.FirstOrDefault());
       }
   }

4. Use .NET Core dependency injection to get the repository class instance on your API Controller action method. You will be able to call CRUD operations from your controller.
   

   public class BlogsController: ControllerBase {
       private IBlogRepository _blogRepository;
       public BlogsController(IBlogRepository blogRepository) {
           _blogRepository = blogRepository;
       }
       // GET api/values
       [HttpGet]
       public ActionResult < IEnumerable < Blog >> Get() {
           return _blogRepository.GetBlog();
       }
   }

5. Add a connection string property to the appSettings.json file and refer to your DbContext class inside Startup.cs file along with connection string. You will be all set to call your API to connect to a single SQL database for now.
   

   "ConnectionStrings": {
      "MasterDatabase": "Server=.\\SQLEXPRESS;Database=Master;Trusted_Connection=True;"
   }

   
   
6. Until now, we just followed the regular steps to create a Web API with basic CRUD operation with SQL database. Now, we will make this API into a multi-tenant database API. For that, we need a master database where all of our subscriber's DB connection strings are stored or any other way of the source which holds their value in any form. (In my case, I had a master database where all subscriber DB connection strings were stored along with their username and password).
   

   public void ConfigureServices(IServiceCollection services) {
       services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
       services.AddDbContext < MasterDbContext > (options => options.UseSqlServer(Configuration.GetConnectionString("MasterDatabase")), ServiceLifetime.Singleton);
       services.AddDbContext < BlogDbContext > ();
   }

7. Go to your DbContext class, add a static property, and name it as ConnectionString. Then, add the following code into OnConfiguring() method as shown below.
   

   public static string ConnectionString {
       get;
       set;
   }
   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
       //if (!optionsBuilder.IsConfigured)
       //{
       //#warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
       if (!string.IsNullOrEmpty(ConnectionString)) optionsBuilder.UseSqlServer(ConnectionString);
       // }
   }

8. Now, create a middleware (Don't panic. Just a normal .cs file) and add the following code into it. Middleware are the layers in the Web API which will be executed in the order they were configured in the startup file. So every time a request comes into the API, it will pass through this middleware and we can decide what these middlewares are going to do.
   

   public class AuthenticationMiddleware {
       private readonly RequestDelegate _next;
       private MasterDbContext _masterContext;
       public AuthenticationMiddleware(RequestDelegate next, MasterDbContext masterDevContext) {
           _next = next;
           _masterContext = masterDevContext;
       }
       public async Task Invoke(HttpContext context, BlogDbContext blogDBContext) {
           try {
               string authHeader = context.Request.Headers["Authorization"];
               if (authHeader != null && authHeader.StartsWith("Basic")) {
                   //Extract credentials
                   string encodedUsernamePassword = authHeader.Substring("Basic ".Length).Trim();
                   Encoding encoding = Encoding.GetEncoding("iso-8859-1");
                   var usernamePassword = encoding.GetString(Convert.FromBase64String(encodedUsernamePassword));
                   int seperatorIndex = usernamePassword.IndexOf(':');
                   var username = usernamePassword.Substring(0, 9);
                   var password = usernamePassword.Substring(seperatorIndex + 1);
                   if (true) //check if your credentials are valid
                   {
                       BlogDbContext.ConnectionString = ""; //_masterContext.Retrive Your subscriber connection string here
                       if (string.IsNullOrEmpty(BlogDbContext.ConnectionString)) {
                           //no authorization header
                           context.Response.StatusCode = 401; //Unauthorized
                           return;
                       }
                       await _next.Invoke(context);
                   } else {
                       context.Response.StatusCode = 401; //Unauthorized
                       return;
                   }
               } else {
                   // no authorization header
                   context.Response.StatusCode = 401; //Unauthorized
                   return;
               }
           } catch (Exception e) {
               // no authorization header
               context.Response.StatusCode = 400;
               return;
           }
       }
   }

9. Inside middleware, we are getting the userId & password (in encrypted form) through HTTP request headers from the client request. Using these credentials, we need to query the master database and retrieve the respective client's database connection string. Once the connection string is retrieved, set it to the DbContext static ConnectionString property.
   

   if (true) //check if your credentials are valid
   {
       BlogDbContext.ConnectionString = ""; //_masterContext.Retrive Your subscriber connection string here
       if (string.IsNullOrEmpty(BlogDbContext.ConnectionString)) {
           //no authorization header
           context.Response.StatusCode = 401; //Unauthorized
           return;
       }
       await _next.Invoke(context);
   } else {
       context.Response.StatusCode = 401; //Unauthorized
       return;
   }

10. If you do not receive a valid userId and password, you can easily redirect the bad response code to the client. 
11. Finally, you can configure your middleware in the startup.cs file under the Configure method.
    

    public void Configure(IApplicationBuilder app, IHostingEnvironment env) {
        if (env.IsDevelopment()) {
            app.UseDeveloperExceptionPage();
        } else {
            // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
            app.UseHsts();
        }
        app.UseHttpsRedirection();
        app.UseMiddleware < AuthenticationMiddleware > ();
        app.UseMvc();
    }

12. Now, whenever there is an incoming HTTP request, it will go first to middleware, get the dbConnection string, and then route it to the appropriate controller.
    
    
13. That's it. We are done with the implementation of setting the connection strings for every request in .NET Core API with the multi-tenant database.

Thanks for reading! Your feedback is most welcomed. Please notify me with the reason if you think this is not the right way of implementation.