Contained Database Users - Making Your Database Portable

Use contained database users to authenticate SQL Server and SQL Database connections at the database level. A contained database is a database that is isolated from other databases and from the instance of SQL Server/SQL Database (and the master database) that hosts the database. SQL Server supports contained database users for both Windows and SQL Server authentication. When using SQL Database, combine contained database users with database level firewall rules. This topic reviews the differences and benefits of using the contained database model compared to traditional login/user model and Windows or server-level firewall rules. Specific scenarios, manageability or application business logic may still require use of traditional login/user model and server-level firewall rules.

Note

As Microsoft evolves the SQL Database service and moves towards higher guaranteed SLAs you may be required to switch to the contained database user model and database-scoped firewall rules to attain the higher availability SLA and higher max login rates for a given database. Microsoft encourage you to consider such changes today.

Traditional Login and User Model

In the traditional connection model, Windows users or members of Windows groups connect to the Database Engine by providing user or group credentials authenticated by Windows. Or the connection provides both a name and password and connects by using SQL Server authentication (which is the only option when connecting to SQL Database). In both cases, the master database must have a login that matches the connecting credentials. After the Database Engine confirms the Windows authentication credentials or authenticates the SQL Server authentication credentials, the connection typically attempts to connect to a user database. To connect to a user database, the login must be able to be mapped to (that is, associated with) a database user in the user database. The connection string may also specify connecting to a specific database which is optional in SQL Server but required in SQL Database.

The important principal is that both the login (in the master database) and the user (in the user database) must exist and be related to each other. This means that the connection to the user database has a dependency upon the login in the master database, and this limits the ability of the database to be moved to a different hosting SQL Server or Azure SQL Database server. And if, for any reason, a connection to the master database is not available (for example, a failover is in progress), the overall connection time will be increased or connection might time out. Consequently this may reduce connection scalability.

Contained Database User Model

In the contained database user model, the login in the master database is not present. Instead, the authentication process occurs at the user database, and the database user in the user database does not have an associated login in the master database. The contained database user model supports both Windows authentication (in SQL Server) and SQL Server authentication (in both SQL Server and SQL Database). To connect as a contained database user, the connection string must always contain a parameter for the user database so that the Database Engine knows which database is responsible for managing the authentication process. The activity of the contained database user is limited to the authenticating database, so when connecting as a contained database user, the database user account must be independently created in each database that the user will need. To change databases, SQL Database users must create a new connection. Contained database users in SQL Server can change databases if an identical user is present in another database.

For SQL Database, no changes are required to the connection string when switching from the traditional model to the contained database user model. For SQL Server connections, the name of the database must be added to the connection string, if it is not already present.

Important

When using the traditional model, the server level roles and server level permissions can limit access to all databases. When using the contained database model, database owners and database users with the ALTER ANY USER permission can grant access to the database. This reduces the access control of high privileged server logins and expands the access control to include high privileged database users.

Firewalls

SQL Server

Windows firewall rules apply to all connections and have the same effects on logins (traditional model connections) and contained database users. For more information about the Windows firewall, see Configure a Windows Firewall for Database Engine Access.

SQL Database Firewalls

SQL Database allows separate firewall rules for server level connections (logins) and for database level connections (contained database users). When connecting to a user database, first database firewall rules are checked. If there is no rule that allows access to the database, the server level firewall rules are checked, which requires access to the logical server master database. Database level firewall rules combined with contained database users can eliminate necessity to access master database of the server during connection providing improved connection scalability.

For more information about SQL Database firewall rules, see the following topics:

• Azure SQL Database Firewall

• How to: Configure Firewall Settings (Azure SQL Database)

• sp_set_firewall_rule (Azure SQL Database)

• sp_set_database_firewall_rule (Azure SQL Database)

Syntax Differences

Traditional model	Contained database user model
When connected to the master database: CREATE LOGIN login_name WITH PASSWORD = 'strong_password'; Then when connected to a user database: CREATE USER 'user_name' FOR LOGIN 'login_name';	When connected to a user database: CREATE USER user_name WITH PASSWORD = 'strong_password';

Traditional model	Contained database user model
To change password, in context of master DB: ALTER LOGIN login_name WITH PASSWORD = 'strong_password';	To change password, in context of user DB: ALTER USER user_name WITH PASSWORD = 'strong_password';

Remarks

• In SQL Server, contained database users must be enabled for the instance of SQL Server. For more information, see contained database authentication Server Configuration Option.

• Contained database users and logins with non-overlapping names can co-exist in your applications.

• If there is a login in master database with the name name1 and you create a contained database user named name1, when a database name is provided in the connection string, the context of the database user will be picked over login context when connecting to the database. That is, contained database user will take precedence over logins with the same name.

• In SQL Database the name of contained database user cannot be the same as the name of the server admin account.

• The SQL Database server admin account can never be a contained database user. The server admin has sufficient permissions to create and manage contained database users. The server admin can grant permissions to contained database users on user databases.

• Since contained database users are database level principals, you need to create contained database users in every database that you would use them. The identity is confined to the database and is independent in all aspects from a user with same name and same password in another database in the same server.

• Use the same strength passwords that you would normally use for logins.

See Also

Contained Databases
Security Best Practices with Contained Databases
CREATE USER (Transact-SQL)