Contained Database 101

Although it looks like an individual entity, a database in SQL Server is tightly integrated with the SQL Server instance. Every time we move an user database from one instance to another, a series of steps follow, which includes transfers of logins, migration of jobs, DB mail, endpoints, CLR etc.

If we look at the dependencies, a user database depends on all system databases and/or other user databases and the SQL Server instance. Most of the time, when we create a database, we normally leave the collation setting to server default. The server collation setting, logins, etc. are stored in the master database, Jobs, DB Mail, etc. goes to msdb, sorting happens in tempdb, and the model database provides the initial template for every user database. Apart from this, a user database, may depends on the other user databases. As mentioned above, a user database is tightly integrate with the SQL server instance and can not move as a single entity. Contained database, a feature offered in SQL Server 2012, can help us reducing these additional steps, while moving the database across environments.

Contained Database

A Contained Database is a database, which is isolated from other databases and from the instance of SQL Server that hosts the database.

In case of a contained database, all the configuration and metadata settings are stored within the database. With the release of SQL Server 2012, Partial Contained Database is supported.

Partial Containment

This feature is off by default. If you create a database, by default, the containment type of the database is set as NONE. Here is the definition from Books Online:

A partial contained database is a contained database, which allows some features to cross the database boundary.

SQL Server 2012 offers the following features in Partial Contained Database.

  • Authentication @ Contained database level
  • Collation Setting @ Contained database level

However, for features like SQL job, DB Mail, CLR, we still have dependencies on the SQL Server instance, that’s why Partial Containment!

How should I Configure?

Before we create a contained database, we need to ConfigureContainedDBconfigure the SQL Server instance to enable Partial Containment. Well, we can do it, in one of the two ways. We can set the option from the GUI using SSMS as shown here, Connect to the instance using SSMS, Right-click on the instance, go to Properties, click on the Advanced tab. We can set the Enable Contained Databases setting to true. Alternatively, we can use the following system stored procedure to configure the same.

/* Enable Contained Database Authentication for the SQL Server Instance */

use [master];
exec sp_configure 'contained database authentication', 1;
reconfigure with override;

Now that, we have configured the SQL Server instance, let’s create a contained database.

Create a Contained Database

We can create a contained database from the GUI, where we have to set the Containment Type to Partial in the database option page, or, we can use the following query. We are going to create the contained database CDB1. I have set the auto-close property of the database to off as I am using a SQL Server 2012 Express edition.

/* Create Contained Databases */
use [master];
if exists (select 1 from sys.databases where [name] = 'CDB1')
		drop database [cdb1];

-- create a contained database
create database [cdb1] containment = partial;

-- turn off auto close setting
use [master];
alter database [cdb1] set auto_close off;

Authentication @ Contained Database

When we connect to the SQL server instance using a SQL Login, the authentication happens at the instance level (against master database, where the SQL login and passwords are stored). With the Partial containment in SQL Server 2012, now the authentication happens at the contained database level for the contained users. One of the new feature available with Contained Database is the new user type, SQL User with Password. This new user account, removes the dependencies on the SQL Login, by storing the password hash inside the contained database. Thus, whenever, we move the database from one SQL server 2012 instance to another SQL Server 2012 instance, we don’t have to transfer the logins to the new instance anymore, our application can connect immediately to the database once its online. Remember, this feature is only applicable to Contained Users, however, if we create a SQL Login and assign certain permissions on the contained database, then we have to transfer the login manually to the new instance.

Although, there is no changes from a Windows user perspective, we don’t have to create a Windows Login at the instance level for a Contained user. With that, let’s see how we create the new SQL User account with Password.

Create a Contained User

As seen before, ContainedUser we can create the new SQL user account from GUI mode. A point to consider is that, we have to create the contained user within the contained database. This user account is not available in a Non-Contained Database. Alternatively, we can also use the following t-sql query to create the contained user.

/* Create Contained Database User */

-- create a contained database user with password
use [cdb1];
create user [cdb1_user] with password ='passw0rd@1'
	, default_language = [English]
	, default_schema = [dbo];

Update: We can also create a contained user from an existing / new windows account; however, we don’t have to create the Windows Login at the instance level. We can directly create the Windows user inside the contained database. Thanks Sahal for asking.

-- Create a Contained User from Windows
use [cdb1];
create user [skg\sqldba] with default_schema = [dbo];

Connect using Contained-User Account

Now we have a contained database and a new Contained user account. It’s time to connect to the instance using the new account. When we try to login to the instance after providing the new user name and password, we will get the following error

Login failed for user ‘cdb1_user’. Reason: Could not find a login matching the name provided.

Now the reason for the above login failure is, SQL Server is trying to authenticate the user account against master database; however, the new SQL user with password is created inside the contained database, in our case, it’s present inside the database CDB1. All we have to do, while accessing the contained database using a contained user account, we have to explicitly mention the “Connect to Database” as the contained database name, in the connection properties window in SSMS. ConnectingAsContainedUser

It’s done. As we see from the above screenshot, we are able to connect to the instance, using the new SQL user account. In the next part, we will look at the enhancement on the Collation setting in Contained Database.

Happy Learning Smile


About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in ContainedDB, How To, SQL Server, SQL2012, UG Meet and tagged , , , , , . Bookmark the permalink.

5 Responses to Contained Database 101

  1. Sahal Omer says:

    Well explained bro, i have one question, i have a contained database and there is a login for example for an application and it has to use a domain account. can we use this feature in this situation or it works only with “Sql user with password”?

    • Good one, Sahal. Thanks for asking. Although SQL Server allows Windows Authentication, We have to create a Windows Login manually from AD. However, incase of Contained Database, we only have to create the user for the Windows Login. For example, here is the script to create a Contained User from Windows. We have to create this user within the Contained Database.

      -- create a contained database user from an existing domain login
      use [cdb1];
      create user [skg\sqldba] with default_schema = [dbo];

      Hope, I didn’t confuse you !!

  2. Sahal Omer says:

    No, you didnt confuse me. its clear. from GUI also can we create a contained user and domain login?

    • Yes, however, if we want to create contained user (a SQL user with password &/ a Windows User), we have to create it within the Contained database. Expand the Contained Database –> Go to Security –> Users. Here, we can create the contained user

  3. Pingback: Contained Database 102 | SQL…. Still Learning

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s