Guest Post: How to use Contained Databases in SQL Server 2012

  • Article

Angela Cataldo works for Firebrand Training as a subject matter expert and instructor for SQL Server and System Centre. For over 10 years Angela specialized in SQL Server, delivering training and consultancy services to a number of companies throughout the UK and Europe guiding and mentoring customers to follow Microsoft Best Practice and assist in their understanding and adoption of SQL innovative features.

Before SQL Server 2012, databases have always had a degree of portability. But with SQL Server 2012 we can now embrace the powerful new manageability and security features of Contained Databases - which make a database much more portable.

Why do we need Contained Databases?

SQL Server security has always been managed at two levels:

image

Logins are managed at Server level, and users are managed at Database level. This means permissions for SQL Server have to be defined in two or more locations, and this can cause confusion.

Also, having to manage logins and users separately can cause problems when it comes to maintaining high availability and disaster recovery solutions. And the need to regularly synchronise logins against failover and secondary servers; to avoid problems such as orphaned users.

So with the introduction of containment and the concept of boundaries in SQL Server 2012; a database can become free of external dependencies, server level metadata, settings and security logins.

For a Database Administrator this can also potentially help with the problem we have all faced after recovering a database: repairing a large numbers of logins using the sp_change_users_login stored procedure.

What is a Contained Database?

In simple terms it is a database that is isolated from other databases, and isolated from the instance of SQL Server that is hosting the database.

There are four ways that SQL Server 2012 helps to isolate databases from the instance:

  • Much of the metadata that describes a database is maintained in the database and not in the master database
  • All metadata is defined using the same collation
  • User authentication can be performed by the database, reducing the database’s dependency on the logins of the instance of SQL Server
  • The SQL Server environment reports, DMVs and xEvents can act upon containment information.

How to create a Contained Database

In this example I am going to demonstrate - in four steps - how to create and authenticate against a Contained Database:

  1. Enable Contained Database Authentication
  2. Create a Contained Database
  3. Create a user in the Contained Database
  4. Authenticate a user against the Contained Database

Step 1

First I need to enable contained database authentication, by executing the following code as a New Query in SQL Server Management Studio (SSMS) against the master database:

 

 

The first sp_configure line reports on the current setting and the second sp_configure line enables the server-level setting. This allows SQL Server to defer authentication to the database, provided that we have configured the users correctly with the right authentication.

Step 2

Now I can create a contained database, executing the following code as a New Query in SSMS:

image

If we take a look at the Database properties of this database we can see on the Options Select a Page the menu option for Containment Type:

  • Partial – boundary defined isolating the database from the server instance
  • None – the default containment type for a database

image

You can also use SSMS to configure containment for databases.

Step 3

Now we have a contained database we next need to create a user by executing the following code as a New Query in SSMS:

image

For further syntax of Create User see SQL Server Books Online: CREATE USER, examples cover:

  • Creating a contained user for a domain login
  • Creating a contained user with a specific SID

You can also use SSMS to create a contained user, for User Type selecting SQL User with password:

image

We can also take an existing user and convert it to a contained user executing stored procedure sp_migrate_user_to_contained.

For an explanation of syntax see SQL Server Books Online: sp_migrate_user_to_contained

Step 4

Now we can take the final step and login in as a Contained Database user, ensuring that in the Connection Properties, under Connect to database,isour Contained Database AdventureWorks2012.

When connecting to a contained database, if the user does not have a login in the master database, the connection string must include the contained database name as the initial catalog. The initial catalog parameter is always required for a contained database user with password.

image

In four simple steps I have enabled database level authentication, created a contained database and contained user then logged into SQL Server Management Studio as the new user.

What else do I need to know?

As a Database Administrator, security is a major concern and there are unique threats when using Contained Databases that must be considered. Thankfully SQL Server Books Online has a dedicated page on these implications: Security Best Practices with Contained Databases.

An example is passwords in a database require to be strong, complex passwords - and cannot be protected by domain password policies. Therefore, wherever possible create contained users for domain logins and take advantage of Windows Authentication.

Contained databases are set to be one of the top new features for DBAs. Plus AlwaysOn Availability Groups are also new to SQL Server 2012 - helping to simplify environmental and failover concerns, to ensure a highly available disaster recovery solution. SQL Server 2012 Database Containment is simply one of the best things to have happened to SQL Server.