Contained Database Authentication: Introduction

In Microsoft SQL Server code-name “Denali” Community Technology Preview 1 (CTP1) we introduced the Contained Database (CDB) feature.

As the name suggests, self-contained database have no external dependencies. Contained databases can therefore be easily moved to another server and start working instantly without the need of any additional configuration.

One of the key features of a CDB is the ability to remove the reliance upon logins so that the database will become more portable. As a result the concept of Contained Users is introduced in SQL Server “Denali”.

A contained user is a user without a login in the master database which resides in a Contained Database and can connect to this database specifying its credentials in the connection string. For SQL Server Authentication Users, this implies that the password will have to be provided when such users are created; Windows Authentication Users can be created the same way they are traditionally created:

-- A member of the sysadmin fixed server role must explicitly enable contained database authentication on the instance of SQL Server

sp_configure 'show advanced', 1;

RECONFIGURE WITH OVERRIDE;

go

sp_configure 'contained database authentication', 1;

RECONFIGURE WITH OVERRIDE;

go

-- To create contained db you have to specify CONTAINMENT property

CREATE DATABASE db_Contained

CONTAINMENT = PARTIAL;

go

USE db_Contained;

go

-- Create a contained SQL Server Authentication user

CREATE USER usr_Contained

WITH PASSWORD = 'LJDUT9!@$';

go

-- Create a Windows Authentication user

CREATE USER [DOMAIN\User_01];

go

User that resides entirely within a database is considered contained. Such user can only connect to the database where they have been created, cannot change database and has virtually no permissions outside of this database.

 Note, that a Windows Authentication user created above could be contained or not-contained depending on the existence of a corresponding login DOMAIN\User_01. If the login exists, the newly created user is not contained and can go outside of the contained database where he may have some permissions associated to the corresponding login. If such a login doesn’t exist, the user is contained, has virtually no permissions outside of the database, and can only connect to the database where he resides. Therefore, a Windows Authentication user can change its containment behavior when a Windows Authentication login is created or dropped or when the database is moved to another instance of SQL Server where, again, such a login may or may not exist. However, typically it shouldn’t affect the application’s behavior because inside the database the user will possess the same permissions independently of its containment status. How a user connects and whether they get a full login or just a contained login will be covered in future post.

To connect with contained database user credentials you have to specify the contained database in the connection string. If no database is specified, the connection will attempt traditional authentication as a login in the master database.

Read more about database authentication in further posts.