SQL Server 2012 Contained Databases

Although SQL Server databases have always had a degree of portability, SQL Server 2012 introduces a powerful new database containment feature that makes databases much more portable. After I explain why this new feature is needed, I'll show you how to work with contained databases. I'll also address some of the pros, cons, and potential gotchas associated with their use.

The Need for Contained Databases

Authentication is the process of making sure that users and applications are who or what they say they are. Prior to SQL Server 2012, authentication could be performed only at the server level and effectively worked in either one of two ways: authentication by Windows (or against the underlying SQL Server host) or authentication by SQL Server. With Windows integrated authentication, SQL Server trusts the underlying host's assessment that users and applications are who or what they claim to be. That's why Windows integrated authentication is sometimes called "trusted auth" -- SQL Server is trusting Windows' assessment that a user or application is authentic. With SQL Server authentication, users and applications are assumed authentic if they provide SQL Server with the correct username and password.

The users and applications that log in to a server are collectively known and managed as entities called logins. After logins are authenticated by either Windows or SQL Server, they're permitted to execute certain tasks or operations within SQL Server. For some logins, permissions might exist at the server level, which means that they're allowed to create databases, manage security or backups, or even take full control of all aspects of a given SQL Server instance if they've been mapped, for example, to the sysadmin server-level role.

Most logins, however, are typically authorized for certain tasks at the database level. This means that they're given permission to access certain databases (while being denied access to others) and are assigned explicit rights (e.g., the ability to read data, write data, or create new objects) at the database level. Conversely, logins might be explicitly denied certain rights. These particular rights are managed as a collection in a construct known as a user. A user is created and managed within each database in which a login has been granted explicit permissions.

Because logins are managed at the server level and users are managed at the database level, SQL Server permissions end up being defined in two or more locations. Besides creating confusion for less-experienced DBAs and many end users, a huge problem can occur if the mapping that "binds" the logins to users becomes lost when a database is copied, moved, or restored on a different server from where the mapping was originally defined.

When a database is restored or attached to its new host, the logins must already be defined on the new host and must exactly match the logins on the original host. If identical logins aren't on the new host, the mappings will be broken. Although the logins can be created after the fact, the mappings between logins and users often remain broken, resulting in what are commonly referred to as orphaned users. To complicate matters further, even in cases in which identical logins might already exist, it's sadly all too common for the mappings between logins and users to become lost during database migrations.

In this regard, having to manage logins and users separately can cause problems when it comes to creating and maintaining high availability and disaster recovery solutions. For starters, the fact that users are defined at the database level and logins are defined at the server level means that databases really aren't as portable as they should be. SQL Server databases have underlying dependencies on their current host server configuration to work correctly. As such, DBAs need to regularly synchronize logins against secondary or failover servers if they want to avoid problems with orphaned users after recovering from certain types of failures or crashes. Moreover, because orphaned users can still occur despite regularly synchronizing logins, DBAs might still have the headache of having to repair large numbers of users after a disaster. To make matters worse, the syntax of the sp_change_users_login stored procedure used to repair orphaned users is rather unintuitive. (See my video that explains how to copy and move SQL Server logins.)

Contained Databases -- A Better Way

In SQL Server 2012, DBAs finally get help dealing with the age-old problem of orphaned users, thanks to the new database containment feature. With containment, SQL Server databases can become free of external dependencies on server-level metadata and settings, including login details.

To facilitate the goal of containment, SQL Server 2012 introduces the notion of boundaries, which are borders that define where a database and its metadata and settings end and where server-level features, metadata, and dependencies begin. In addition to implicitly allowing increased portability, boundaries are also used to help explicitly isolate data, settings, features, and capabilities.

There are three types of containment hinted at in SQL Server 2012 Books Online (BOL):

To achieve increased containment and isolation, SQL Server 2012 introduced a few new features and capabilities that allow the database to take greater control over its own metadata. One such feature is the option to define users at the database level so that they don't have any dependency on server-level logins. This new option provides increased portability, which addresses the problem of orphaned users in high availability and disaster recovery scenarios and offers other benefits.

Some features have also been modified in SQL Server 2012 so that they support contained databases. One such feature is the ALTER DATABASE statement. It includes the new CONTAINMENT argument, which lets you change a database's containment status to partially contained. In addition, the ALTER DATABASE statement includes the new CURRENT argument. When you want to alter the current database, you can simply specify this argument rather than having to specify the database by name.

Getting Started with Contained Databases

In SQL Server 2012, user databases can be easily configured to support partial containment or no containment by means of the ALTER DATABASE statement. To configure partial containment, you set the new CONTAINMENT argument to PARTIAL, as in:

ALTER DATABASE AdventureWorks2012SET CONTAINMENT = PARTIALGO 

You can also use SQL Server Management Studio (SSMS) to configure partial containment. To do so, right-click the database, select Properties, and go to the Options page. In the Containment type drop-down list, select Partial, as Figure 1 shows.

Figure 1: Configuring Partial Containment in SSMS 

After you configure a database for partial containment, you need to configure the server to allow contained database authentication. Otherwise, the underlying host (or SQL Server itself) won't allow these login attempts. To enable contained database authentication, you use code such as:

EXEC sp_configure 'contained database authentication'EXEC sp_configure 'contained database authentication',1GORECONFIGUREGO 

The first line just reports on the current setting. With this server-level setting enabled, SQL Server allows deferment of authentication to the databases themselves, provided that users have been configured with the necessary authentication details.

At this point, you can create "portable" users. For example, the following code defines a portable user (SamplePortableUser) that will use SQL Server authentication to access the AdventureWorks2012 database:

USE AdventureWorks2012GOCREATE USER SamplePortableUserWITH PASSWORD = 'Some secure passphrase goes here.'GOALTER ROLE [db_owner] ADD MEMBER SamplePortableUserGO 

For examples of how to create portable users that log in with Windows integrated authentication, see the CREATE USER (Transact-SQL) web page in SQL Server 2012 BOL.

As Figure 2 shows, you can also use SSMS to create portable users, whether you want to use SQL Server authentication or Windows integrated authentication. To do so, expand the Security node of the target database, right-click the Users node, and select the New User context-menu option.

Figure 2: Creating Portable Users at the Database Level in SSMS 

The prospect of databases creating portable users that can log in to a given host raises a number of potential security implications, which is why SQL Server 2012 BOL has dedicated an entire section, "Security Best Practices with Contained Databases," to that topic. It's worth noting that once a portable user gains access to a contained database through contained database authentication, that user also ends up gaining guest access to all other databases on the host system -- a potential issue to be aware of (and something that will hopefully be addressed when FULL containment becomes available).

It's also worth noting that conventional users can also be converted into standalone database-level portable users through the use of a new special stored procedure: sp_migrate_user_to_contained. It provides an argument that you can use to specify whether or not to disable the server-level login -- something you'll typically want to do as a best practice to avoid ugly login problems that can occur when duplicate logins and users overlap each other.

At this point, if you copy or deploy this sample database on a new host, you'll be able log in to this database as the SamplePortableUser against the new host using a standard .NET (or similar) connection string without needing to copy any logins over to the server itself. (This is assuming that you configured the server to allow contained database authentication and that you enabled SQL Server authentication in your test environment.)

However, if you test this out using SSMS, you might wonder why you aren't able to connect to your contained database on the other server when specifying the correct login, as shown in Figure 3. This error message is caused by the fact that contained database authentication can only occur when the contained database is specified in the connection details being used to connect to the server, as shown in Figure 4. Without a specified database, SQL Server would have to traverse each database on the server to determine whether the login being used was a contained user. (For more information about this, see Don Kiely's article "SQL Server 2012 Keeps Your Data a Little More Secure," May 2012.)

 
Figure 3: Resulting Error Message when the Contained Database Is not Specified in the Connection Deta

Figure 4: Specifying the Contained Database in the Connection Details

Contained Database Limitations

Because contained databases are explicitly designed to use boundaries that help decouple SQL Server databases from the underlying server, it should come as no surprise that there are some limitations when using them. According to SQL Server 2012 BOL, partially contained databases can't use:

Ironically, temporary stored procedures are currently supported in contained databases, but their use is strongly discouraged because they breach containment. According to SQL Server 2012 BOL, temporary stored procedures probably won't be supported in future versions.

There are also a few situations in which "extra database" interactions might be supported but execute with slightly different behaviors than what might be typically expected. For example, in "Contained Database Collations," SQL Server 2012 BOL provides a comprehensive overview of the kinds of behavioral changes that can be expected when contained databases interact with the tempdb database. The behavioral changes are due to the fact that noncontained databases use the tempdb database's collation settings by default, whereas contained databases use their own collation settings by default when interacting with the tempdb database. It's important to note that although contained databases can interact with the tempdb database to create temporary tables, the use of explicitly named constraints defined against those temporary tables isn't allowed once databases become contained.

Given that the entire purpose of database containment is to facilitate isolation and portability, it stands to reason that cross-database queries, joins, and other interactions (such as calls into msdb to start a SQL Server Agent job or send an email message) would limit a database's portability. Therefore, the use of any feature that crosses the isolation boundary for a contained database is going to cause portability problems.

Happily, though, the SQL Server team has done a fantastic job of making these and other kinds of potential containment concerns easy to identify by means of a powerful new dynamic management view (DMV) named sys.dm_db_uncontained_entities. This DMV provides a plethora of information about noncontained entities within any contained or noncontained SQL Server 2012 database.

For example, if you were to create a cross-database query by means of the stored procedure in Listing 1 (below), then query sys.dm_db_uncontained_entities using the code in Listing 2 (below), you'd end up with the results shown in Figure 5. As you can see, these results include my test stored procedure along with a few other entities that aren't fully contained. Note how thorough sys.dm_db_uncontainted_entities is -- it's showing full-text indexes as noncontained entities because they impose minor additional dependencies on the underlying server in the sense that you might want to know about these host-related features if you were trying to deploy this database to a host where, for example, Full-Text Indexing (FTI) wasn't available.

Figure 5: Examining the Results from Querying sys.dm_db_uncontained_entities 

Consequently, if you intend to use contained databases, you'll likely want to become very familiar with the new sys.dm_db_uncontained_entities DMV because it can help you identify uncontained entities that might jeopardize portability. You might also want to use sys.databases to identify databases that are configured to enable containment. Sys.databases has been updated to show this information at the server level as well, through the new "containment" and "containment_desc" columns.

The Allure of Portability

By and large, the biggest benefit of contained databases is that they help address long-standing problems with logins and users when it comes to addressing failover in high availability and disaster recovery scenarios. Accordingly, it's no surprise that contained databases were released at the same time as AlwaysOn Availability Groups -- a SQL Server 2012 Enterprise feature that lets DBAs build high availability and disaster recovery solutions with a single set of tools. However, as well as database containment naturally complements AlwaysOn Availability Groups, there's no reason why you can't use contained databases in high availability and disaster recovery solutions that don't require the Enterprise edition, such as log shipping and database mirroring.

A bigger allure, though, is that database containment helps paint the picture of a brighter tomorrow, where, in a future version of SQL Server, databases might become portable enough to:

Similarly, it's difficult not to think of database containment in relation to SQL Azure because Azure databases are essentially specialized instances of SQL Server databases designed to run in a state where they're isolated from the host. So, in both cases, it's difficult not to wonder what database containment might lead to in the future.

But the reality is that, right now and right out of the box, SQL Server 2012's support for database containment is simply one of the best things to happen to high availability and disaster recovery in a long time. It can greatly simplify environmental and failover concerns.

Listing 1: Example of a Cross-Database Query that Violates Containment

CREATE PROC dbo.CrossDatabaseBorderViolationExampleAS  SET NOCOUNT ON  SELECT COUNT(*) FROM ReportServer.sys.all_views  RETURN 0GO 

Listing 2: Code to Query the New DMV for Uncontained Entities

SELECT  class_desc,  CASE classWHEN 1 THEN OBJECT_NAME(major_id)WHEN 4 THEN 'Login: ' +  (SELECT name from sys.server_principals  WHERE principal_id = u.major_id)    COLLATE SQL_Latin1_General_CP1_CI_ASWHEN 7 THEN 'FTI:' + OBJECT_NAME(major_id)-- etc.  END [Detail],  major_id,  statement_line_number,  statement_typeFROM  sys.dm_db_uncontained_entities u