December, 2010

To connect with contained user credentials you have to specify contained database in the connection string. If no database is specified the connection will try to do traditional authentication as a login in master database.  If the database does not support containment, then the user will be logged into master and then connect to the database (as it currently exists in shipping versions of SQL Server).

Note, that in SQL Server “Denali” we introduce Partially Contained databases. Partial Containment implies that some server dependences could still exist in such databases. As such, traditional users mapped to logins can coexist with new contained users in the same database and we support both Server level authentication (connecting with a login) and Database level authentication (connecting with a database user). Moreover, as users and logins don’t share the same namespace, there could be a situation when you have a login login1 in master database and contained user login1 in contained database – they are different entities and both may be able to connect to this contained database (let’s say the login login1 has a corresponding user login2 mapped to it in the contained database) under different circumstances. SQL Server must decide what kind of authentication it is –server level authentication or database level authentication.

Also a Windows Authentication user may or may not have a corresponding login and therefore the trusted connection may use server level authentication or database level authentication.

So, during the login process SQL Server must decide the type of authentication used for this connection. The following algorithm demonstrates how this is determined:

This algorithm has the following consequences: 

1.      For SQL Server Authentication, if a database is specified in connection string and the database is a contained, then database level authentication will first be attempted and if a matching contained user is not found, then authentication will fall back to the server level and will look for a matching login. 

2.      If based on the decision made in #1we proceed with database level authentication and password validation fails at the database, then we will terminate the connection and no fallback processing will be involved.

 3.      The consequence of #2 is that if you have contained database SQL Server user and a SQL Server Authenticated login in master having the same name and try to connect specifying contained database in the connection string you will always end up with database authentication regardless of the password (user’s or login’s) you are specifying. To be able to connect as a login, in this case, you will have to connect to master (or any non contained database) and then switch to the database using ‘USE db’ statement.

Note, that this is a not recommended scenario. Try to avoid such ambiguity to avoid possible confusions. 

For Window Authentication, if a login exists for the connecting principal, server level authentication logic will be followed.  If no Windows principal or group exists at the server level, the authentication will then proceed at the database level.

Also note, that previously existed user without login (Create user user_01 without login) is a different then contained user with password and cannot login the SQL Server.

   With the release of Microsoft SQL Server code-name “Denali” Community Technology Preview 1 (CTP1) and the introduction of Contained Database (CDB) (http://msdn.microsoft.com/en-us/library/ff929071(SQL.110).aspx ), we also introduced the capability of  database authentication (http://msdn.microsoft.com/en-us/library/ms173463(v=SQL.110).aspx , http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/03/contained-database-authentication-introduction.aspx, http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/04/contained-database-authentication-monitoring-and-controlling-contained-users.aspx).

  Since the configuration setting  that governs CDB & database authentication is a server scoped setting and the option to modify the containment property for a database is database -scoped; some DBAs may be wondering how to control which databases are allowed to authenticate users.

  Database authentication still fires logon triggers, therefore providing a server-scoped access control where the DBA can specify a policy based on the authentication information available. Below are a few of the tools you may find useful when creating logon triggers that are CDB-authentication ready.

  The information provided by sys.dm_exec_sessions has changed slightly to reflect this new authentication option. 

  A new column, authenticating_database_id has been added to sys.dm_exec_sessions that displays the database that authenticated the session:

·          When the session is an internal task, the value for this new column will be null

·          When t he session uses server-scoped authentication (i.e. T-SQL login, or Windows authentication with full server access), the value is 1 (i.e. the id of master database)

·         When the session is a CDB authenticated session, the value is the DB_ID of the authenticating database at the time of the authentication.

  Since the database -authenticated token doesn’t have any server-token information (i.e. there is no login), the suser_sname() and any error message referencing the login name (for example, when trying to access another database) will display the SID in string format, for example:

1> use db_test3

2> go

Msg 916, Level 14, State 1, Server RAULGA-VM03, Line 1

The server principal "S-1-9-3-3323865656-1154615280-1570172340-4238753615." is not able to access the database "db_test3" under the current security context.

   In order to find the user name used in the connection string, you can make use of another column from sys.dm_exec_sessions: original_login_name. This column should return the user name used in the connection string.

  It is very important to notice that all of these values are set for the session at the time the session was established, but may not reflect the current state of the server. For example, the user name for the principal may have changed, but the original_login_name column information would still reflect the name used during the authentication (The SID would still be the same in this case).

  Now, putting it all together, here is a simple example of a trigger that would restrict authentication based on the authentication DB_ID.

/***************************************************************

*

* Sample code for CDB authentication-aware logon trigger

*

* Author:   Raul Garcia

* Date:           11/12/2010

*

* This code is provided as-is and confers no rights or warranties.

* This code is based on a CTP version of SQL Server, which is considered a work in progress.

*

* Microsoft SQL Server code-name “Denali” Community Technology Preview 1 (CTP1)

* © 2010 Microsoft Corporation.

*

****************************************************************/

-- Since logon triggers are server-scoped objects,

-- we will create any necessary additional objects in master.

-- This would give DBA better control over these objects since

-- only privileged principals should have privileges to alter them

-- 

USE master

GO

CREATE TABLE [dbo].[t_logon_authentication_dbs](db_id bigint primary key);

GO

-- We want anyone to be able to access this data for read-only purposes

--

GRANT SELECT ON [dbo].[t_logon_authentication_dbs] TO public;

GO

-- Add the DB id for all of the DBs authorized to authenticate

-- including/excluding master DB

--

INSERT INTO [dbo].[t_logon_authentication_dbs] VALUES (db_id('master'));

GO

--   This logon trigger will verify the authenticating DB_ID and verify if

-- it matches one of the authorized DBs.

--   If it does, it allows the logon process to continue,

-- otherwise it will rollback, causing the session to terminate

--

CREATE TRIGGER trig_logon_db_authentication

ON ALL SERVER

FOR LOGON

AS

BEGIN

   DECLARE @dbid bigint;

   SELECT @dbid = authenticating_database_id FROM sys.dm_exec_sessions WHERE session_id = @@spid;

   IF NOT (EXISTS(SELECT * FROM [dbo].[t_logon_authentication_dbs] WHERE db_id = @dbid ))

   BEGIN

       ROLLBACK;

   END

END;

GO

------------------------------------------------

-- For demonstration purposes, we will create a partially contained DB where we will:

--   * Create a user with password

--   * Try to connect with this newly created user

CREATE DATABASE db_cdb_test CONTAINMENT = PARTIAL;

GO

USE db_cdb_test;

GO

CREATE USER user_test WITH PASSWORD = 'S0m3 P@ssw0rD! 4D3M0';

GO

/****************************************************************************

* Running from the command line:

*

>sqlcmd -S MyServer -U user_test -P "S0m3 P@ssw0rD! 4D3M0" -d db_cdb_test

Msg 17892, Level 14, State 1, Server MyServer, Line 1

Logon failed for login 'S-1-9-3-538751325-1104058235-1199607715-665140684.' due to trigger execution.

****************************************************************************/

-------------------------------------------------------------

-- DBA can add/remove DB IDs as necesary

-- In this case we will allow db_cdb_test to authenticate

--

USE master;

GO

INSERT INTO [dbo].[t_logon_authentication_dbs] VALUES (db_id('db_cdb_test'));

GO

/****************************************************************************

* Running from the command line:

*

>sqlcmd -S MyServer -U user_test -P "S0m3 P@ssw0rD! 4D3M0" -d db_cdb_test

1> SELECT user_name();

2> GO

--------------------------

user_test

(1 rows affected)

1>

****************************************************************************/

  Thanks to Sameer Tejani, Rick Byham for their feedback.

  -Raul Garcia
   SDE/T
   SQL Server Engine

Enabling contained database authentication on an instance allows db owners (and other privileged db users) to create and manage users who can connect to the database on the instance. However, the instance administrator (or other privileged server principal) may want to monitor database authentication – users and connections.

Here are some queries which should help monitor and control contained users from the instance level.

1.    Detect that contained database authentication is enabled at the instance:

 sp_configure 'show advanced', 1;

RECONFIGURE WITH OVERRIDE;

go

sp_configure 'contained database authentication';

go

2.    List of contained databases on the instance:

SELECT database_id, name, containment_desc FROM sys.databases

WHERE containment > 0;

3.      Users who can connect to the CDB. This includes all Windows users and groups, plus users with passwords in contained db (for example in db_Contained database):

SELECT principal_id, name, type_desc, authentication_type_desc

FROM db_Contained.sys.database_principals

WHERE authentication_type IN (2, 3);-- either user with password or Windows user\group

4.    Current database authenticated sessions:

SELECT es.session_id, es.login_time, es.original_login_name, db.name AS 'CDb name'

FROM sys.dm_exec_sessions AS es JOIN sys.databases AS db

ON es.authenticating_database_id = db.database_id AND es.authenticating_database_id > 1;

Note, that Authenticating DatabaseId in the sys.dm_exec_sessions DMV is the Id of the database where the user was authenticated. For Server level authentication this is always master (Id = 1).

Read more about database authentication in further posts and in Books Online .

In Microsoft SQL Server code-name “Denali” Community Technology Preview 1 (CTP1) we introduced the Contained Database (CDB) feature.

As the name suggests, self-contained database have no external dependencies. Contained databases can therefore be easily moved to another server and start working instantly without the need of any additional configuration.

One of the key features of a CDB is the ability to remove the reliance upon logins so that the database will become more portable. As a result the concept of Contained Users is introduced in SQL Server “Denali”.

A contained user is a user without a login in the master database which resides in a Contained Database and can connect to this database specifying its credentials in the connection string. For SQL Server Authentication Users, this implies that the password will have to be provided when such users are created; Windows Authentication Users can be created the same way they are traditionally created:

-- A member of the sysadmin fixed server role must explicitly enable contained database authentication on the instance of SQL Server

sp_configure 'show advanced', 1;

RECONFIGURE WITH OVERRIDE;

go

sp_configure 'contained database authentication', 1;

RECONFIGURE WITH OVERRIDE;

go

-- To create contained db you have to specify CONTAINMENT property

CREATE DATABASE db_Contained

CONTAINMENT = PARTIAL;

go

USE db_Contained;

go

-- Create a contained SQL Server Authentication user

CREATE USER usr_Contained

WITH PASSWORD = 'LJDUT9!@$';

go

-- Create a Windows Authentication user

CREATE USER [DOMAIN\User_01];

go

User that resides entirely within a database is considered contained. Such user can only connect to the database where they have been created, cannot change database and has virtually no permissions outside of this database.

 Note, that a Windows Authentication user created above could be contained or not-contained depending on the existence of a corresponding login DOMAIN\User_01. If the login exists, the newly created user is not contained and can go outside of the contained database where he may have some permissions associated to the corresponding login. If such a login doesn’t exist, the user is contained, has virtually no permissions outside of the database, and can only connect to the database where he resides. Therefore, a Windows Authentication user can change its containment behavior when a Windows Authentication login is created or dropped or when the database is moved to another instance of SQL Server where, again, such a login may or may not exist. However, typically it shouldn’t affect the application’s behavior because inside the database the user will possess the same permissions independently of its containment status. How a user connects and whether they get a full login or just a contained login will be covered in future post.

To connect with contained database user credentials you have to specify the contained database in the connection string. If no database is specified, the connection will attempt traditional authentication as a login in the master database.

Read more about database authentication in further posts.