partially contained databases

PARTIALLY CONTAINED DATABASES

Steve Verschaeve

ABOUT ME

• Sr. SQL Server Consultant at KOHERA• Webmaster & board member at SQLUG.BE• Co-organiser at SQLServerDays.be• Microsoft Extended Expert Team member• MCP, MCTS, MCITP, MCT• steve@sqlug.be• Blog.steveverschaeve.be• @sql_lazywriter

AGENDA

• What is a (partially) contained database• Features within/outside Application Model• Authentication• Collation• Identifying database containment• Threats against partially contained databases• Demos• Resources• Q&A

WHAT IS A (PARTIALLY) CONTAINED DATABASE

• Scenario: Deploy to production; HA & DR

DB DB

Instance A Instance B

Backup/Copy/Restore

LoginsLinked ServersAgent jobs…

?

WHAT IS A (PARTIALLY) CONTAINED DATABASE

• Improved dependency management• Include all settings + metadata• No login authentication at database engine level• Isolated from the database engine• Improved transition between environments

• Not yet fully contained• Moving to SQL Azure• Fully contained• Uncontained features disabled

by default

• All SQL Server editions

FEATURES WITHIN/OUTSIDE APPLICATION MODEL

Within the Application Model [1]Contained

Outside the Application Model [2]Non-Contained

System Viewssys.indexes, sys.types, …

Catalog Viewssys.servers, sys.server_role_members…

Data TypesAll data types excluding CLR data types

T-SQLBackup, Restore, Set Ansi_Nulls, …

Dynamic Management Viewssys.dm_db_uncontained_entities

Built-in Functions@@servername, loginproperty, …

T-SQLHaving, Rollback Transaction, …

System Functionssys.fn_get_sql, sys.fn_cdc_get_min_lsn, ...

Built-in Functions@@rowcount, Getdate, IsNull, …

OtherLinked servers, Full-Text Search, Synonyms, …

System Stored Proceduressp_helptext, sp_columns, sp_addrole, …

Replication, Change data capture, Change tracking

DBCC StatementsCHECKDB, SHOW_STATISTICS, …

ENABLE PARTIALLY CONTAINED DATABASES

• Instance level

EXEC sys.sp_configure N’contained database authentication’,N’1’;GO

• Database level

CREATE DATABASE [PartialCDB] CONTAINMENT = PARTIAL [NONE];GO

• New syntax

ALTER DATABASE CURRENT ...

DEMO

Enable & Convert non-contained DB to Partial-CDB

AUTHENTICATION

• Contained users connect without server level authenticating• Contained SQL User with password syntax

CREATE USER Giselle WITH PASSWORD = ‘xyz’;GO

• Multiple users with same name for different databases• Normal users tied to login coexist with

contained users in same database [1]

DEMO

• Authentication

COLLATION

• Two types of collation: DATABASE_DEFAULT & CATALOG_DEFAULT• New catalog collation Latin1_General_100_CI_AS_WS_KS• Syntax

CREATE TABLE T1 (Name nvarchar(max) COLLATE CATALOG_DEFAULT);GO

• Same collation for all contained databases and instances• Cannot be changed

COLLATION

Item Non-Contained Database Contained Database

User data (default)

DATABASE_DEFAULT DATABASE_DEFAULT

Temp Data (default)

TempDB Collation DATABASE_DEFAULT

Metadata DATABASE_DEFAULT / CATALOG_DEFAULT CATALOG_DEFAULT

Temp Metadata TempDB Collation CATALOG_DEFAULT

Variables Instance Collation CATALOG_DEFAULT

Goto Labels Instance Collation CATALOG_DEFAULT

Cursor Names Instance Collation CATALOG_DEFAULT

DEMO

• Collation

IDENTIFYING DATABASE CONTAINMENT

• Sys.dm_db_uncontained_entities• View• Potentially uncontained entities• Static

• Cdb_uncontained_usage • Extended Event• When uncontained entity is detected and identified at run time• Dynamic

DEMO

• Sys.dm_db_uncontained_entities• Cdb_uncontained_usage

THREATS AGAINST PART. CONTAINED DATABASES

• Who can change containment settings• Users in a converted DB can create new users with password• Prevent a DB from being contained• Prevent connections from users with passwords• No rechecked passwords• Users with password cannot use Kerberos authentication• Offline dictionary attack• Auto_Close database property

http://specialops.sqlpass.org

RESOURCES

• SQL Server v.Next(Denali): Contained Databases (Aaron Bertrand)• SQL Server 2012: Sometimes Partial Is Preferable (Denny Ch

erry)• Partially Contained Databases (TechNet)• SQL Server 2012 Partially Contained Databases (Steve Versc

haeve)• Contained Database Authentication in depth (Lyudmila Fokin

a)

Q&A

THANK YOUsteve@sqlug.beBlog.steveverschaeve.be@sql_lazywriter

© 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after

the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.