Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted February 10, 2012

Contained Database Authentication in SQL Server 2012

By Arshad Ali

Introduction

It is not an uncommon requirement to move your database from one SQL Server instance to another instance, either for migration or for setting up a failover scenario. But the problem is, a database relies on some of the data that resides outside the database and hence you have more overhead than just moving your database to a new instance; it means you also need to move/restore data that resides outside of the database (for example logins, etc.) to another instance along with your database. This does not sound very difficult but I must say it’s very tedious and any miss to move even a single object (stored outside the database) to your new instance can prevent your application from running as expected.

SQL Server 2012 (code name Denali) comes with a new feature called Contained Database Authentication, which solves this issue and makes life easier for both data administrator and application developer.

Contained Database Authentication

SQL Server 2012 introduces a new feature called Contained Database Authentication. The Contained Database Authentication feature allows the database to partially contain the data that was  previously stored outside the database. In other words, once you enable the containment feature at the SQL Server instance level, you can create a database that can store user information/credentials inside the database itself (rather than having a login at instance level, which gets stored in the master database) and hence you don't need to create logins on the target instance (and do mapping of SID) again during database movement. Quite a relief...isn't it?

Apart from the above benefit, contained database also provides one more enhancement. In cases where you have your server collation different from database collation, temporary objects will now be created by using the collation of the contained database instead of deriving the collation information from the server. You had to use COLLATE before, which is not required now.

Please note:

  • This is applicable to users who access the database and don’t do any system administration on the instance level
  • Partially contained database cannot use features like Replication, Change Data Capture or Change Tracking
  • Users created inside partially contained database will have guest access to master and tempdb databases
  • This feature is being used by the AlwaysOn feature internally as well to facilitate better user database portability

Enough of theory now, let’s jump into practical usage and do some hands on around this new feature.

Getting Started with Contained Database Authentication – Example

Contained Database Authentication is not enabled by default on the SQL Server instance. First you need to enable it at the instance level before you can create a database with containment or a database that can authenticate users. There are basically two ways to do this (other than using the PowerShell command); the first one is to use the sp_configure system stored procedure and enabling this feature with the script as shown below:

USE master
GO
sp_configure 'show   advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'CONTAINED   DATABASE AUTHENTICATION', 1
GO
RECONFIGURE
GO
sp_configure 'show   advanced options', 0 
GO
RECONFIGURE
GO

 

Configuring instance for contained database authentication with script
Figure 1 - Configuring instance for contained database authentication with script

 

The second way is to use the wizard from SSMS (SQL Server Management Studio). Connect to the SQL Server instance on which you want to enable this feature, right click on the instance name in the Object Explorer, click on the Properties menu and then click on the Advanced page as shown below. Finally set the value of the "Enable Contained Databases" property to TRUE from its default value of FALSE:

Configuring instance for contained database authentication with wizard
Figure 2 - Configuring instance for contained database authentication with wizard

Once enabled at instance level, you can create a database with containment by stating CONTAINMENT = PARTIAL clause in the CREATE DATABASE command as shown below. CONTAINMENT = NONE is the default value if you don’t specify this clause.

USE master
GO
--Contained Database   Authentication
CREATE DATABASE   [CDA]
 CONTAINMENT = PARTIAL
 ON    PRIMARY 
(
       NAME   = N'CDA', 
       FILENAME   = N'C:\Program   Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\CDA.mdf' , 
       SIZE   = 4096KB , 
       FILEGROWTH   = 1024KB )
 LOG   ON 
(
       NAME   = N'CDA_log', 
       FILENAME   = N'C:\Program Files\Microsoft   SQL Server\MSSQL11.SQL2012\MSSQL\DATA\CDA_log.ldf' , 
       SIZE   = 1024KB , 
       FILEGROWTH   = 10%)
GO
USE [CDA]
GO
IF NOT   EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') 
       ALTER   DATABASE [CDA]   MODIFY FILEGROUP   [PRIMARY] DEFAULT
GO

Creating a database with containment is not only possible with a script, as shown above, but you can also use the "New Database" wizard from SSMS for this. When creating a database from the wizard of SSMS, you need to specify the "Containment type" property value to "Partial" as shown below from its default value of "None":

Creating a database with Partial Containment
Figure 3 - Creating a database with Partial Containment

Once you have created a database with partial containment, you can create users in it, which will eventually be authenticated by this database rather than SQL Server instance on which this database resides. A user can be a Windows user as shown below, where you just need to specify the Windows user name. Next you need to give appropriate permissions to this user or Windows group this user belongs to so that he/she can perform required operations once connected to the database.

USE [CDA]
GO
CREATE USER   [ARSHAD-PC\ARSHAD-WA]
GO
ALTER ROLE   [db_datareader] ADD   MEMBER [ARSHAD-PC\ARSHAD-WA]
ALTER ROLE   [db_datawriter] ADD   MEMBER [ARSHAD-PC\ARSHAD-WA]
--ALTER ROLE   [db_ddladmin] ADD MEMBER [ARSHAD-PC\ARSHAD-WA]
GO

A user can be a SQL user as well, as shown below. In this case you need to specify the user name and password to be used for connecting to the database. Next, here also, you need to give appropriate permissions to this user to perform required operations in the database. For example, in the script below I am adding the user to db_datareader and db_datawriter so that the user can read from the database and write or use DML (Data Manipulation Language) commands.

USE [CDA]
GO
CREATE USER   [ARSHAD-SA] WITH   PASSWORD=N'asdf@1234', 
 DEFAULT_LANGUAGE=[English], 
 DEFAULT_SCHEMA=[dbo]
GO
 
ALTER ROLE   [db_datareader] ADD   MEMBER [ARSHAD-SA]
ALTER ROLE   [db_datawriter] ADD   MEMBER [ARSHAD-SA]
--ALTER ROLE   [db_ddladmin] ADD MEMBER [ARSHAD-SA]
GO

Now let’s try to connect to the database using the user account created above, which is expected to be authenticated by the database:

Connecting to database using Contained Database Authentication - 1
Figure 4 - Connecting to database using Contained Database Authentication - 1

Oops...what happened? What went wrong?

This is expected because even though the user has the access on the database, database authentication does not work by default. The reason is, you need to specifically specify the database name on which you want to connect or tell exactly which database to authenticate the user; for that go to the Connection Properties page as shown below and specify the name of the database and then try connecting again; this time it will work.

Connecting to database using Contained Database Authentication - 2
Figure 5 - Connecting to database using Contained Database Authentication - 2

Once successfully authenticated and connected to the database, the user will only see the database to which he/she has access and objects of the database whereas if the user is authenticated by the SQL Server instance, other instance level options will be visible as shown below:

Object Explorer view with Contained Database Authentication
Figure 6 - Object Explorer view with Contained Database Authentication

Object Explorer view with Instance Level Authentication
Figure 7 - Object Explorer view with Instance Level Authentication

Now coming to working on the database, the database authenticated user performs operations inside the database only; any operation on the instance level would not be allowed, not even the database backup. As you can see, you don’t the have option of Backup and Restore database when we right click on database:

Object Explorer does not allow operations outside database
Figure 8 - Object Explorer does not allow operations outside database

Okay, now if you remember we gave access only to read and write and if the user tries to execute DDL (Data Definition Language) to create an object in the database, it will fail with the following exception:

USE [CDA]
GO
CREATE TABLE   Employee
(
       [EmployeeID]   INT IDENTITY, 
       [FirstName]   Varchar(100), 
       [LastName]   Varchar(100), 
       [Address]   Varchar(100), 
)
GO

A database object can be created if the user has sufficient permissions - 1
Figure 9 - A database object can be created if the user has sufficient permissions - 1

A database object can be created if the user has sufficient permissions - 2
Figure 10 - A database object can be created if the user has sufficient permissions - 2

If you want the user to create database objects you need to make the user a member of db_ddladmin or db_owner role or else they will get this exception when they try to create a table.

The database authenticate user already has read/write permissions; he/she will be able to run these scripts without any problem.

USE [CDA]
GO
INSERT INTO   Employee([FirstName], [LastName], [Address]) 
VALUES ('Arshad', 'Ali', 'India') 
INSERT INTO   Employee([FirstName], [LastName], [Address]) 
VALUES ('John', 'Mathew', 'India') 
GO
SELECT *   FROM Employee
GO

Conclusion

In this article I talked about a new feature called Contained Database Authentication. The Contained Database Authentication feature allows the database to partially contain the data that used to previously be stored outside database, like logins etc., and eliminates the extra overhead of moving them as well during database migration.

Resources

Partially Contained Databases

See all articles by Arshad Ali



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM