casinos

Contained Databases inside SQL Server 2012 October 3rd, 2012

Vinod Kumar

Recently I was reading one of the blog post from Pinal around “Importance of users without Logins” and this triggered this post. The concept of contained databases are lesser known inside SQL Server 2012 release and hardly mentioned. In this post let me introduce what Contained Databases are and explain some of the benefits.

Before we hit the basics, it is important to understand what are the different things applications require from the database? Apart from the database, data and some logics (SP’s, functions, triggers etc) there are also components that live outside the scope of the application database. Some of them include:

  1. Logins: Maintained in the master DB.
  2. Temp tables: T-SQL code use them and are manipulated in the tempDB Database.
  3. Jobs: These can be agent jobs for specific automation or day end jobs for cleansing.
  4. Error Messages: Custom error messages are stored inside the master DB.
  5. Linked Servers: These are stored in the master database.

There can be other things that get missed from the above list. But the problem starts when this application needs to be moved around between environments for system upgrades, high availability, consolidation or load balancing etc reasons. Before SQL Server 2012 there was no systematic process to take these items along in this situation. The biggest downfall today is to have significant familiarity with all these moving components and to manually set them on each of the environments. I am not trying to make this sound really bad, but these are things to keep in mind even today.

Partial Containment in SQL Server 2012

Now that we know the basics of why and need for some changes, let me introduce the containment concept. SQL Server 2012 introduces partial containment concept which introduces an application boundary around the database yet allowing features that cross the application boundary to be accessible. As the name suggests, in this release we have the contained authentication and some subtle collation dependencies added. This means:

  1. We can have a new SQL User with a password part of the contained authentication.
  2. Now tempDb objects don’t use the default collation of tempDB but uses that defined with the contained database.

I feel this is a great start atleast. In the future, we can see enhancements going into this feature to make it more complete by adding all the application objects into the containment boundary. Only the future releases will define these, let us see what we have in hand.

Enable Contained Databases at Instance level

Using SQL Server Management Studio, the steps are simple.

  1. In Object Explorer, right-click the server name, and then click Properties.
  2. On the Advanced page, in the Containment section, set the Enable Contained Databases option to True.
  3. Click OK.

image

If you would like to script this step and use it part of your application deployment strategy.

EXEC sp_configure ‘show advanced options’, 1 ;
GO
RECONFIGURE ;
GO
EXEC sp_configure ‘contained database authentication’, 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure ‘show advanced options’, 0 ;
GO
RECONFIGURE ;
GO

The above option enables both contained databases and contained authentication.

Creating a Partially Contained Database

Just like the option with the Server node, there is a containment type option that has been added to the existing database options. The same can be set in the database properties –> Options page like the image below:

image

From a T-SQL perspective, you can use the option extended part of the CREATE Database command to enable the same.

CREATE DATABASE database_name
[ CONTAINMENT = { OFF | PARTIAL } ]
[ ON
[ PRIMARY ] <filespec> [ ,...n ] ….

Now coming for existing databases to be converted to contained databases, there are few steps to be taken. I would highly recommend to check if there are any containment errors that might come before this step. The first and simplest way would be to use the DMV (sys.dm_db_uncontained_entities). Just run:

select * from sys.dm_db_uncontained_entities

Some of the Dynamic SQL cannot be determined till the run time and can be ignored or needs further attention. To pick these errors at runtime use the database_uncontained_usage Xevent if needbe. Since this XEvent will fire for actual uncontained entities at run time, it will not identify any uncontained user entities that you have not run.

Once the errors have been eliminated and we are sure the database is safe to be turned into contained database, use the SQL Server Management Studio to do the same or use the below T-SQL.

USE [master]
GO
ALTER DATABASE [AdventureWorks2012] SET CONTAINMENT = PARTIAL
GO

Once this step has been performed, check if the same has taken effect. The two columns namely containment and containment_desc can be used to determine the containment state of the databases.

SELECT containment, containment_desc FROM sys.databases
WHERE name like ‘AdventureWorks2012′

Next step would be to Migrate your existing logins to be converted to SQL users for contained databases. This can be done using the SP sp_migrate_user_to_contained. A typical login called “AppUser” can be converted like below:

EXECUTE sp_migrate_user_to_contained
        @username = ‘AppUser’,
        @rename = N’keep_name’,
        @disablelogin = N’disable_login’;

If you would like to find out specific Logins associated with users inside a given database, feel free to use the below query:

SELECT dp.name
       FROM sys.database_principals AS dp
       JOIN sys.server_principals AS sp
       ON dp.sid = sp.sid
       WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;

Creating Contained Users

We started this blog post talking about users without logins. Contained users are new and authenticated inside SQL Server 2012 and can be of 3 types.

  1. User based on a Windows user who has no login.
  2. User based on a Windows group that has no login.
  3. Contained database user with password.

To create a new contained user, under the Security node inside the Database node you have the New –> User option.

image

Once inside the dialog, Select either SQL user with password or Windows user. On the General page, enter a name for the new user in the User name box and click OK.

image

To do the same operation inside T-SQL, feel free to use the below syntax. You can see we have not referenced the Login here for the user. For a contained DB this will be an contained user.

CREATE USER MyDBUser
WITH PASSWORD = ‘my$tr0ngPwd’;
GO

How are Users Authenticated?

For SQL Server authentication in a contained database, the connection must specify an initial catalog and the authentication in this scenario is first attempted against the contained user. In event of no such user exists, SQL Server will fall back to check the authentication at the server level. If the user or password donot match then the authentication will fail.

The concept is similar incase of Windows authentication too but the order is reversed as the first check is done at the server level and then at the DB level.

Final thoughts

I think this is a great start to understand contained databases to start with. Do try to play around with this feature and drop a line if they are making interesting scenario’s for you. We have refrained from talking about other fine prints and scenarios of duplicate names (Login ID), tempDB objects, Collation etc in this post. We will reserve the same for a later post.

Hope this post makes it easy to understand what contained databases are, how to create contained users and what are some of the fine prints to understand. Your comments are most welcome.

Tags: , , , , , , ,

This entry was posted on Wednesday, October 3rd, 2012 at 10:14 and is filed under Book, Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


17 Responses to “Contained Databases inside SQL Server 2012”

  1. Raja says:

    Can I create multiple contained DBs on a single instance of SQL, and assign a separate IP to each Contained DB ?

  2. senthilkumar says:

    I could’t not understand. What is the purpose of Contained DB.?

    • Vinod Kumar says:

      Senthil – Contained DB is a process of bringing all the artifacts of a given application (DB, TempDB, Collation, Authentication, Authorization etc) into the simple boundary of a database. Doing this, we can just move this database around servers without having to worry about application breakage (except for connection strings). This is helpful in consolidation environments.

  3. Dear sir

    I have created contained database by name containedDB successfully
    i have created user for it.

    I am also able to login into the contained database, where i can see only single database

    THE PROBLEM I AM FACING IS THAT.

    when i am trying to right click the contained database name
    a dialogue comes up and says

    “login failed for the [user], error code 18456″

    sir what could be the reason.

  4. Hi Vinod,

    Thanks a lot for this nice post, quite informative.

    Regards,
    Girijesh

  5. siva says:

    Hi Vinod,

    is there any chanse to implement in 2008 using policy based mangement
    when ever db failover occur every time we are doing manully some of the users are not mapping to logins could you please suggest how can we over come this issue in 2008

    Best Regards
    Siva

    • Vinod Kumar says:

      I think you are getting Policy Based Management wrongly. It is completely for a different purpose. As far as I know, the behavior of contained database is not possible in SQL 2008. Contained Authentication and TempDB collation isolations are not possible in earlier versions.

  6. siva says:

    Hi Vindod,

    Thanks for your valuble information

    Best Regards
    Siva

  7. siva says:

    Hi Vinod

    It will work on the sql server 2008 R2, it is very useful to us at the time of db failover in DB Mirroing some of the logins and user are not mapped at that time better we can use only users

    can you please suggest us it is work in 2008 we will implement it. Becuase every DB automatic/ manual failover we facing alote of issues with logins and users

    Regars
    Siva

    • Vinod Kumar says:

      Siva – good point. Contained databases are exactly poised for this scenario only and works great in HA scenario when coupled with features like AlwaysOn in SQL 2012. Maybe in my next post I will talk about these.

      Unfortunately this feature is unavailable in the SQL 2008 R2 version or prior and no possible workaround possible in my opinion.

    • philhege says:

      Siva, the disconnection between users/logins during failover and recovery is a by-design issue. We have a small suite of management SPs, used during the recovery process, that re-associate logins and database users, and report on orphaned users. This makes the process of recovery a bit less painful.

  8. [...] and my friend and SQL Expert Vinod Kumar has written excellent follow up blog post about Contained Databases inside SQL Server 2012. Now lots of people asked me if I can also explain the same concept again so here is the small [...]

  9. [...] Contained Databases inside SQL Server 2012 Share:LinkedInTwitterFacebookEmailPrintMoreDiggRedditStumbleUponTumblrPinterest [...]

Leave a Reply