DB Mirroring Endpoint Cert


MS SQL 2008 Database Mirroring Endpoint to Use Certificates

The steps for configuring server instances to use certificates to authenticate outbound connections for database mirroring. Outbound connection configuration must be done before you can set up inbound connections.  3 Server: Primary Server: A, Secondry Server: B, Witness Server: Ws

All mirroring connections on a server instance use a single database mirroring endpoint, and you must specify the authentication method of the server instance when you create the endpoint.
 
* The process of configuring outbound connections, involves the following general steps:

1.In the master database, create a database Master Key.

2.In the master database, create an encrypted certificate on the server instance.

3.Create an endpoint for the server instance using its certificate.

4.Back up the certificate to a file and securely copy it to the other system or systems.

You must complete these steps for each partner and the witness, if there is one.

The following procedure describes these steps, for each step, the procedure provides an example for configuring a server instance on a system named HOST_A. The accompanying Exp. section demonstrates the same steps for another server instance on a system named HOST_B.

To configure server instances for outbound mirroring connections (On Server_A)
* On the master database, create the database Master Key, if none exists. To view the existing keys for a database, use the sys.symmetric_keys catalog view.

To create the database Master Key, use the following Transact-SQL command:

USE master;
–Create the database Master Key, if needed.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘*Stockholm_Man*’;
GO

Use a unique, strong password, and record it in a safe place.

For more information, see CREATE MASTER KEY (T-SQL) and Create a Database Master Key.

* In the master database, create an encrypted certificate on the server instance to use for its outbound connections for database mirroring.

For exp. to create a certificate for the Server_A system.
create this rule on the @ExpirationDate field of the Certificate facet, if you like to.

USE master;
CREATE CERTIFICATE Server_A_cert
   WITH SUBJECT = ‘Server_A certificate for database mirroring’,
   EXPIRY_DATE = ’11/30/2030′; — 2030 or 2050 year
GO

* To view the certificates in the master database, you can use the following Transact-SQL statements:

 USE master;
SELECT * FROM sys.certificates;

* Database mirroring endpoint exist on each of the server instances.

If a database mirroring endpoint already exists for the server instance, you should reuse that endpoint for any other sessions you establish on the server instance.
To determine whether a database mirroring endpoint exists on a server instance and to view its configuration, use the following statement:
 SELECT name, role_desc, state_desc, connection_auth_desc, encryption_algorithm_desc
   FROM sys.database_mirroring_endpoints;

If no endpoint exists, create an endpoint that uses this certificate for outbound connections and that uses the certificate’s credentials for verification on the other system. This is a server-wide endpoint that is used by all mirroring sessions in which the server instance participates.

For example, to create a mirroring endpoint for the example server instance on Server_A.

 CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=0077 –as you wish
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Server_A_cert
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

4.Back up the certificate and copy it to the other system or systems. This is necessary in order to configure inbound connections on the other system.
 BACKUP CERTIFICATE HOST_A_cert TO FILE = ‘C:Server_A_cert.cer’;
GO

Copy this certificate using any secure method you choose. Be extremely careful to keep all of your certificates secure.

The example code in the preceding steps configure outbound connections on Server_A.

You now need to perform the equivalent outbound steps for Server_B. These steps are apply to ServerB or IF you have any witness server too.

configuring Server_B for outbound connections.
 USE master;
–Create the database Master Key, if needed.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<Stockholm_#2>’;
GO

— Make a certifcate on Server_B server instance.
CREATE CERTIFICATE Server_B_cert
   WITH SUBJECT = ‘Server_B certificate for database mirroring’,
   EXPIRY_DATE = ’11/30/2050′;
GO

–Create a mirroring endpoint for the server instance on Server_B.
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=0078
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Server_B_cert
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

–Backup Server_B certificate.
BACKUP CERTIFICATE HOST_B_cert TO FILE = ‘C:Server_B_cert.cer’;
GO

–Using any secure copy method, copy C:Server_B_cert.cer to Server_A.

Use same method on WS also.

Copy the certificate to the other system using any secure method you choose. Be extremely careful to keep all of your certificates secure.
Any feedback will be warmly welcome because i do believe that we can learn from each other.

Mehboob

MS SQL & SharePoint Server Adminstrator 2010 – MCITP & MCTS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s