Contained Databases in SQL Server “Denali”

OK, so here’s a common scenario. You’ve developed an application that uses a SQL Server database on a development machine, and now you need to deploy the application (and its database) to a staging or production environment. Of course, you can generate scripts to recreate the database on another server, or you could simply back it up and restore it to the other server. However, while that would successfully move the database, it would not take across any server-level objects that the application depends on, such as logins or Agent jobs. SQL Server 2008 R2, introduced Data-Tier applications as a way to package up databases with their server-level dependencies and deploy them to another server (which I covered in a previous blog post), and this is a great step forward, but to be honest, wouldn’t it be nice if the application database was simply self-contained with no need to rely on server-level objects at all?

Well, in SQL Server “Denali”, you get your wish in the form of Contained Databases. When you create a database, you now have the option of setting its containment type property to partial, which enables you to create “contained” objects that would normally be defined at the server-level within the database itself – most notably logins. The “partial” containment type value specifies that the database can use a mixture of contained and non-contained objects (so for example, traditional server-level logins can be used to access the database as well as contained logins) – a further option of “full” containment (which disallows the use of non-contained objects) is promised but not supported in the current CTP 1 release.

So let’s see an example of how to create and use a contained database. First, you need to enable contained databases in the server. You can do this in the Server Properties dialog box as shown here, or you can use the following Transact-SQL code:

sp_configure 'show advanced options', 1 ;

GO

RECONFIGURE ;

GO

sp_configure 'contained database authentication', 1;

GO

RECONFIGURE ;

GO

sp_configure 'show advanced options', 0 ;

GO

RECONFIGURE ;

GO

After you’ve enabled contained databases, you can create one like this:

CREATE DATABASE [MyContainedDB]

CONTAINMENT = PARTIAL

GO

Or alternatively, you can use the New Database dialog box in SQL Server Management Studio and set the Containment type property as shown here.

 

 

 

 

 

Now that you have a contained database, you can create users within it. The important point here is that you can create users that do not map to server-level logins, so the traditional dependency between a user object in a database and a login object in the server instance where that database is hosted is broken. To create a contained SQL Server user that has a password, you can use a Transact-SQL CREATE USER statement in the contained database like this one:

USE [MyContainedDB]

GO

CREATE USER [MyContainedUser] WITH PASSWORD = ‘5up3r53cret’

GO

Or, you can create a contained user for a Windows account like this:

CREATE USER [DEVBOX\MyAppAccount]

GO

Of course, you can also use the graphical tools in SQL Server Management Studio to create a contained user, as shown here:

Note that if you want to use a SQL Server user, you need to enable so-called “mixed mode” authentication at the server-level – even though there’s no server-level SQL Server logins.

As stated before, contained users do not rely on server-level logins, they exist only within the contained database. This means that when you want to connect to the contained database from a client application, you need to specify the database name in the connection string or the connection will fail because SQL Server will attempt to default to the master database and try to authenticate your credentials as a server-level login. In a typical client application, you can specify the database in a connection string like this:

“SERVER=mysqlserver; DATABASE=MyContainedDB; USER ID=MyContainedUser; PASSWORD=5up3r53cret”

When connecting from tools like SQL Server Management Studio, you can specify the database name in the connection dialog box like this:

The ability to create contained databases that include users with no dependency on server-level logins can be extremely useful when you need to move the database from one server to another. However, you should be aware of the security implications of using database-specific authentication that effectively bypasses the usual security management architecture. You can find out more about these security implications here.

del.icio.us Tags: SQL Server Denali