针对异地还原或故障转移配置和管理 Azure SQL 数据库的安全性Configure and manage Azure SQL Database security for geo-restore or failover

适用于:是Azure SQL 数据库 APPLIES TO: yesAzure SQL Database

本文介绍了配置和控制活动异地复制自动故障转移组的身份验证要求。This article describes the authentication requirements to configure and control active geo-replication and auto-failover groups. 它还介绍了设置用户对辅助数据库的访问权的步骤。It also provides the steps required to set up user access to the secondary database. 最后它还介绍了如何在使用异地还原后启用对已恢复数据库的访问权限。Finally, it also describes how to enable access to the recovered database after using geo-restore. 有关恢复选项的详细信息,请参阅业务连续性概述For more information on recovery options, see Business Continuity Overview.

使用包含的用户进行灾难恢复Disaster recovery with contained users

不同于必须映射到 master 数据库中登录名的传统用户,包含的用户完全由数据库自身管理。Unlike traditional users, which must be mapped to logins in the master database, a contained user is managed completely by the database itself. 这带来了两个好处。This has two benefits. 在灾难恢复方案中,用户可以继续连接到新的主数据库或使用异地还原恢复的数据库,不需进行任何额外的配置,因为数据库会管理用户。In the disaster recovery scenario, the users can continue to connect to the new primary database or the database recovered using geo-restore without any additional configuration, because the database manages the users. 从登录的立场来看,此配置还有潜在的缩放性和性能优势。There are also potential scalability and performance benefits from this configuration from a login perspective. 有关详细信息,请参阅包含的数据库用户 - 使数据库可移植For more information, see Contained Database Users - Making Your Database Portable.

主要的不足是,在规模较大的情况下,管理灾难恢复过程更具挑战性。The main trade-off is that managing the disaster recovery process at scale is more challenging. 当有多个使用同一登录名的数据库时,在多个数据库中使用包含用户来维护凭据可能会抵消包含用户的好处。When you have multiple databases that use the same login, maintaining the credentials using contained users in multiple databases may negate the benefits of contained users. 例如,密码轮换策略要求在多个数据库中进行一致性的更改,而不是在 master 数据库中更改登录名的密码一次。For example, the password rotation policy requires that changes be made consistently in multiple databases rather than changing the password for the login once in the master database. 因此,如果多个数据库使用同一用户名和密码,则不建议使用包含用户。For this reason, if you have multiple databases that use the same user name and password, using contained users is not recommended.

如何配置登录名和用户How to configure logins and users

如果要使用登录名和用户(而不是包含用户),必须采取额外的步骤以确保相同的登录名存在于 master 数据库中。If you are using logins and users (rather than contained users), you must take extra steps to ensure that the same logins exist in the master database. 以下部分概述了相关的步骤和其他注意事项。The following sections outline the steps involved and additional considerations.


还可以使用 Azure Active Directory (AAD) 登录来管理数据库。It is also possible to use Azure Active Directory (AAD) logins to manage your databases. 有关详细信息,请参阅 Azure SQL 登录和用户For more information, see Azure SQL logins and users.

设置对辅助数据库或已恢复数据库的用户访问权限Set up user access to a secondary or recovered database

为了确保能够将辅助数据库用作只读辅助数据库,以及确保使用异地还原对新的主数据库或已恢复数据库进行适当的访问,必须在恢复之前对目标服务器的 master 数据库进行适当的安全配置。In order for the secondary database to be usable as a read-only secondary database, and to ensure proper access to the new primary database or the database recovered using geo-restore, the master database of the target server must have the appropriate security configuration in place before the recovery.

本主题稍后部分介绍了各步骤所需的特定权限。The specific permissions for each step are described later in this topic.

应该在配置异地复制的过程中进行用户访问权限方面的准备,以便用户能够访问异地复制辅助数据库。Preparing user access to a geo-replication secondary should be performed as part configuring geo-replication. 只要原始服务器处于联机状态(例如,在进行 DR 钻取时),就可以进行用户访问权限方面的准备,使用户能够访问异地还原数据库。Preparing user access to the geo-restored databases should be performed at any time when the original server is online (e.g. as part of the DR drill).


如果在故障转移到或异地还原到某个服务器时,该服务器没有正确配置的登录名,则将限制为只有服务器管理员帐户能够访问它。If you fail over or geo-restore to a server that does not have properly configured logins, access to it will be limited to the server admin account.

在目标服务器上设置登录名涉及三个步骤,概述如下:Setting up logins on the target server involves three steps outlined below:

1.确定有权访问主数据库的登录名1. Determine logins with access to the primary database

该过程的第一个步骤就是确定必须在目标服务器上复制哪些登录名。The first step of the process is to determine which logins must be duplicated on the target server. 可以使用一对 SELECT 语句来完成此操作,其中一个语句用于源服务器上的逻辑 master 数据库,另一个语句用于主数据库本身。This is accomplished with a pair of SELECT statements, one in the logical master database on the source server and one in the primary database itself.

只有服务器管理员或 LoginManager 服务器角色的成员,才能使用以下 SELECT 语句确定源服务器上的登录名。Only the server admin or a member of the LoginManager server role can determine the logins on the source server with the following SELECT statement.

SELECT [name], [sid]
FROM [sys].[sql_logins]
WHERE [type_desc] = 'SQL_Login'

只有 db_owner 数据库角色的成员、dbo 用户或服务器管理员,才能确定主数据库中的所有数据库用户主体。Only a member of the db_owner database role, the dbo user, or server admin, can determine all of the database user principals in the primary database.

SELECT [name], [sid]
FROM [sys].[database_principals]
WHERE [type_desc] = 'SQL_USER'

2.查找步骤 1 中确定的登录名的 SID2. Find the SID for the logins identified in step 1

通过将前一部分中所述的查询的输出进行比较以及对 SID 进行匹配,可以将服务器登录名映射到数据库用户。By comparing the output of the queries from the previous section and matching the SIDs, you can map the server login to database user. 包含数据库用户以及匹配的 SID 的登录名有权以该数据库用户主体的身份访问该数据库。Logins that have a database user with a matching SID have user access to that database as that database user principal.

可以使用以下查询来查看所有用户主体及其在数据库中的 SID。The following query can be used to see all of the user principals and their SIDs in a database. 只有 db_owner 数据库角色的成员或服务器管理员才能运行此查询。Only a member of the db_owner database role or server admin can run this query.

SELECT [name], [sid]
FROM [sys].[database_principals]
WHERE [type_desc] = 'SQL_USER'


INFORMATION_SCHEMA 和 sys 用户具有 NULL SID,guest SID 为 0x00 。The INFORMATION_SCHEMA and sys users have NULL SIDs, and the guest SID is 0x00. 如果数据库创建者是服务器管理员而不是 DbManager 的成员,则 dbo SID 可能以 0x01060000000001648000000000048454 开头。The dbo SID may start with 0x01060000000001648000000000048454, if the database creator was the server admin instead of a member of DbManager.

3.在目标服务器上创建登录名3. Create the logins on the target server

最后一个步骤是转到一个或多个目标服务器,并使用相应的 SID 生成登录名。The last step is to go to the target server, or servers, and generate the logins with the appropriate SIDs. 基本语法如下。The basic syntax is as follows.

CREATE LOGIN [<login name>]
WITH PASSWORD = <login password>,
SID = <desired login SID>


如果要授予用户对辅助数据库而不是主数据库的访问权限,可以使用以下语法更改主服务器上的用户登录名来实现此目的。If you want to grant user access to the secondary, but not to the primary, you can do that by altering the user login on the primary server by using the following syntax.


DISABLE 不会更改密码,因此,始终可以根据需要启用该登录名。DISABLE doesn't change the password, so you can always enable it if needed.

后续步骤Next steps