Contained Database Authentication: How to control which databases are allowed to authenticate users using logon tri...

[raulgarciamsft]

First published on MSDN on Dec 06, 2010

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-introd... , http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/04/contained-database-authentication-monito... ).

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