Voice of the DBA

Tag: contained databases

Enabling Database Containment for an Instance – #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

I wanted to test a contained database feature the other day and ran this:
```
ALTER DATABASE [sandbox2] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO
```
However, this didn’t work. I ended up with an error:

Msg 12824, Level 16, State 1, Line 3

The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database. You may need to use RECONFIGURE to set the value_in_use.

The issue is that the server instance needs to have contained authentication enabled in order to pass any authentication requests to the database
```
EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
```
Now I can run the code again to alter the database for containment.

SQLNewBlogger

The issue was obvious to me since I’d dealt with it in the past, but this is something you could solve and write up in 10-15 minutes.
October 4, 2017
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:

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.
July 19, 2012
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

July 14, 2012
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:

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:

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:

The contained database has correctly resolved the collation issues.

You can check the collations with sp_help with the two table names.
June 7, 2012