Contained Database Authentication in depth

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.

Comments

• Anonymous
  December 22, 2010
  i accidentely denied the admin in window authentication mode .Now i want to grant i again.can you help me
• Anonymous
  December 22, 2010
  i accidentely denied the admin in window authentication mode .Now i want to grant i again.can you help me
• Anonymous
  December 24, 2010
   The following article describes how to recover from a scenario like the one you described: blogs.msdn.com/.../disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx . I hope this information helps,-Raul Garcia SDE/T SQL Server Engine
• Anonymous
  November 03, 2011
  looks like a nice feature for our environment. from all ive read about 2012 so far, looking forward to this release
• Anonymous
  February 04, 2016
  This is not my idea of "in depth", not one example? think you should change the title to 50,000ft overview