数据库镜像 - 使用证书进行入站连接Database Mirroring - Use Certificates for Inbound Connections

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

本主题说明配置服务器实例以使用证书对数据库镜像的入站连接进行身份验证的步骤。This topic describes the steps for configuring server instances to use certificates to authenticate inbound connections for database mirroring. 在可以建立入站连接之前,必须在每个服务器实例上配置出站连接。Before you can set up inbound connections, you must configure outbound connections on each server instance. 有关详细信息,请参阅 允许数据库镜像终结点使用证书进行出站连接 (Transact-SQL)For more information, see Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL).

配置入站连接的过程通常有以下几个步骤:The process of configuring inbound connections, involves the following general steps:

  1. 为其他系统创建登录名。Create a login for other system.

  2. 创建一个使用该登录名的用户。Create a user for that login.

  3. 获取其他服务器实例的镜像端点的证书。Obtain the certificate for the mirroring endpoint of the other server instance.

  4. 将该证书与在步骤 2 中创建的用户相关联。Associate the certificate with the user created in step 2.

  5. 授予对该镜像端点的登录名的 CONNECT 权限。Grant CONNECT permission on the login for that mirroring endpoint.

如果存在见证服务器,还必须为见证服务器设置进站连接。If there is a witness, you must also set up inbound connections for it. 这需要在两个伙伴上为见证服务器设置登录名、用户和证书,反之亦然。This requires setting up logins, users, and certificates for the witness on both of the partners, and vice versa.

下面的过程详细说明了这些步骤。The following procedure describes these steps in detail. 对于每个步骤,该过程都提供了一个在名为 HOST_A 的系统上配置服务器实例的示例。For each step, the procedure provides an example for configuring a server instance on a system named HOST_A. 伴随的示例部分说明了在名为 HOST_B 的系统上配置另一服务器实例的步骤(步骤相同)。The accompanying Example section demonstrates the same steps for another server instance on a system named HOST_B.

为入站镜像连接配置服务器实例(在 HOST_A 上)To configure server instances for inbound mirroring connections (on HOST_A)

  1. 为其他系统创建登录名。Create a login for the other system.

    下面的示例在 HOST_A 上的服务器实例的 master 数据库中为系统 HOST_B 创建登录名;在此示例中,登录名为 HOST_B_loginThe following example creates a login for the system, HOST_B, in the master database of the server instance on HOST_A; in this example, the login is named HOST_B_login. 请用自己的密码替换示例密码。Substitute a password of your own for the sample password.

    USE master;  
    CREATE LOGIN HOST_B_login   
       WITH PASSWORD = '1Sample_Strong_Password!@#';  
    GO  
    

    有关详细信息,请参阅 CREATE LOGIN (Transact-SQL)For more information, see CREATE LOGIN (Transact-SQL).

    若要查看此服务器实例上的登录名,可以使用以下 Transact-SQLTransact-SQL 语句:To view the logins on this server instance, you can use the following Transact-SQLTransact-SQL statement:

    SELECT * FROM sys.server_principals  
    

    有关详细信息,请参阅 ys.server_principals (Transact-SQL)For more information, see sys.server_principals (Transact-SQL).

  2. 创建一个使用该登录名的用户。Create a user for that login.

    下面的示例为上述步骤中创建的登录名创建了一个用户 HOST_B_userThe following example creates a user, HOST_B_user, for the login created in the preceding step.

    USE master;  
    CREATE USER HOST_B_user FOR LOGIN HOST_B_login;  
    GO  
    

    有关详细信息,请参阅 CREATE USER (Transact-SQL)For more information, see CREATE USER (Transact-SQL).

    若要查看此服务器实例上的用户,可以使用以下 Transact-SQLTransact-SQL 语句:To view the users on this server instance, you can use the following Transact-SQLTransact-SQL statement:

    SELECT * FROM sys.sysusers;  
    

    有关详细信息,请参阅 sys.sysusers (Transact-SQL)For more information, see sys.sysusers (Transact-SQL).

  3. 获取其他服务器实例的镜像端点的证书。Obtain the certificate for the mirroring endpoint of the other server instance.

    如果配置出站连接时还没有获取远程服务器实例的镜像端点的证书副本,请执行此操作。If you have not already done so when configuring outbound connections, obtain a copy of the certificate for the mirroring endpoint of the remote server instance. 若要执行此操作,请按照 允许数据库镜像终结点使用证书进行出站连接 (Transact-SQL) 在该服务器实例上对证书进行备份。To do this, back up the certificate on that server instance as described in Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL). 将证书复制到其他系统时,请使用安全的复制方法。When copying a certificate to another system, use a secure copy method. 必须格外小心地保证所有证书的安全。Be extremely careful to keep all of your certificates secure.

    有关详细信息,请参阅 BACKUP CERTIFICATE (Transact-SQL)For more information, see BACKUP CERTIFICATE (Transact-SQL).

  4. 将该证书与在步骤 2 中创建的用户相关联。Associate the certificate with the user created in step 2.

    下面的示例将 HOST_B 的证书与它在 HOST_A 上的用户关联。The following example, associates the certificate of HOST_B with its user on HOST_A.

    USE master;  
    CREATE CERTIFICATE HOST_B_cert  
       AUTHORIZATION HOST_B_user  
       FROM FILE = 'C:\HOST_B_cert.cer'  
    GO  
    

    有关详细信息,请参阅 CREATE CERTIFICATE (Transact-SQL)For more information, see CREATE CERTIFICATE (Transact-SQL).

    若要查看此服务器实例上的证书,请使用以下 Transact-SQLTransact-SQL 语句:To view the certificates on this server instance, use the following Transact-SQLTransact-SQL statement:

    SELECT * FROM sys.certificates  
    

    有关详细信息,请参阅 sys.certificates (Transact-SQL)For more information, see sys.certificates (Transact-SQL).

  5. 授予对远程镜像端点的登录名的 CONNECT 权限。Grant CONNECT permission on the login for the remote mirroring endpoint.

    例如,若要将对 HOST_A 的权限授予 HOST_B 上的远程服务器实例,以连接到其本地登录名,即连接到 HOST_B_login,请使用以下 Transact-SQLTransact-SQL 语句:For example, to grant permission on HOST_A to the remote server instance on HOST_B to connect to its local login-that is, to connect to HOST_B_login-use the following Transact-SQLTransact-SQL statements:

    USE master;  
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];  
    GO  
    

    有关详细信息,请参阅 GRANT 终结点权限 (Transact-SQL)For more information, see GRANT Endpoint Permissions (Transact-SQL).

这将完成对 HOST_B 登录到 HOST_A 所需的证书身份验证的设置。This completes setting up certificate authentication for HOST_B to log in to HOST_A.

您现在需要在 HOST_B 上为 HOST_A 执行相同的入站步骤,You now need to perform the equivalent inbound steps for HOST_A on HOST_B. 下面的示例部分中示例的入站部分说明了这些步骤。These steps are illustrated in the inbound portion of the example in the Example section, below.

示例Example

下面的示例说明了配置入站连接的 HOST_B。The following example demonstrates configuring HOST_B for inbound connections.

备注

此示例使用一个包含 HOST_A 证书的证书文件,该证书由允许数据库镜像终结点使用证书进行出站连接 (Transact-SQL) 中的代码片段创建。This example uses a certificate file containing the HOST_A certificate that is created by a code snippet in Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL).

USE master;  
--On HOST_B, create a login for HOST_A.  
CREATE LOGIN HOST_A_login WITH PASSWORD = 'AStrongPassword!@#';  
GO  
--Create a user, HOST_A_user, for that login.  
CREATE USER HOST_A_user FOR LOGIN HOST_A_login  
GO  
--Obtain HOST_A certificate. (See the note   
--   preceding this example.)  
--Asscociate this certificate with the user, HOST_A_user.  
CREATE CERTIFICATE HOST_A_cert  
   AUTHORIZATION HOST_A_user  
   FROM FILE = 'C:\HOST_A_cert.cer';  
GO  
--Grant CONNECT permission for the server instance on HOST_A.  
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_A_login  
GO  

如果打算在具有自动故障转移功能的高安全性模式下运行,则必须重复相同的设置步骤为出站连接和入站连接配置见证服务器。If you intend to run in high-safety mode with automatic failover, you must repeat the same set up steps to configure the witness for outbound and inbound connections.

有关创建镜像数据库(包括 Transact-SQL 示例)的详细信息,请参阅为镜像准备镜像数据库 (SQL Server)For information on creating a mirror database, including a Transact-SQL example, see Prepare a Mirror Database for Mirroring (SQL Server).

有关建立高性能模式会话的 Transact-SQL 示例,请参阅 示例:使用证书设置数据库镜像 (Transact-SQL)For a Transact-SQL example of establishing a high-performance mode session, see Example: Setting Up Database Mirroring Using Certificates (Transact-SQL).

.NET Framework 安全性.NET Framework Security

将证书复制到其他系统时,请使用安全的复制方法。When copying a certificate to another system, use a secure copy method. 必须格外小心地保证所有证书的安全。Be extremely careful to keep all of your certificates secure.

另请参阅See Also

针对数据库镜像和 AlwaysOn 可用性组的传输安全性 (SQL Server) Transport Security for Database Mirroring and Always On Availability Groups (SQL Server)
GRANT 终结点权限 (Transact-SQL) GRANT Endpoint Permissions (Transact-SQL)
设置加密的镜像数据库 Set Up an Encrypted Mirror Database
数据库镜像终结点 (SQL Server) The Database Mirroring Endpoint (SQL Server)
数据库镜像配置故障排除 (SQL Server)Troubleshoot Database Mirroring Configuration (SQL Server)