Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)

Applies to: SQL Server

This example shows all the stages required to create a database mirroring session using certificate-based authentication. The examples in this topic use Transact-SQL. Unless you can guarantee that your network is secure, we recommend that you use encryption for database mirroring connections.

When copying a certificate to another system, use a secure copy method. Be extremely careful to keep all of your certificates secure.

The following example demonstrates what must be done on one partner that resides on HOST_A. In this example, the two partners are the default server instances on three computer systems. The two server instances run in nontrusted Windows domains, so certificate-based authentication is required.

The initial principal role is taken by HOST_A, and the mirror role is taken by HOST_B.

Setting up database mirroring using certificates involves four general stages, of which three stages-1, 2, and 4-are demonstrated by this example. These stages are as follows:

1. Configuring Outbound Connections

   This example shows the steps for:

   1. Configuring Host_A for outbound connections.

   2. Configuring Host_B for outbound connections.

   For information about this stage of setting up database mirroring, see Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL).

2. Configuring Inbound Connections

   This example shows the steps for:

   1. Configuring Host_A for inbound connections.

   2. Configuring Host_B for inbound connections.

   For information about this stage of setting up database mirroring, see Allow a Database Mirroring Endpoint to Use Certificates for Inbound Connections (Transact-SQL).

3. Creating the Mirror Database

   For information on how to create a mirror database, see Prepare a Mirror Database for Mirroring (SQL Server).

4. Configuring the Mirroring Partners

To configure Host_A for outbound connections

1. On the master database, create the database master key, if needed. Replace <password> with a valid password.

   USE master;  
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';  
   GO  
   

2. Make a certificate for this server instance.

   USE master;  
   CREATE CERTIFICATE HOST_A_cert   
      WITH SUBJECT = 'HOST_A certificate';  
   GO  
   

3. Create a mirroring endpoint for server instance using the certificate.

   CREATE ENDPOINT Endpoint_Mirroring  
      STATE = STARTED  
      AS TCP (  
         LISTENER_PORT=7024  
         , LISTENER_IP = ALL  
      )   
      FOR DATABASE_MIRRORING (   
         AUTHENTICATION = CERTIFICATE HOST_A_cert  
         , ENCRYPTION = REQUIRED ALGORITHM AES  
         , ROLE = ALL  
      );  
   GO  
   

4. Back up the HOST_A certificate, and copy it to other system, HOST_B.

   BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOST_A_cert.cer';  
   GO  
   

5. Using any secure copy method, copy C:\HOST_A_cert.cer to HOST_B.

To configure Host_B for outbound connections

1. On the master database, create the database master key, if needed.

   USE master;  
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#2>';  
   GO  
   

2. Make a certificate on the HOST_B server instance.

   CREATE CERTIFICATE HOST_B_cert   
      WITH SUBJECT = 'HOST_B certificate for database mirroring';  
   GO  
   

3. Create a mirroring endpoint for the server instance on HOST_B.

   CREATE ENDPOINT Endpoint_Mirroring  
      STATE = STARTED  
      AS TCP (  
         LISTENER_PORT=7024  
         , LISTENER_IP = ALL  
      )   
      FOR DATABASE_MIRRORING (   
         AUTHENTICATION = CERTIFICATE HOST_B_cert  
         , ENCRYPTION = REQUIRED ALGORITHM AES  
         , ROLE = ALL  
      );  
   GO  
   

4. Back up HOST_B certificate.

   BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\HOST_B_cert.cer';  
   GO   
   

5. Using any secure copy method, copy C:\HOST_B_cert.cer to HOST_A.

For more information, see Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL).

[Top of Example]

To configure Host_A for inbound connections

1. Create a login on HOST_A for HOST_B. Replace <password> with a valid password.

   USE master;  
   CREATE LOGIN HOST_B_login WITH PASSWORD = '<password>';  
   GO  
   

2. --Create a user for that login.

   CREATE USER HOST_B_user FOR LOGIN HOST_B_login;  
   GO  
   

3. --Associate the certificate with the user.

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

4. Grant CONNECT permission on the login for the remote mirroring endpoint.

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

To configure Host_B for inbound connections

1. Create a login on HOST_B for HOST_A.

   USE master;  
   CREATE LOGIN HOST_A_login WITH PASSWORD = '=Sample#2_Strong_Password2';  
   GO  
   

2. Create a user for that login.

   CREATE USER HOST_A_user FOR LOGIN HOST_A_login;  
   GO  
   

3. Associate the certificate with the user.

   CREATE CERTIFICATE HOST_A_cert  
      AUTHORIZATION HOST_A_user  
      FROM FILE = 'C:\HOST_A_cert.cer'  
   GO  
   

4. Grant CONNECT permission on the login for the remote mirroring endpoint.

   GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];  
   GO  
   

For more information, see Allow a Database Mirroring Endpoint to Use Certificates for Inbound Connections (Transact-SQL).

[Top of Example]

For information on how to create a mirror database, see Prepare a Mirror Database for Mirroring (SQL Server).

1. On the mirror server instance on HOST_B, set the server instance on HOST_A as the partner (making it the initial principal server instance). Substitute a valid network address for TCP://HOST_A.Mydomain.Corp.Adventure-Works``.com:7024. For more information, see Specify a Server Network Address (Database Mirroring).

   --At HOST_B, set server instance on HOST_A as partner (principal server):  
   ALTER DATABASE AdventureWorks   
       SET PARTNER = 'TCP://HOST_A.Mydomain.Corp.Adventure-Works.com:7024';  
   GO  
   

2. On the principal server instance on HOST_A, set the server instance on HOST_B as the partner (making it the initial mirror server instance). Substitute a valid network address for TCP://HOST_B.Mydomain.Corp.Adventure-Works.com:7024.

   --At HOST_A, set server instance on HOST_B as partner (mirror server).  
   ALTER DATABASE AdventureWorks   
       SET PARTNER = 'TCP://HOST_B.Mydomain.Corp.Adventure-Works.com:7024';  
   GO  
   

3. This example assumes that the session will be running in high-performance mode. To configure this session for high-performance mode, on the principal server instance (on HOST_A), set transaction safety to OFF.

   --Change to high-performance mode by turning off transaction safety.  
   ALTER DATABASE AdventureWorks   
       SET PARTNER SAFETY OFF  
   GO  
   

   Note

   If you intend to run in high-safety mode with automatic failover, leave transaction safety set to FULL (the default setting) and add the witness as soon as possible after executing the second SET PARTNER 'partner_server' statement. Note that the witness must first be configured for outbound and inbound connections.

[Top of Example]

• Prepare a Mirror Database for Mirroring (SQL Server)

• Allow a Database Mirroring Endpoint to Use Certificates for Inbound Connections (Transact-SQL)

• Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL)

• Management of Logins and Jobs After Role Switching (SQL Server)

• Manage Metadata When Making a Database Available on Another Server Instance (SQL Server) (SQL Server)

• Troubleshoot Database Mirroring Configuration (SQL Server)

Transport Security for Database Mirroring and Always On Availability Groups (SQL Server)
Specify a Server Network Address (Database Mirroring)
The Database Mirroring Endpoint (SQL Server)
Use Certificates for a Database Mirroring Endpoint (Transact-SQL)
ALTER DATABASE (Transact-SQL)
Security Center for SQL Server Database Engine and Azure SQL Database