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.
- 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
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:
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":
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:
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.
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:
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:
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
Figure 9 - A database object can be created if the user has sufficient permissions - 1
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
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.