Skip to content

Latest commit

 

History

History
113 lines (77 loc) · 12.9 KB

contained-database-users-making-your-database-portable.md

File metadata and controls

113 lines (77 loc) · 12.9 KB
title description ms.custom ms.date ms.prod ms.prod_service ms.reviewer ms.technology ms.topic helpviewer_keywords ms.assetid author ms.author monikerRange
Contained user access to contained databases
Learn how to configure contained user access for contained databases, and the differences between a traditional login/user model.
seo-lt-2019
01/28/2019
sql
database-engine, sql-database, sql-data-warehouse, pdw
security
conceptual
contained database, users
user [SQL Server], about contained database users
e57519bb-e7f4-459b-ba2f-fd42865ca91d
VanMSFT
vanto
=azuresqldb-current||>=sql-server-2016||=azure-sqldw-latest||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Contained Database Users - Making Your Database Portable

[!INCLUDEappliesto-ss-asdb-asdw-xxx-md]

Use contained database users to authenticate [!INCLUDEssNoVersion] and [!INCLUDEssSDS] connections at the database level. A contained database is a database that is isolated from other databases and from the instance of [!INCLUDEssNoVersion]/ [!INCLUDEssSDS] (and the master database) that hosts the database. [!INCLUDEssNoVersion] supports contained database users for both Windows and [!INCLUDEssNoVersion] authentication. When using [!INCLUDEssSDS], 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 [!INCLUDEmsCoName] evolves the [!INCLUDEssSDS] 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. [!INCLUDEmsCoName] 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 [!INCLUDEssDE] by providing user or group credentials authenticated by Windows. Or you can provide both a name and password and connects by using [!INCLUDEssNoVersion] authentication. In both cases, the master database must have a login that matches the connecting credentials. After the [!INCLUDEssDE] confirms the Windows authentication credentials or authenticates the [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] but required in [!INCLUDEssSDS].

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 [!INCLUDEssNoVersion] or [!INCLUDEssSDSFull] 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 [!INCLUDEssNoVersion] authentication, and can be used in both [!INCLUDEssNoVersion] and [!INCLUDEssSDS]. To connect as a contained database user, the connection string must always contain a parameter for the user database so that the [!INCLUDEssDE] 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, [!INCLUDEssSDS] users must create a new connection. Contained database users in [!INCLUDEssNoVersion] can change databases if an identical user is present in another database.

Azure: [!INCLUDEsqldbesa] and [!INCLUDEssSDW_md] support Azure Active Directory identities as contained database users. [!INCLUDEssSDS_md] supports contained database users using [!INCLUDEssNoVersion_md] authentication, but [!INCLUDEssSDW_md] 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 SQL Data Warehouse.

For [!INCLUDEssSDS] and [!INCLUDEssSDW_md], 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 [!INCLUDEssNoVersion] 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

[!INCLUDEssNoVersion]

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.

[!INCLUDEssSDS] Firewalls

[!INCLUDEssSDS] 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 [!INCLUDEssSDS] firewall rules, see the following topics:

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';

Managed instance

An Azure SQL Database 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.

For example:

Use MASTER;
GO 

ALTER DATABASE Test
SET containment=partial


USE Test;  
GO  
CREATE USER Carlo  
WITH PASSWORD='Enterpwdhere*'  


SELECT containment_desc FROM sys.databases
WHERE name='test'

Remarks

  • In [!INCLUDEssNoVersion], contained database users must be enabled for the instance of [!INCLUDEssNoVersion]. 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 [!INCLUDEssSDS] the name of contained database user cannot be the same as the name of the server admin account.
  • The [!INCLUDEssSDS] 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)
Connecting to SQL Database By Using Azure Active Directory Authentication