SQL Server 2016 Reference Architecture for Contained Databases

Solution Overview

The SQL 2016 Contained Database feature provides hosting service providers the opportunity to support a key business scenario:

  • Provide flexibility to service providers to migrate consumer's databases in a multi-tenant environment between different server instances be it on premise or on cloud seamlessly along with server level metadata, settings and security logins.

This document describes the following:

  • Contained Database Migration Scenarios
  • Users
  • Security
  • High Availability
  • Backup and Restore

These are the focus of this document.

Intended Audience

The audience for concepts explained in this document are the Infrastructure Consultants, Architects and Database Administrators of hosting service providers. Hosting service providers who are considering utilizing their in-house hardware and software investments towards the opportunity to increase bottom line revenue. Along with in-house hardware Hosting service providers can also utilize the Microsoft Hybrid Cloud Platform in incremental stages to offer ancillary services around Contained Database to benefit their consumers.

Architectural Overview

The Reference Architecture gives an overview of how Contained Database feature fits in the overall solution architecture of a service provider. At a high level we can visualize this as three layered architecture which includes Infrastructure, Platform and Application. Each layer has its own importance and association.

Infrastructure

The components within infrastructure layer represents the technical capabilities of service provider which are required to host on-premise platform, software and applications. The infrastructure layer of a service provider facilitates PaaS and IaaS services to consumers to host Contained Databases on virtualized environment as well as in the form of a dedicated physical hardware. The network component of this layer represent the physical network switches, routers, firewalls etc. and is used to establish two-way communication between infrastructure, software, platform and application.

Platform

Platform layer works in conjunction with infrastructure and application to provide database service, this offers hosting of Contained Databases in a multi-tenant environment on both on premise as well as Cloud. Consumers' databases get hosted on this platform to isolate their database from the sql server instance based on consumers' requirements. Enabling and configuring Contained Database is easy, one key thing which we have to be keep in consideration is maintaining database settings in the database itself instead of master database.

Application

Application layer works in conjunction with infrastructure and platform and provide data to end users for their analysis. There could be a variety of applications which might want to gain access to data from a Contained Database and there are certain considerations one has to keep in view when connecting to Contained Databases.

Desktop

Accessing a Contained Database from a desktop application is possible and there are no special changes or considerations one has to make. It is seamless to access Contained Database from a desktop because under the cover everything this is taken care at the database platform side and from an application perspective it is as good as connecting to any standard SQL database. This means be it a .NET thick client or a Java or any other application as long as they can connect to SQL databases using correct connection string they would be able to leverage Contained Database features.

Mobile

Accessing a Contained Database from mobile devices such as phones and tablets is seamless and transparent. Mobile apps can connect to SQL instance and leverage Contained Database feature. This holds good for different mobile platforms like iOS, Android and Windows.

A Mobile device such as phone and tablet can connect to a Contained Database using APIs or a direct mode in either cases from a development point of view there is no change as Contained Database works at server side.

Web

Accessing a Contained Database from Web apps is easy and seamless just like accessing any other database in SQL Server. Web application developed on variety of platform like .NET as well as third party platform like Java, Python, PHP etc., can leverage Contained Database seamlessly because Contained Database feature works at server side, this means web developers from any platform need not to worry about any changes into their SQL Server database access method. Same hold true for web based reporting applications, these can connect Contained Databases by providing proper connecting string with the correct Contained Database name.

Contained Database Components

At technology level, Contained Database is integrated with the SQL Server relational engine, and can be accessed transparently using the same interfaces.

A Contained Database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2016 helps user to isolate their database from the instance in 4 ways.

  • Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)
  • All metadata are defined using the same collation.
  • User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
  • The SQL Server environment (DMV's, XEvents, etc.) reports and can act upon containment information.

Architecture Design & Primary Scenarios

In a multi-tenant environment there are multiple databases running on a single SQL Server instance and it might be possible that different databases belong to different consumers' and have their own set of users and security requirements. As a service provider it becomes a challenging task to maintain different consumers' database on a multi-tenant environment. Contained Database feature helps service providers to isolate and manage different consumer databases independently in a multi-tenant environment, it helps isolate data, meta-data, users and security within the database and makes it kind of independent of server instance. This brings a lot of flexibility to service providers to move consumers' database in multi-tenant environment from one environment to another environment without worrying about any breaks in terms of users, security and data access.

Hosting Service Providers

Hosting service providers can leverage the innovative solutions discussed in this document which explains the Contained Database scenario of migrating a database free of external dependencies, server level metadata, settings and security logins. With a combination of hybrid architecture, a service provider can not only offer to run these mission critical workloads in their multi-tenant environment but they can also offer enterprise grade security and migration of these databases from one environment to another environment with minimal changes.

Multi-tenant Contained Database Scenario

Contained Database and Users

The most important security concern on a multi-tenant SQL Server is to keep tenant users confined to their own database. A tenant user should not be able to see data in any other database, including system databases Master and MSDB. You set these boundaries through partially Contained Databases and contained users.

A Contained Database is isolated from all other databases. All user login information and metadata are stored in the database.

To enable a Contained Database, a server configuration option must first be set to allow Contained Databases on the SQL Server. The Containment property can then be set on the database. Contained users have their credentials contained within a single database.

Security

There are security concerns in Contained Databases if the user gains access to the database as a Windows principle instead of a contained user. DBAs need to take care that they create a contained user when that user is not intended to have permissions outside the Contained Database. See Security Best Practices with Contained Databases.

Threats Related to User

Users in a Contained Database that have the ALTER ANY USER permission, such as members of the db_owner and db_securityadmin fixed database roles, can grant access to the database without the knowledge or permission or the SQL Server administrator. Granting users access to a Contained Database increases the potential attack surface area against the whole SQL Server instance. Administrators should understand this delegation of access control, and be very careful about granting users in the Contained Database the ALTER ANY USER permission. All database owners have the ALTER ANY USER permission. SQL Server administrators should periodically audit the users in a Contained Database.

Password Policies

Passwords in a database can be required to be strong passwords, but cannot be protected by robust password policies. Use Windows Authentication whenever possible to take advantage of the more extensive password policies available from Windows.

Kerberos Authentication

Contained Database users with passwords cannot use Kerberos Authentication. When possible, use Windows Authentication to take advantage of Windows features such as Kerberos.

Offline Dictionary attack

The password hashes for Contained Database users with passwords are stored in the Contained Database. Anyone with access to the database files could perform a dictionary attack against the Contained Database users with passwords on an unaudited system. To mitigate this threat, restrict access to the database files, or only permit connections to Contained Databases by using Windows Authentication.

Escaping a Contained Database

If a database is partially contained, SQL Server administrators should periodically audit the capabilities of the users and modules in Contained Databases.

Denial of Service Through AUTO_CLOSE

Do not configure Contained Databases to auto close. If closed, opening the database to authenticate a user consumes additional resources and could contribute to a denial of service attack.

Migrate to a Partial Contained Database

Service Providers can leverage the Contained Database feature to migrate existing database on-premise or on Cloud without any hassle.

The Contained Database considers features with regard to containment. Any user-defined entity that relies only on functions that reside in the database is considered fully contained. Any user-defined entity that relies on functions that reside outside the database is considered uncontained.

A partially Contained Database is a Contained Database that can allow some features that cross the database boundary. SQL Server includes the ability to determine when the containment boundary is crossed.

Contained types :

Contained
Type

Description

NONE

By default, each database has its mode set as NONE. This means there is no Contained Database feature being used.

PARTIAL

Partially Contained Databases provides some isolation from the instance of SQL Server but not full containment.

Considerations while Migrating Partially Contained DB

Following are the considerations while migrating database to the partially Contained Database model:

  1. User should understand the partially Contained Database model.
  2. User should understand risks that are unique to partially Contained Databases.
  3. Contained Databases do not support replication, change data capture, or change tracking. Confirm the database does not use these features.
  4. Review the list of database features that are modified for partially Contained Databases.
  5. Query sys.dm_db_uncontained_entities to find uncontained objects or features in the database.
  6. Monitor the database_uncontained_usage XEvent to see when uncontained features are used.

High Availability

By reducing the ties to the instance of SQL Server, partially Contained Databases can be useful during failover when you use AlwaysOn Availability Groups.

Creating contained users enables the user to connect directly to the Contained Database. This is a very significant feature in high availability and disaster recovery scenarios such as in an AlwaysOn solution. If the users are contained users, in case of failover, people would be able to connect to the secondary without creating logins on the instance hosting the secondary. This provides an immediate benefit.

Back-up and Restore

Contained Database are backed up as part of regular database backups so you don't need to do anything special.

However, in order to restore a Contained Database into a different instance of sql server, the sp_configure value 'Contained Database authentication' must be set to 1.

Summary

In this document we studied how Contained Database are good from a service provider perspective and what key measures should be taken to ensure best usage of Contained Database which is free of external dependencies, server level metadata, settings and security logins. Also, Contained Database is highly available with Always ON feature in case of failover. While restoring the Contained Database, "Enable Contained Databases" property must be set to True.