LearnNowOnline

Blog

Contained Databases in SQL Server 2012

Excerpt by Don Kiely | May 29, 2013

Contained databases is an interesting new part of SQL Server 2012. This isn't directly a security feature as such, but it implements a new authentication feature and so has a security element. This chapter will not cover contained databases in detail, but briefly contained databases solves the problem of moving databases from one server to another. In the past, you had to move the database itself, in addition to server-level objects such as logins and SQL Agent job information. Getting everything configured on the destination server was a royal pain, particularly since you had to recreate server logins and remap security IDs (SIDs). Contained databases attempts to solve most of these problems.

The new authentication feature necessitated by contained databases is the ability to create a SQL user in a database with a password, or create a user associated with a Windows user without requiring an associated login.

Authentication takes place directly against the database, and a successful authentication results in a token that grants access to that database only. This serves to provide a tightly scoped and narrow security boundary around the database so that the authenticated user can only perform database-level operations.

The sample code demonstrates how contained databases works. You'll need a contained database to try it out on, so there is also a PubsContained.sql script file that creates a contained version of the old pubs sample database. The main change, besides cleaning up some archaic statements, is how the code creates the PubsContained database:

CREATE DATABASE PubsContained CONTAINMENT = PARTIAL;

The CONTAINMENT option set to PARTIAL results in a contained database, while a setting of NONE creates a regular database, which is the default. Using this clause creates the database and configures it to allow authentication against the database.

Before you run the script in PubsContained.sql, you have to enable the contained databases feature in this instance of SQL Server. A fresh installation of SQL Server minimizes the available attack surface for security vulnerabilities by installing some features but disabling them. Contained databases is one such feature.

The Contained Databases.sql file has the code to enable contained databases in an instance of SQL Server (it's disabled by default). When making certain configuration changes in SQL Server, you have to enable the "show advanced" feature to allow the change, so this code sets that feature on, makes the change, and sets it back off. The relevant line of code for contained databases is the highlighted statement.

sp_configure 'show advanced', 1;

RECONFIGURE;

GO

sp_configure 'contained database authentication', 1;

RECONFIGURE;

GO

sp_configure 'show advanced', 0;

RECONFIGURE;

GO

Now you can successfully execute the code in PubsContained.sql.

The code then creates a user floyd in the PubsContained database with a strong password, using the following statement. This part is what's new in contained databases in SQL Server 2012: when a user attempts to log in as floyd, the authentication happens in the database, not at the server level.

CREATE USER floyd WITH PASSWORD = '%5JiD2s^6^Y^$u26q7YL';

Then you can start a new instance of Management Studio and log in as floyd (copy the password!). The log in fails, because the user floyd is not a server login and so cannot be authenticated using a traditional SQL login. In order to authenticate floyd in the PubsContained database, you need to click the Options button in the Connect to Server dialog box and set the Connect to database option to PubsContained, as you can see in the Figure below.

TIP: Don't even try to select the database from the drop-down list; you've entered floyd's login information, which doesn't have the ability to even see which databases are available in the SQL Server instance. Your only option is to type in the database name.

When you click the Connect button, this time floyd is able to connect. At this point the user has access to the PubsContained database, but no permissions on any objects. And, as you can see in the Figure below, floyd has no access to any other server objects, so the only thing visible in Object Explorer is the PubsContained database. floyd is a very restricted user!

This post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.