VSS KB Articles

How to Configure DNS on a Domain Controller with Two IP Addresses
7/12/2018
How to Configure DNS on a Domain Controller with Two IP Addresses; Configure DNS on a DC with two IP addresses


How to safeguard from the SWEET32
7/12/2018
How to safeguard from the SWEET32 Issue, CVE-2016-2183, Disable RC4 and 3DES on Windows Server


Redirect from HTTP to HTTPS using the IIS URL Rewrite module
2/12/2018
This is the most common requirement on most of the Exchange servers hosted on IIS. The server admins configure an http to https redirect.


Sonicwall disconnects from network every 10-15 minutes on Verizon FIOS
2/11/2018
ISP temporarily disabling port due to receiving excessive ARP requests from SonicWall. Here are the settings to resolve it.


Encrypting Connections to MS SQL Server
2/8/2018
If you want to use encryption with a failover cluster, you must install the server certificate with the fully qualified DNS name of the failover clustered instance on all nodes in the failover cluster. For example, if you have a two-node cluster...


    

4/27/2011
Setting Up Database Mirroring Using Certificates (Transact-SQL)

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

            SQL Server 2008 R2
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 How to: Allow Database Mirroring 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 How to: Allow Database Mirroring to Use Certificates for Inbound Connections (Transact-SQL).

  3. Creating the Mirror Database

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

  4. Configuring the Mirroring Partners

Configuring Outbound Connections

To configure Host_A for outbound connections

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

    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_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 How to: Allow Database Mirroring to Use Certificates for Outbound Connections (Transact-SQL).

[Top]

Configuring Inbound Connections

To configure Host_A for inbound connections

  1. Create a login on HOST_A for HOST_B.

    USE master;
    CREATE LOGIN HOST_B_login WITH PASSWORD = '1Sample_Strong_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
    
Important noteImportant

If you intend to run in high-safety mode with automatic failover, you must repeat the same setup steps to configure the witness for outbound and inbound connections. Setting up the inbound connections when a witness is involved requires that you set up logins and users for the witness on both of the partners and for both partners on the witness.

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

[Top]

Creating the Mirror Database

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

Configuring the Mirroring Partners

  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 Specifying a Server Network Address (Database Mirroring).

    --At HOST_B, set server instance on HOST_A as partner (principal server):
    ALTER DATABASE AdventureWorks2008R2 
        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 AdventureWorks2008R2 
        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 transacton safety.
    ALTER DATABASE AdventureWorks2008R2 
        SET PARTNER SAFETY OFF;
    GO
    
    NoteNote

    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]