Using MS SQL Cluster as Backend Database

Password Manager Pro allows you to use MS SQL cluster as backend database. The steps to configure Password Manager Pro to use MS SQL cluster as backend are significantly different from using a standalone SQL server as the backend database because Password Manager Pro 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 Password Manager Pro, you need to obtain a certificate for pmpcluster.yourcompany.com and install the certificate on both nodes.

Note: It is recommended to try these steps in a test setup first and verify if everything is working fine. You may download Password Manager Pro 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.

Steps Required

  1. Enable SSL Encryption in SQL Server

    1.1 Create an SSL Certificate and Install it in Windows Certificate Store

    1.2 Generate the Certificate and get it Signed by a third-party CA (OR)

    1.3 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 Password Manager Pro
  4. Enable SSL Encryption in all the Nodes where SQL Server is Running
  5. Execute ChangeDB.bat

1. Enable SSL Encryption in SQL Server

1.1 Create an SSL Certificate and Install it in Windows Certificate Store (in the machine where SQL server is running)

Prior to trying to connect Password Manager Pro 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.

Note: The SQL certificate used for SQL cluster should contain the following attributes:

CN: cl-sql.example.com
SAN:
<li>sql-node1
sql-node1.example.com
sql-node2
sql-node2.example.com
sqlinstance
sqlinstance.example.com
</li>

1.2 Generate the Certificate and get it Signed by a third-party CA

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

  • Generating private key
  • Generating certificate

Use the following commands to create the certificate:

Generate private key:

openssl genrsa -des3 -out server.key 2048

Generate a certificate:

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.

  1. After generating the certificate, you need to get it signed by a third-party CA. Some of the prominent CAs are Verisign, Thawte, RapidSSL. Check their documentation / website for details on submitting CSRs and this will involve a cost to be paid to the CA.
  2. 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
  3. 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 Password Manager Pro server.

1.3 Create a Self-Signed Certificate

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

1.3.1 Create a self-signed certificate using PowerShell:

  1. Navigate to the SQL Server and open PowerShell (Run as administrator).
  2. Execute the following command:

    New-SelfSignedCertificate -DnsName FQDN of the SQL server -CertStoreLocation cert:\LocalMachine\My

  3. The above command will install and store a self-signed certificate in your local store.

1.3.2 Exporting self-signed .pfx file:

  1. Click Start >> Run (in the machine where SQL server is running). In the Run dialog box type MMC.
  2. On the Console menu, click File >> Add/Remove Snap-in. Click Add >> 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 Computer Account.
  3. Select Certificates (Local Computer) >> Personal >> Certificates.
  4. Locate the self signed certificate just created, right click and export .pfx file.

1.3.3 Importing self signed .pfx file in all the nodes where SQL server is running:

  1. Click Start >> Run (in the machine where SQL server is running). In the Run dialog box type MMC.
  2. On the Console menu, click File >> Add/Remove Snap-in. Click Add >> 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 Computer Account.
  3. Select Certificates (Local Computer) >> Personal >> Certificates.
  4. Right-click Certificates >> Click All Tasks >> Import.
  5. Browse select the exported .pfx file certificate to be installed.

1.3.4 Install the server certificate in Password Manager Pro:

  1. Copy the server certificate and paste it under <Password Manager Pro Installation Folder>/bin directory.
  2. From <Password Manager Pro Installation Folder&/bin directory, execute the following command:
    importCert.bat <name of the server certificate>
  3. This adds the certificate to the Password Manager Pro certificate store.

2. Install the Server Certificate in all the Nodes where SQL Server is Running

  1. Click Start >> Run (in the machine where SQL server is running). In the Run dialog box type MMC.
  2. On the Console menu, click File >> Add/Remove Snap-in. Click Add >> 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.
  3. Select the Computer Account.
  4. Select Certificates (Local Computer) >> Personal >> Certificates.
  5. Right-click Certificates and click All Tasks >> Import.
  6. Browse and select the certificate to be installed.

3. Install the CA's Root Certificate in Password Manager Pro

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

4. 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.

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

  1. Eliminate the Unicode character from the thumbprint. You can do this by pasting the thumbprint in a notepad.

  2. Save the notepad content as a different file in ANSI format which will prompt the following dialog, click OK and proceed.

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

5. Execute ChangeDB.bat

After completing the above steps, you need to execute ChangeDB.bat in Password Manager Pro. Refer to section Execute ChangeDB.bat in Password Manager Pro of Using MS SQL Server as Backend Database chapter of the help documentation for details.
Top