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.
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 you can provide both a name and password and connects by using SQL Server authentication. 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 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 and SQL Server authentication, and can be used 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.
Azure: SQL Database and Azure Synapse Analytics support Azure Active Directory identities as contained database users. SQL Database supports contained database users using SQL Server authentication, but Azure Synapse Analytics does not. For more information, see Connecting to SQL Database By Using Azure Active Directory Authentication. When using Azure Active Directory authentication, connections from SSMS can be made using Active Directory Universal Authentication. Administrators can configure Universal Authentication to require Multi-Factor Authentication, which verifies identity by using a phone call, text message, smart card with pin, or mobile app notification. For more information, see SSMS support for Azure AD MFA with SQL Database and Azure Synapse Analytics.
For SQL Database and Azure Synapse Analytics, since the database name is always required in the connection string, 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.
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.
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 SQL Database 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)
|Traditional model||Contained database user model|
|When connected to the master database:
Then when connected to a user database:
|When connected to a user database:
|Traditional model||Contained database user model|
|To change password, in context of master DB:
||To change password, in context of user DB:
Azure SQL Managed Instance behaves like SQL Server on-premises in the context of contained databases. Be sure to change the context of your database from the master database to the user database when creating your contained user. Additionally, there should be no active connections to the user database when setting the containment option.
Before you run the following script, be sure that no other connections are active on your Managed Instance database. The script might disrupt other processes that are running on the database.
Use MASTER; GO ALTER DATABASE Test SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE Test SET containment=partial; ALTER DATABASE Test SET MULTI_USER; USE Test; GO CREATE USER Carlo WITH PASSWORD='Enterpwdhere*' SELECT containment_desc FROM sys.databases WHERE name='Test'
- 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.