包含的数据库用户 - 使数据库可移植Contained Database Users - Making Your Database Portable

适用于: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: YesSQL Server YesAzure SQL Database YesAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

使用包含的数据库用户在数据库级别对 SQL ServerSQL ServerSQL 数据库SQL Database 连接进行身份验证。Use contained database users to authenticate SQL ServerSQL Server and SQL 数据库SQL Database connections at the database level. “包含的数据库”是独立于其他数据库以及承载数据库的 SQL ServerSQL Server/ SQL 数据库SQL Database 实例(和 master 数据库)的一种数据库。A contained database is a database that is isolated from other databases and from the instance of SQL ServerSQL Server/ SQL 数据库SQL Database (and the master database) that hosts the database. SQL ServerSQL Server 支持包含的数据库用户进行 Windows 和 SQL ServerSQL Server 身份验证。supports contained database users for both Windows and SQL ServerSQL Server authentication. 使用 SQL 数据库SQL Database时,将包含的数据库用户与数据库级别防火墙规则相结合。When using SQL 数据库SQL Database, combine contained database users with database level firewall rules. 本主题介绍与传统的登录名/用户模型和 Windows 或服务器级别防火墙规则相比,使用包含的数据库模型的差异和好处。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.

备注

随着 MicrosoftMicrosoft 发展 SQL 数据库SQL Database 服务并转向更有保证的 SLA,你可能需要切换到包含的数据库用户模型和数据库范围防火墙规则,以针对给定数据库获得更高可用性的 SLA 和更高的最大登录率。As MicrosoftMicrosoft evolves the SQL 数据库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. MicrosoftMicrosoft 建议立即考虑进行这类更改。encourage you to consider such changes today.

传统的登录名和用户模型Traditional Login and User Model

在传统的连接模型中,通过提供由 Windows 进行身份验证的用户或组凭据,Windows 用户或 Windows 组成员可连接到 数据库引擎Database EngineIn 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. 也可以同时提供名称和密码,并通过使用 SQL ServerSQL Server 身份验证进行连接。Or you can provide both a name and password and connects by using SQL ServerSQL Server authentication. 在这两种情况下,master 数据库必须拥有匹配连接凭据的登录名。In both cases, the master database must have a login that matches the connecting credentials. 数据库引擎Database Engine 确认了 Windows 身份验证凭据或验证了 SQL ServerSQL Server 身份验证凭据之后,该连接通常会尝试连接到用户数据库。After the 数据库引擎Database Engine confirms the Windows authentication credentials or authenticates the SQL ServerSQL 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. 连接字符串还可以指定连接到特定数据库,该数据库在 SQL ServerSQL Server 中为可选但在 SQL 数据库SQL Database中为必需。The connection string may also specify connecting to a specific database which is optional in SQL ServerSQL Server but required in SQL 数据库SQL Database.

重要原则是登录名(在 master 数据库中)和用户(在用户数据库中)必须存在,并且彼此相关。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. 这意味着到用户数据库的连接依赖于 master 数据库中的登录名,并且这限制了将数据库移动到其他托管 SQL ServerSQL ServerAzure SQL 数据库Azure SQL Database 服务器的功能。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 ServerSQL Server or Azure SQL 数据库Azure SQL Database server. 而且,如果由于任何原因,到 master 数据库的连接不可用(例如,进程中出现故障),整个连接时间将会增加,或者连接可能超时。因此,这可能会降低连接可伸缩性。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

在包含的数据库用户模型中,master 数据库中不存在登录名。In the contained database user model, the login in the master database is not present. 相反,身份验证过程发生在用户数据库中,并且用户数据库中的数据库用户在 master 数据库中没有关联的登录名。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. 包含的数据库用户模型支持 Windows 身份验证和 SQL ServerSQL Server 身份验证,并且可以在 SQL ServerSQL ServerSQL 数据库SQL Database中使用。The contained database user model supports both Windows authentication and SQL ServerSQL Server authentication, and can be used in both SQL ServerSQL Server and SQL 数据库SQL Database. 若要作为包含的数据库用户进行连接,连接字符串必须始终包含用户数据库的参数,以便 数据库引擎Database Engine 知道哪个数据库负责管理身份验证过程。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. 若要更改数据库, SQL 数据库SQL Database 用户必须创建一个新的连接。To change databases, SQL 数据库SQL Database users must create a new connection. 如果另一个数据库中存在相同的用户, SQL ServerSQL Server 中的包含的数据库用户可以更改数据库。Contained database users in SQL ServerSQL Server can change databases if an identical user is present in another database.

Azure: SQL 数据库SQL DatabaseSQL 数据仓库SQL Data Warehouse 支持将 Azure Active Directory 标识作为包含的数据库用户。Azure: SQL 数据库SQL Database and SQL 数据仓库SQL Data Warehouse support Azure Active Directory identities as contained database users. SQL 数据库SQL Database 支持包含的数据库用户使用 SQL ServerSQL Server 身份验证,而 SQL 数据仓库SQL Data Warehouse 不支持。supports contained database users using SQL ServerSQL Server authentication, but SQL 数据仓库SQL Data Warehouse does not. 有关详细信息,请参阅使用 Azure Active Directory 身份验证连接到 SQL 数据库For more information, see Connecting to SQL Database By Using Azure Active Directory Authentication. 使用 Azure Active Directory 身份验证时,可以使用 Active Directory 通用身份验证建立来自 SSMS 的连接。When using Azure Active Directory authentication, connections from SSMS can be made using Active Directory Universal Authentication. 管理员将通用身份验证配置为需要多重身份验证,这会使用电话呼叫、短信、智能卡 pin 或移动应用通知来验证身份。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. 有关详细信息,请参阅 SQL 数据库和 SQL 数据仓库针对 Azure AD MFA 的 SSMS 支持For more information, see SSMS support for Azure AD MFA with SQL Database and SQL Data Warehouse.

对于 SQL 数据库SQL DatabaseSQL 数据仓库SQL Data Warehouse,因为连接字符串中始终需要数据库名称,所以在从传统模型切换到包含的数据库用户模型时,无需更改连接字符串。For SQL 数据库SQL Database and SQL 数据仓库SQL Data Warehouse, 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. 对于 SQL ServerSQL Server 连接,如果数据库名称尚不存在,它将必须添加到连接字符串。For SQL ServerSQL 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. 在使用包含的数据库模型时,数据库所有者和具有 ALTER ANY USER 权限的数据库用户可以授予对数据库的访问权限。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 ServerSQL Server

Windows 防火墙规则适用于所有连接,并且对登录名(传统模型连接)和包含的数据库用户具有相同影响。Windows firewall rules apply to all connections and have the same effects on logins (traditional model connections) and contained database users. 有关 Windows 防火墙的详细信息,请参阅 为数据库引擎访问配置 Windows 防火墙For more information about the Windows firewall, see Configure a Windows Firewall for Database Engine Access.

SQL 数据库SQL Database 防火墙Firewalls

SQL 数据库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. 如果没有允许访问数据库的规则,则检查服务器级别防火墙规则,这将需要对 SQL 数据库服务器 master 数据库的访问权限。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. 与包含的数据库用户相结合的数据库级别防火墙规则可以无需在连接过程中访问服务器的 master 数据库,从而提供改进的连接可伸缩性。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.

有关 SQL 数据库SQL Database 防火墙规则的详细信息,请参阅以下主题:For more information about SQL 数据库SQL Database firewall rules, see the following topics:

语法差异Syntax Differences

传统模型Traditional model 包含的数据库用户模型Contained database user model
连接到 master 数据库时: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
要更改密码,在 master 数据库的上下文中: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

在包含的数据库的上下文中,Azure SQL 数据库托管实例的行为类似于本地 SQL Server。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

  • SQL ServerSQL Server中,必须为 SQL ServerSQL Server的实例启用包含的数据库用户。In SQL ServerSQL Server, contained database users must be enabled for the instance of SQL ServerSQL Server. 有关详细信息,请参阅 contained database authentication Server Configuration OptionFor more information, see contained database authentication Server Configuration Option.
  • 具有非重叠名称的包含的数据库用户和登录名可以在应用程序中共存。Contained database users and logins with non-overlapping names can co-exist in your applications.
  • 如果 master 数据库中有一个名为 name1 的登录名,并且你创建一个名为 name1的包含的数据库用户,当在连接字符串中提供数据库名称时,将在连接到数据库的情况下提取登录上下文上的数据库用户上下文。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.
  • SQL 数据库SQL Database 中,包含的数据库用户的名称不能与服务器管理员帐户的名称相同。In SQL 数据库SQL Database the name of contained database user cannot be the same as the name of the server admin account.
  • SQL 数据库SQL Database 服务器管理员帐户绝不能是包含的数据库用户。The SQL 数据库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) CREATE USER (Transact-SQL)
使用 Azure Active Directory 身份验证连接到 SQL 数据库Connecting to SQL Database By Using Azure Active Directory Authentication