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

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

Posted via email from .NET Info