Contained Databases inside SQL Server 2012 - by Vinod Kumar

Recently I was reading one of the blog post from Pinal around “Importance of users without Logins” and this triggered this post. The concept of contained databases are lesser known inside SQL Server 2012 release and hardly mentioned. In this post let me introduce what Contained Databases are and explain some of the benefits.
Before we hit the basics, it is important to understand what are the different things applications require from the database? Apart from the database, data and some logics (SP’s, functions, triggers etc) there are also components that live outside the scope of the application database. Some of them include:

1. Logins: Maintained in the master DB.
2. Temp tables: T-SQL code use them and are manipulated in the tempDB Database.
3. Jobs: These can be agent jobs for specific automation or day end jobs for cleansing.
4. Error Messages: Custom error messages are stored inside the master DB.
5. Linked Servers: These are stored in the master database.

There can be other things that get missed from the above list. But the problem starts when this application needs to be moved around between environments for system upgrades, high availability, consolidation or load balancing etc reasons. Before SQL Server 2012 there was no systematic process to take these items along in this situation. The biggest downfall today is to have significant familiarity with all these moving components and to manually set them on each of the environments. I am not trying to make this sound really bad, but these are things to keep in mind even today.

Partial Containment in SQL Server 2012

Now that we know the basics of why and need for some changes, let me introduce the containment concept. SQL Server 2012 introduces partial containment concept which introduces an application boundary around the database yet allowing features that cross the application boundary to be accessible. As the name suggests, in this release we have the contained authentication and some subtle collation dependencies added. This means:

1. We can have a new SQL User with a password part of the contained authentication.
2. Now tempDb objects don’t use the default collation of tempDB but uses that defined with the contained database.

I feel this is a great start atleast. In the future, we can see enhancements going into this feature to make it more complete by adding all the application objects into the containment boundary. Only the future releases will define these, let us see what we have in hand.

Enable Contained Databases at Instance level

Using SQL Server Management Studio, the steps are simple.

1. In Object Explorer, right-click the server name, and then click Properties.
2. On the Advanced page, in the Containment section, set the Enable Contained Databases option to True.
3. Click OK.

If you would like to script this step and use it part of your application deployment strategy.

EXEC sp_configure ‘show advanced options’, 1 ;
GO
RECONFIGURE ;
GO
EXEC sp_configure ‘contained database authentication’, 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure ‘show advanced options’, 0 ;
GO
RECONFIGURE ;
GO

The above option enables both contained databases and contained authentication.

Creating a Partially Contained Database

Just like the option with the Server node, there is a containment type option that has been added to the existing database options. The same can be set in the database properties –> Options page like the image below:

From a T-SQL perspective, you can use the option extended part of the CREATE Database command to enable the same.

CREATE DATABASE database_name
[ CONTAINMENT = { OFF | PARTIAL } ]
[ ON
[ PRIMARY ] <filespec> [ ,...n ] ….

Now coming for existing databases to be converted to contained databases, there are few steps to be taken. I would highly recommend to check if there are any containment errors that might come before this step. The first and simplest way would be to use the DMV (sys.dm_db_uncontained_entities). Just run:

select * from sys.dm_db_uncontained_entities

Some of the Dynamic SQL cannot be determined till the run time and can be ignored or needs further attention. To pick these errors at runtime use the database_uncontained_usage Xevent if needbe. Since this XEvent will fire for actual uncontained entities at run time, it will not identify any uncontained user entities that you have not run.
Once the errors have been eliminated and we are sure the database is safe to be turned into contained database, use the SQL Server Management Studio to do the same or use the below T-SQL.

USE [master]
GO
ALTER DATABASE [AdventureWorks2012] SET CONTAINMENT = PARTIAL
GO

Once this step has been performed, check if the same has taken effect. The two columns namely containment and containment_desc can be used to determine the containment state of the databases.

SELECT containment, containment_desc FROM sys.databases
WHERE name like ‘AdventureWorks2012′

Next step would be to Migrate your existing logins to be converted to SQL users for contained databases. This can be done using the SP sp_migrate_user_to_contained. A typical login called “AppUser” can be converted like below:

EXECUTE sp_migrate_user_to_contained
        @username = ‘AppUser’,
        @rename = N’keep_name’,
        @disablelogin = N’disable_login’;

If you would like to find out specific Logins associated with users inside a given database, feel free to use the below query:

SELECT dp.name
       FROM sys.database_principals AS dp
       JOIN sys.server_principals AS sp
       ON dp.sid = sp.sid
       WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;

Creating Contained Users

We started this blog post talking about users without logins. Contained users are new and authenticated inside SQL Server 2012 and can be of 3 types.

1. User based on a Windows user who has no login.
2. User based on a Windows group that has no login.
3. Contained database user with password.

To create a new contained user, under the Security node inside the Database node you have the New –> User option.

Once inside the dialog, Select either SQL user with password or Windows user. On the General page, enter a name for the new user in the User name box and click OK.

To do the same operation inside T-SQL, feel free to use the below syntax. You can see we have not referenced the Login here for the user. For a contained DB this will be an contained user.

CREATE USER MyDBUser
WITH PASSWORD = ‘my$tr0ngPwd’;
GO

How are Users Authenticated?

For SQL Server authentication in a contained database, the connection must specify an initial catalog and the authentication in this scenario is first attempted against the contained user. In event of no such user exists, SQL Server will fall back to check the authentication at the server level. If the user or password donot match then the authentication will fail.
The concept is similar incase of Windows authentication too but the order is reversed as the first check is done at the server level and then at the DB level.

Final thoughts

I think this is a great start to understand contained databases to start with. Do try to play around with this feature and drop a line if they are making interesting scenario’s for you. We have refrained from talking about other fine prints and scenarios of duplicate names (Login ID), tempDB objects, Collation etc in this post. We will reserve the same for a later post.
Hope this post makes it easy to understand what contained databases are, how to create contained users and what are some of the fine prints to understand. Your comments are most welcome.