Contained Databases – Server Setting Matters

In doing some additional testing on contained databases, I decided to create a new database on a new test VM.

CREATE DATABASE cdb1
 containment = PARTIAL
;

To my surprise, I got this error:

Msg 12824, Level 16, State 1, Line 1

The sp_configure value ‘contained database authentication’ must be set to 1 in order to create

a contained database.  You may need to use RECONFIGURE to set the value_in_use.

I checked the server setting, and sure enough the instance property was set to 0 (false).

At first you might think this shouldn’t matter, but imagine you go to attach a backup of a contained database to an instance that doesn’t have this enabled. However there are a few security and administrative concerns over contained databases. We have the password policies, the potential collision of user names, and more.

The easy fix is to enable the instance level setting. That’s easily done with this code:

-- Set advanced options
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'contained database authentication', 1;
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO

What about restoring a contained database backup? Surely it will just come online without the contained authentication?

I tried it, before running the script above, and I got this error:

containedfail

Clearly the instance level setting matters. It’s easy to change, either in script or the GUI. However if you use the GUI, please don’t click OK and save the changes. Use the script button, save that for your logging/documentation, and then run the script.

AlwaysOn and Contained Databases in SQL Server 2012

This talk is mostly based on contained databases in SQL Server 2012. The first part of the talk goes over the problems with current database movement, the idea of containment, and the implementation in SQL Server 2012. There are demos that cover the user and collation scenarios. The second part of the talk goes into the basics of AlwaysOn. I talk about the architecture, the various scenarios you can have, and the advantages and drawbacks of the technologies involved. There are no demos of AlwaysOn mostly due to time.

Length: 60 minutes

Slides: downloadable after the SQL in the City 2012 tour.

Code: Demo code

Contained Databases – Preventing Collation Conflicts

One of the demos from my Contained Databases talk looks at the issues you can have when your database collation does not match your server collation. I’ll walk through the issue here. I’ll show the issue, and then the fix with contained databases.

First, let’s create a database and a table:

-- create db without containment
CREATE DATABASE ucdb2
 COLLATE Japanese_CS_AS
;
go
USE ucdb2
;
go


-- Create Unicode Table, add a row
CREATE TABLE MyTable
( mychar NVARCHAR(200)
);
go
INSERT MyTable SELECT 'This is a Japanese Row'
go

My server collation is shown here (SQL_Latin1_General_CP1_CI_AS:

containeddb1

Now let’s create the exact same table in tempdb.

-- create temp unicode table
CREATE TABLE #MyTable
( mychar NVARCHAR(200)
);
go
INSERT #MyTable SELECT 'This is a Japanese Row'
go

If I try to work with these two tables together, I will have problems. As an example, let’s just union query these two items together.

SELECT 
  'udcb2'
,  mychar
 FROM MyTable
UNION ALL
SELECT 
  'tempdb'
, mychar
 FROM #MyTable
;
go

I get an error, as shown here:

containeddb2

The collation error occurs because the query optimizer can’t decide which collation to use. You can easily fix this, as I’ve blogged about before with a collation clause.

However contained databases mean you don’t have to change code. Let’s do the same thing, this time with a contained database.

-- create db with containment
CREATE DATABASE cdb2
 CONTAINMENT = PARTIAL
 COLLATE Japanese_CS_AS
;
go
USE cdb2
;
go

-- Create Unicode Table, add a row
CREATE TABLE MyTable
( mychar NVARCHAR(200)
);
go
INSERT MyTable SELECT 'This is a Japanese Row'
go

-- create temp unicode table
CREATE TABLE #MyTable
( mychar NVARCHAR(200)
);
go
INSERT #MyTable SELECT 'This is a Japanese Row'
go

Now if I run the same statement, I see:

containeddb3

The contained database has correctly resolved the collation issues.

You can check the collations with sp_help with the two table names.

Creating a User without a Login – Contained Databases

In SQL Server 2012, we have a new feature: partially contained databases. In a previous post, I showed how to enable this, and this post will look at one of the advantages of contained databases: users without logins.

Contained Users

One of the problems in non-contained databases is the fact that when a database is moved or restored, the login mapping to the user in the database doesn’t always transfer cleanly. Microsoft has sp_help_revlogin and sp_change_users_login to help fix this, but in a DR situation, or in a crisis, this may not work. It’s also a hassle.

Contained users help fix this. They are users that exist within the database, and do not require a login mapping. The server level authentication will transfer to a database level authentication, if the database has the partial containment option set.

To create a contained user, you can use the GUI, or T-SQL, both of which are easy and I’ll show them below:

SSMS Contained User

If you right click the Users folder (under Security) in a database, you can select the New User option.

cdb3

When this appears, you can then use the drop down to select a User with a Password option for a SQL Server user that is contained inside a database.

cdb4

The traditional user is a user with a login. Here’s the dialog from SSMS 2008, with no option for a user without a password.

cdb8

Back to 2012, I can enter a user name and password, and then I have a user in my database.

cdb5

The process for a Windows user (again, without a login) is similar. I can select a “Windows User” and then select the ellipsis by the User name and search for an AD user.

cdb6

This looks the same when I accept a user

cdb7

I can set a default schema here, and a language, but I don’t need the login.

T-SQL

The process with T-SQL is the same. The code for the CREATE USER command is simple:

create user Billy with password = 'Billy2Goat$Gruff'
;

If I want a Windows user, I can do this:

CREATE USER [DKRSQL2012\Andy]
GO

Note that this is domain\user syntax. Some AD tools allow the user@domain syntax, but this isn’t allowed in SQL Server 2012 for the CREATE USER command.

You can replace the user name with a group at the database level, and the syntax is the same.

Summary

That’s it. It’s simple, and in another post, I’ll look at authentication.

Enabling Contained Databases in SQL Server 2012

One of the new features in SQL Server 2012 is the Partially Contained Database feature. I gave a talk on this recently, and I’m looking forward to seeing where this might go in the future.

This post looks at how you can enable containment in SQL Server 2012. It’s a very simple process, in a couple steps, and I’ll show you both with the GUI and with T-SQL.

Enable the Instance setting

There are two levels to enable containment. The first is at the instance level. If you get the server properties for your instance, and look at the advanced tab, you’ll see this:

cdb1

You can use the drop down to select the setting you want (true = enabled), but please don’t click the OK button. Always, always, always click the “Script” button. This allows you to see the exact code being run, and then you can also use this in your documentation for change control. Even if this is a development server, get in the habit of scripting things.

script

Once you click the script button, you’ll get this T-SQL:

EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

This will enable containment for the instance and you’ll be halfway there.

Database Containment

The database itself also has a containment setting. In this case, you can look at the properties for the database, on the options tab.

cdb2

If you look near the top, you have the collation drop down, the Recovery mode, the compatibility model, and then containment is new in 2012. You have “None” and “Partial” available, and clicking Partial will enable containment in  2012. Again, please don’t click OK, but click script.

The code will appear as below:

USE [master]
GO
ALTER DATABASE [cdb2] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

You can also set this value when you create a database:

-- create db with containment
CREATE DATABASE cdb2
 CONTAINMENT = PARTIAL

That’s it.

These two items together will enable containment on a database, and then you can work with contained users, something I’ll talk about in another post.

Contained Databases in SQL Server 2012

Abstract:

One of the problems with databases in SQL Server is the dependency of the database on various parts of the host instance. In SQL Server 2012 there is an enhancement to the database format that allows for partial containment of your database, and will make the movement, migration, and management of the databases much simpler. This session introduces the new database structure and explains how it can be used in your environment.

This talk looks at the new partially contained databases in SQL Server 2012.

Agenda:

  • What is a contained database
  • Contained databases in SQL Server 2012
  • The Future

The talk will demonstrate the issues with uncontained databases, provide demos on how to create and alter contained databases, look at security and collation issues in SQL Server and speculate on the future.

Level: 200

Length: 60 minutes

Slides:

Demo Code: ContainedDB.zip

Related Posts

You can read all the posts I’ve written on contained databases by examining that tag

Presentations

You can view my complete speaking schedule here: http://wp.me/P14wgJ-1tV