The SQL 2016 Contained Database feature provides hosting service providers the opportunity to support a key business scenario:
This document describes the following:
These are the focus of this document.
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.
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.
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 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 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.
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.
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.
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.
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.
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 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.
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.
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.
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.
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.
Contained Database users with passwords cannot use Kerberos Authentication. When possible, use Windows Authentication to take advantage of Windows features such as Kerberos.
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.
If a database is partially contained, SQL Server administrators should periodically audit the capabilities of the users and modules in Contained Databases.
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.
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 | 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. |
Following are the considerations while migrating database to the partially Contained Database model:
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.
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.
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.