Using MS SQL Cluster as Backend Database

Overview

Password Manager Pro allows you to use MS SQL cluster as backend database. The steps to configure PMP to use MS SQL cluster as backend are significantly different from using a standalone SQL server as the backend database because PMP has been configured to connect to SQL server cluster only through SSL to ensure high level of security.

To use a standalone SQL server as the backend database, the SSL certificate is created with the fully qualified DNS name of the SQL server and it imported in the LOCAL COMPUTER PERSONAL certificate store. In the SQL server configuration manager, the certificates matching the DNS name of the SQL server get listed in the certificate configuration screen. But, this procedure will not work in the case of SQL server cluster setup.

In the case of SQL server cluster, you need to obtain the server certificate with the fully qualified DNS name of the failover clustered instance and install it on all the nodes in the failover cluster. For example, assume that you have a two-node cluster with nodes named test1.yourcompany.com and test2.yourcompany.com and a failover clustered instance of SQL Server named pmpcluster. To use the cluster with PMP, you need to obtain a certificate for pmpcluster.yourcompany.com and install the certificate on both nodes.

Important Note : It is recommended to try these steps in a test setup first and verify if everything is working fine. You may download PMP and try using MS SQL cluster as backend.



To use MSSQL Always-On failover cluster, you need to add an entry in the pmp_key.key as instructed below:

ENCRYPTIONKEY=n2Z(-*zcPioHfYpmrQwrmICiXmiRUbhQ
MASTERKEY=s4X)6@ajSXCETRC

You can find the master encryption key in masterkey.key file which is placed under <PMP-Home>\conf directory.



MS SQL cluster as backend: Summary of steps

  1. Enable SSL encryption in SQL Server: Create an SSL certificate and install it in Windows certificate store
    • Generate the certificate and get it signed by a third-party CA (OR)
    • Create a self-signed certificate
  2. Install the server certificate in all the nodes where SQL server is running
  3. Install the CA's root certificate / server certificate in PMP
  4. Enable SSL encryption in all the nodes where SQL server is running
  5. Execute ChangeDB.bat

Enable SSL Encryption in SQL Server

Create an SSL certificate and install it in Windows certificate store (in the machine where SQL server is running)

Prior to trying to connect PMP with SQL server, you need to enable SSL encryption in SQL Server. You may create an SSL Certificate and get it signed by a Certificate Authority (CA) OR it could be self-signed.

Option 1: Generating the certificate and getting it signed by a third-party CA:

You can create the certificate using openssl and it involves two steps:

  • generating private key and
  • generating certificate.

Use the following commands to create the certificate.

Generate private key

openssl genrsa -des3 -out server.key 2048

Generate a certificate request

Use the server private key to create a certificate request. Enter the passphrase for the key, Common Name, hostname or IP address when prompted:

openssl req -new -key server.key -out server.csr

Here, in the place of Common Name, specify the FQDN of the SQL Server cluster instance.

  • After generating the certificate, you need to get it signed by a third-party CA. Some of the prominent CAs are Verisign (http://verisign.com), Thawte (http://www.thawte.com), RapidSSL (http://www.rapidssl.com). Check their documentation / website for details on submitting CSRs and this will involve a cost to be paid to the CA
  • This process usually takes a few days time and you will be returned your signed server SSL certificate and the CA's root certificate as .cer files
  • The server certificate has to be installed in all the nodes where SQL server is running. The CA root certificate has to be installed in PMP server.

Install the server certificate in all the nodes where SQL server is running. You may use MMC to do this as shown below

  • Open the MMC console by clicking Start >>> Run (in the machine where SQL server is running). In the Run dialog box type: MMC
  • On the Console menu, click Add/Remove Snap-in. Click Add and then click Certificates. Click Add again.You will be prompted to open the snap-in for the current user account, the service account, or for the computer account. Select the Computer Account.
  • Select Certificates (Local Computer) >> Personal >> Certificates
  • Right-click Certificates >> Click All Tasks >> Import
  • Browse select the certificate to be installed

Install the CA's root certificate in PMP

  • Copy the CA's root certificate and paste it under <Password Manager Pro Installation Folder >/bin directory
  • From <Password Manager Pro Installation Folder>/bin directory, execute the following command:
    importCert.bat <name of the root certificate pasted as explained above>
  • This adds the certificate to the PMP certificate store.

Option 2: Creating a self-signed certificate

If you want to create a self-signed certificate and use it, you need to carry out the following steps in one of the nodes where SQL server is installed:

Create a self signed certificate using the certificate creation tool makecert.exe and install it in one of the nodes where SQL Server is running
  • Execute the following command from one of the nodes where SQL server is installed

    makecert.exe -r -pe -n "CN=pmpcluster.yourcompany.com" -a sha1 -b 01/01/2011 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange pmpcluster.yourcompany.com.cer

    Here, for CN, enter the FQDN of the SQL server cluster instance replacing the example entry pmpcluster.yourcompany.com

  • The above command will install a self signed certificate in your local store. It will also store the certificate in the file pmpcluster.yourcompany.com.cer
Exporting self signed .pfx file
  • Open the MMC console by clicking Start >>> Run (in the machine where SQL server is running). In the Run dialog box type: MMC
  • On the Console menu, click Add/Remove Snap-in. Click Add and then click Certificates. Click Add again.You will be prompted to open the snap-in for the current user account, the service account, or for the computer account. Select the Computer Account.
  • Select Certificates (Local Computer) >> Personal >> Certificates
  • Locate the self signed certificate just created, right click and export .pfx file.
Importing self signed .pfx file in all the nodes where SQL server is running
  • Open the MMC console by clicking Start >>> Run (in the machine where SQL server is running). In the Run dialog box type: MMC
  • On the Console menu, click Add/Remove Snap-in. Click Add and then click Certificates. Click Add again.You will be prompted to open the snap-in for the current user account, the service account, or for the computer account. Select the Computer Account.
  • Select Certificates (Local Computer) >> Personal >> Certificates
  • Right-click Certificates >> Click All Tasks >> Import
  • Browse select the exported .pfx file certificate to be installed
Install the server certificate in PMP
  • Copy the server certificate and paste it under <Password Manager Pro Installation Folder>/bin directory
  • From <Password Manager Pro Installation Folder&/bin directory, execute the following command:
    importCert.bat <name of the server certificate>
  • This adds the certificate to the PMP certificate store.

Enable SSL Encryption in all the nodes where SQL Server is running

The certificate used by SQL Server to encrypt connections is specified in the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate

This key contains a property of the certificate known as thumbprint that uniquely identifies each certificate in the server.

The Thumbprint value from the certificate which is created using the above steps, should be copied and updated in the registry's certificate property.

Important Note: For copying the thumbprint, follow the steps detailed below to copy it properly:

  • Eliminate the Unicode character from the thumbprint. You can do this by pasting the thumbprint in a notepad.
  • Save the notepad content as a different file in ANSI format which will prompt the following dialog, click OK and proceed

  • Then, open the ANSI format file and remove the ? characters from the file
  • Eliminate the spaces between characters in the thumbprint
  • Save this thumbprint to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\Certificate property.

Execute ChangeDB.bat

After completing the above steps, you need to execute ChangeDB.bat in PMP. Refer to Step 4 in the section "Using MS SQL Server as Backend" of Installation & Getting Started chapter of the help documentation for details.

©2014, ZOHO Corp. All Rights Reserved.

Top