Although it looks like an individual entity, a database in SQL Server is tightly integrated with the SQL Server instance. Every time we move an user database from one instance to another, a series of steps follow, which includes transfers of logins, migration of jobs, DB mail, endpoints, CLR etc.
If we look at the dependencies, a user database depends on all system databases and/or other user databases and the SQL Server instance. Most of the time, when we create a database, we normally leave the collation setting to server default. The server collation setting, logins, etc. are stored in the master database, Jobs, DB Mail, etc. goes to msdb, sorting happens in tempdb, and the model database provides the initial template for every user database. Apart from this, a user database, may depends on the other user databases. As mentioned above, a user database is tightly integrate with the SQL server instance and can not move as a single entity. Contained database, a feature offered in SQL Server 2012, can help us reducing these additional steps, while moving the database across environments.
A Contained Database is a database, which is isolated from other databases and from the instance of SQL Server that hosts the database.
In case of a contained database, all the configuration and metadata settings are stored within the database. With the release of SQL Server 2012, Partial Contained Database is supported.
This feature is off by default. If you create a database, by default, the containment type of the database is set as NONE. Here is the definition from Books Online:
A partial contained database is a contained database, which allows some features to cross the database boundary.
SQL Server 2012 offers the following features in Partial Contained Database.
- Authentication @ Contained database level
- Collation Setting @ Contained database level
However, for features like SQL job, DB Mail, CLR, we still have dependencies on the SQL Server instance, that’s why Partial Containment!
How should I Configure?
Before we create a contained database, we need to configure the SQL Server instance to enable Partial Containment. Well, we can do it, in one of the two ways. We can set the option from the GUI using SSMS as shown here, Connect to the instance using SSMS, Right-click on the instance, go to Properties, click on the Advanced tab. We can set the Enable Contained Databases setting to true. Alternatively, we can use the following system stored procedure to configure the same.
/* Enable Contained Database Authentication for the SQL Server Instance */
exec sp_configure 'contained database authentication', 1;
reconfigure with override;
Now that, we have configured the SQL Server instance, let’s create a contained database.
Create a Contained Database
We can create a contained database from the GUI, where we have to set the Containment Type to Partial in the database option page, or, we can use the following query. We are going to create the contained database CDB1. I have set the auto-close property of the database to off as I am using a SQL Server 2012 Express edition.
/* Create Contained Databases */
if exists (select 1 from sys.databases where [name] = 'CDB1')
drop database [cdb1];
-- create a contained database
create database [cdb1] containment = partial;
-- turn off auto close setting
alter database [cdb1] set auto_close off;
Authentication @ Contained Database
When we connect to the SQL server instance using a SQL Login, the authentication happens at the instance level (against master database, where the SQL login and passwords are stored). With the Partial containment in SQL Server 2012, now the authentication happens at the contained database level for the contained users. One of the new feature available with Contained Database is the new user type, SQL User with Password. This new user account, removes the dependencies on the SQL Login, by storing the password hash inside the contained database. Thus, whenever, we move the database from one SQL server 2012 instance to another SQL Server 2012 instance, we don’t have to transfer the logins to the new instance anymore, our application can connect immediately to the database once its online. Remember, this feature is only applicable to Contained Users, however, if we create a SQL Login and assign certain permissions on the contained database, then we have to transfer the login manually to the new instance.
Although, there is no changes from a Windows user perspective, we don’t have to create a Windows Login at the instance level for a Contained user. With that, let’s see how we create the new SQL User account with Password.
Create a Contained User
As seen before, we can create the new SQL user account from GUI mode. A point to consider is that, we have to create the contained user within the contained database. This user account is not available in a Non-Contained Database. Alternatively, we can also use the following t-sql query to create the contained user.
/* Create Contained Database User */
-- create a contained database user with password
create user [cdb1_user] with password ='passw0rd@1'
, default_language = [English]
, default_schema = [dbo];
Update: We can also create a contained user from an existing / new windows account; however, we don't have to create the Windows Login at the instance level. We can directly create the Windows user inside the contained database. Thanks Sahal for asking.
-- Create a Contained User from Windows
create user [skg\sqldba] with default_schema = [dbo];
Connect using Contained-User Account
Now we have a contained database and a new Contained user account. It’s time to connect to the instance using the new account. When we try to login to the instance after providing the new user name and password, we will get the following error
Login failed for user 'cdb1_user'. Reason: Could not find a login matching the name provided.
Now the reason for the above login failure is, SQL Server is trying to authenticate the user account against master database; however, the new SQL user with password is created inside the contained database, in our case, it’s present inside the database CDB1. All we have to do, while accessing the contained database using a contained user account, we have to explicitly mention the “Connect to Database” as the contained database name, in the connection properties window in SSMS.
It’s done. As we see from the above screenshot, we are able to connect to the instance, using the new SQL user account. In the next part, we will look at the enhancement on the Collation setting in Contained Database.