The dependency of database-specific users upon server-based logins poses a challenge when you need to move or restore a database to another server. Although the users move with the database, their associated logins do not, and thus the relocated database will not function properly until you also setup and map the necessary logins on the target server. To resolve these types of dependency problems and help make databases more easily portable, SQL Server 2012 introduces “partially contained” databases.
The term “partially contained” is based on the fact that SQL Server itself merely enables containment—it does not enforce it. It’s still your job to actually implement true containment. From a security perspective, this means that partially contained databases allow you to create a special type of user called a contained user. The contained user’s password is stored right inside the contained database, rather than being associated with a login defined at the server instance level and stored in the master database. Then, unlike the standard SQL Server authentication model, contained users are authenticated directly against the credentials in the contained database without ever authenticating against the server instance. Naturally, for this to work, a connection string with a contained user’s credentials must include the Initial Catalog keyword that specifies the contained database name.
Creating a Partially Contained Database
To create a partially contained database, first enable the contained database authentication setting by calling sp_configure and then issue a CREATE DATABASE statement with the new CONTAINMENT=PARTIAL clause as the following code demonstrates.
-- Enable database containment USE master GO EXEC sp_configure 'contained database authentication', 1 RECONFIGURE -- Delete database if it already exists IF EXISTS(SELECT name FROM sys.databases WHERE name = 'MyDB') DROP DATABASE MyDB GO -- Create a partially contained database CREATE DATABASE MyDB CONTAINMENT = PARTIAL GO USE MyDB GO
To reiterate, SQL Server doesn’t enforce containment. You can still break containment by creating ordinary database users for server-based logins. For this reason, it’s easy to convert an ordinary (uncontained) database to a partially contained database; simply issue an ALTER DATABASE statement and specify SET CONTAINMENT=PARTIAL. You’ll then be able to migrate the server-based logins to contained logins and achieve server independence.
Creating a Contained User
Once you have a contained database, you can create a contained user for it by issuing a CREATE USER statement and specifying WITH PASSWORD, as shown here:
CREATE USER UserWithPw WITH PASSWORD = N'password$1234'
This syntax is valid only for contained databases; SQL Server will raise an error if you attempt to create a contained user in the context of an uncontained database.
That’s all there is to creating partially contained databases with contained users. The only remaining point that’s worth calling out is that an Initial Catalog clause pointing to a partially contained database must be specified explicitly in a connection string that also specifies the credentials of a contained user in that database. If just the credentials are specified without the database, SQL Server will not scan the partially contained databases hosted on the instance for one that has a user with matching credentials. Instead, it will consider the credentials to be those of an ordinary SQL Server login, and will not authenticate against the contained database.
Other Partially Contained Database Features
Aside from server-based logins, there are many other dependencies that a database might have on its hosted instance. These include linked servers, SQL CLR, database mail, service broker objects, endpoints, replication, SQL Server Agent jobs, and tempdb collation. All these objects are considered to be uncontained entities since they all exist outside the database.
Uncontained entities threaten a database’s portability. Since these objects are all defined at the server instance level, behavior can vary unpredictably when databases are shuffled around from one instance to another. Let’s examine features to help you achieve the level of containment and stability that your circumstances require.
Uncontained Entities View
SQL Server provides a new data management view (DMV) called sys.dm_db_uncontained_entities that you can query on to discover potential threats to database portability. This DMV not only highlights dependent objects, it will even report the exact location of all uncontained entity references inside of stored procedures, views, functions, and triggers.
The following code creates a few stored procedures, and then joins sys.dm_db_uncontained_entities with sys.objects to report the name of all objects having uncontained entity references in them.
-- Create a procedure that references a database-level object CREATE PROCEDURE GetTables AS BEGIN SELECT * FROM sys.tables END GO -- Create a procedure that references an instance-level object CREATE PROCEDURE GetEndpoints AS BEGIN SELECT * FROM sys.endpoints END GO -- Identify objects that break containment SELECT UncType = ue.feature_type_name, UncName = ue.feature_name, RefType = o.type_desc, RefName = o.name, Stmt = ue.statement_type, Line = ue.statement_line_number, StartPos = ue.statement_offset_begin, EndPos = ue.statement_offset_end FROM sys.dm_db_uncontained_entities AS ue INNER JOIN sys.objects AS o ON o.object_id = ue.major_id
Here is the result of the query:
UncType UncName RefType RefName Stmt Line StartPos EndPos ----------- --------- -------------------- ------------ ------ ---- -------- --- System View endpoints SQL_STORED_PROCEDURE GetEndpoints SELECT 5 218 274
The DMV identifies the stored procedure GetEndpoints as an object with an uncontained entity reference. Specifically, the output reveals that a stored procedure references the sys.endpoints view in a SELECT statement on line 5 at position 218. This alerts you to a database dependency on endpoints configured at the server instance level that could potentially pose an issue for portability. The GetTables stored procedure does not have any uncontained entity references (sys.tables is contained), and is therefore not reported by the DMV.
Collations and tempdb
Ordinarily, all databases hosted on the same SQL Server instance share the same tempdb database for storing temporary tables, and all the databases (including tempdb) on the instance use the same collation setting (collation controls string data character set, case sensitivity, and accent sensitivity). When joining between regular database tables and temporary tables, both your user database and tempdb must use a compatible collation. This, again, represents an instance-level dependency with respect to the fact that the collation setting can vary from one server instance to another. Thus, problems arise when moving databases between servers that have different collation settings for tempdb. The code below demonstrates the problem, and how to avoid it by using a contained database.
-- Create an uncontained database with custom collation USE master GO IF EXISTS(SELECT name FROM sys.databases WHERE name = 'MyDB') DROP DATABASE MyDB GO CREATE DATABASE MyDB COLLATE Chinese_Simplified_Pinyin_100_CI_AS GO USE MyDB GO -- Create a table in MyDB (uses Chinese_Simplified_Pinyin_100_CI_AS collation) CREATE TABLE TestTable (TextValue nvarchar(max)) -- Create a temp table in tempdb (uses SQL_Latin1_General_CP1_CI_AS collation) CREATE TABLE #TempTable (TextValue nvarchar(max)) -- Fails, because MyDB and tempdb uses different collation SELECT * FROM TestTable INNER JOIN #TempTable ON TestTable.TextValue = #TempTable.TextValue -- Convert to a partially contained database DROP TABLE #TempTable USE master ALTER DATABASE MyDB SET CONTAINMENT=PARTIAL GO USE MyDB GO -- Create a temp table in MyDB (uses Chinese_Simplified_Pinyin_100_CI_AS collation) CREATE TABLE #TempTable (TextValue nvarchar(max)) -- Succeeds, because the table in tempdb now uses the same collation as MyDB SELECT * FROM TestTable INNER JOIN #TempTable ON TestTable.TextValue = #TempTable.TextValue -- Cleanup DROP TABLE #TempTable USE master DROP DATABASE MyDB GO
This code first creates an uncontained database that uses Chinese_Simplified_Pinyin_100_CI_AS collation on a server instance that uses (the default) SQL_Latin1_General_CP1_CI_AS collation. The code then creates a temporary table and then attempts to join an ordinary database table against it. The attempt fails because the two tables have different collations (that is, they each reside in databases that use different collations), and SQL Server issues the following error message:
Msg 468, Level 16, State 9, Line 81 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Chinese_Simplified_Pinyin_100_CI_AS" in the equal to operation.
Then the code issues an ALTER DATABASE…SET CONTAINMENT=PARTIAL statement to convert the database to a partially contained database. As a result, SQL Server resolves the conflict by collating the temporary table in tempdb in the same collation as the contained database, and the second join attempt succeeds.
Partially contained databases in SQL Server 2012 go a long way helping to improve the portability of databases across servers and instances. In this blog post, I demonstrated how to create a partially contained database with contained users, how to deal with collation issues, and how to use the new data management view to discover threats to containment and identify external dependencies. These capabilities are welcome news for SQL Server DBAs everywhere. Enjoy!