High Availability for Microsoft SQL Server
(Feature available in Enterprise Edition only and the procedure applicable for build 6400 and later only)

In mission-critical environments, one of the crucial requirements is to provide un-interrupted access to passwords. Password Manager Pro provides the High Availability feature just to ensure this. While configuring High Availability (HA) for the setup running in Microsoft SQL (MS SQL) server, you need to carry out the replication between master and slave MS SQL databases. This can be done in two ways; from the SQL Server Management Studio and from Password Manager Pro. This document walks you through the process of onfiguring MS SQL server replication between the master and slave MS SQL databases using Password Manager Pro itself.

There is another document, which discusses the same High Availability configuration for the MS SQL server, with the only difference that the replication between master and slave MS SQL databases is carried out using the Microsoft SQL Server Management Studio.

You will learn the following topics with respect to configuring High Availability in a setup running with Microsoft SQL Server:

  1. How does High Availability Work?
  2. The High Availability Architecture in Password Manager Pro
  3. Example Scenarios

    3.1 What happens to Audit Trails?

  4. Setting up High Availability

    4.1 With Replication using Password Manager Pro

    4.2 With Replication using Microsoft SQL Server Management Studio

  5. Verifying High Availability Setup

1. How does High Availability work?

  • There will be redundant Password Manager Pro servers and database instances.
  • One Password Manager Pro instance will be the Primary, providing read/write access to the users. All users will be connected with the Primary only.
  • The other instance will act as the Secondary.
  • Data in both Primary and Secondary servers will always be in sync with each other.
  • Password Manager Pro leverages SQL server's data replication technique for data synchronization that happens through a secure, encrypted channel.
  • When the Primary server goes down, the Secondary will offer 'Read/Write' access to the users (except password reset), until the fully-functional primary server is brought back to service. The changes made in the database in the intervening period will be automatically synchronized upon connection restoration.

2. The High Availability Architecture in Password Manager Pro

3. Example Scenarios

The HA architecture in Password Manager Pro is designed to be compatible with two different scenarios. See the below table for a detailed explanation:

Scenario 1

Hosting Primary & Secondary servers on the same network

The Secondary server is located on the same network, where the Primary server operates. The Secondary server is provided with the read/write access (except password reset action), in case of the Primary server failure.

Example

Primary & Secondary within the same network & the Primary goes down:

Assume, the Primary and Secondary servers are deployed in the same geographical location, say 'A'. In the case when the Primary crashes or goes down, the users of both the Primary and Secondary servers will get the emergency access to the passwords from the Secondary.
Scenario 2

Hosting Primary & Secondary servers on different networks

In this scenario, the Primary and Secondary servers are located on different networks. The Secondary server in the remote network gets the read/write access (except password reset action), in case of a WAN link failure or Primary server failure.

Example

Primary & Secondary in different geographical locations and WAN Link failure happens between the locations:

Assume, the Primary server is in a geographical location 'A' and the Secondary server is deployed in another location 'B'. By default, the users in both 'A' and 'B' will be connected to the Primary and will be carrying out the routine password management activities. The data in both the Primary and Secondary are in sync with each other. Now, imagine, there happens a network connectivity loss between the two locations. In such a situation, the two servers will start operating independently. Thus, the users in location 'A' will remain connected with the Primary server and will be carrying out their operations as usual. On the other hand, the users in location 'B' will get emergency access to the passwords from the Secondary server. Once the connection is re-established between 'A' and 'B', the data in both the locations will be synchronized.


3.1 What happens to Audit Trails?

In the high availability scenarios mentioned above, audit trails will be recorded as usual. In scenario 2, as long as there is network connectivity between the two locations, the audit trails will be printed by the primary. When users connect to the Secondary, it will print operations such as 'password retrieval', 'login' and 'logout'. When the two locations get back network connectivity, the audit data will be synchronized. In scenario 1, when the primary crashes, the 'password retrieval', 'login' and 'logout' done by the users in secondary will be audited. Other audit records will already be in sync at the Standby

4. Setting up High Availability

4.1 With Replication using Password Manager Pro

4.1.i Pictorial Representation of How the High Availability Works

4.1.ii Steps Required

Step 1:

  • Stop primary server, if running

Step 2:

  • The MS SQL server, which is used by the Password Manager Pro primary server, will act as the Master database. You should now specify another instance of MSSQL as slave database. Then, you need to import the SSL certificate of MS SQL server slave database into Password Manager Pro Primary server. Before proceeding with this step, ensure that the MS SQL slave server is also configured with SSL. Refer this section for more information.

To import the SSL certificate of slave SQL server into Password Manager Pro Primary:

  • Navigate to <Password Manager Pro Primary Installation Folder>/bin directory and execute the command importCert.bat <slavecert.cer >

Step 3:

  • Navigate to <Password Manager Pro Primary Installation Folder>/conf directory, open the file masterkey.key and copy the SQL Master Key. You will use this in the next step. (In case, you have moved this key to a secure location as recommended while integration SQL server, keep the key ready for use in the next step).

Step 4:

You need to configure MS SQL server replication between master and slave MS SQL databases.

  • Navigate to <Password Manager Pro Primary Installation Folder>/bin directory and execute the command ConfigureReplication.bat (in Windows) or sh ConfigureReplication.sh (Linux)
  • Specify the details about Master and Slave databases and other details as required

Under Master Database details, provide the following details:

    1. Master Host Name:The name or the IP address of the machine where MS SQL server is installed.
    2. Instance Name: Helps to specify the named instance of SQL server, which has to be used for Password Manager Pro. If instance name is not specified, Password Manager Pro will try establishing connection with the default instance on port 1433.

      3. Since Password Manager Pro connects to MS SQL only in SSL mode, it is recommended that you create a dedicated database instance running in a specific port for Password Manager Pro. If you want to specify a port number other than 1433, you can specify it in the Host Name parameter above as <hostname>:<port>.

    3. User Name and Password: Specify the user name and password with which Password Manager Pro needs to connect to the database. (You need to specify the username having SQL role as sysadmin. Password Manager Pro does not store this username and password anywhere. It is just used for carrying out some queries while configuring replication between MS SQL master and slave servers).
      Here, you have the option to use even your Windows login credentials, if you are connecting to the database from Windows. In this case, you need to enter the username as <domain-name>\<username>
    4. Master Database Name: Name of the Password Manager Pro database.
    5. Master Key: Paste the master key copies in Step 3 above.

Under Slave Database details, provide the following details:

    1. Slave Host Name: The name or the IP address of the machine where MS SQL server is installed.
    2. Instance Name: Helps to specify the named instance of SQL server, which has to be used for Password Manager Pro. If instance name is not specified, Password Manager Pro will try establishing connection with the default instance on port 1433.

      3. Since Password Manager Pro connects to MS SQL only in SSL mode, it is recommended that you create a dedicated database instance running in a specific port for Password Manager Pro. If you want to specify a port number other than 1433, you can specify it in the Host Name parameter above as <hostname>:<port>.

    3. User Name and Password: Specify the user name and password with which Password Manager Pro needs to connect to the database. (You need to specify the username having SQL role as sysadmin. Password Manager Pro does not store this username and password anywhere. It is just used for carrying out some queries while configuring replication between MS SQL master and slave servers).
      Here, you have the option to use even your Windows login credentials, if you are connecting to the database from Windows. In this case, you need to enter the username as <domain-name>\<username>
    4. Slave Database Name: Name of the Password Manager Pro database. Default is "pmpstandby". (If you have chosen the option 'Custom' for "Encryption Key" while configuring ChangeDB.bat for Primary server, you need to create a new database for slave, create Master Key, create Certificate and Create the Symmetric Key using AES 256 encryption. You need to mention the slave database name here.)
    5. Click "Test & Configure" to complete replication. This process will take about 30 minutes or more.

Step 5:

Start the primary server

Step 6:

Install another instance of Password Manager Pro as secondary server in a separate workstation. To install Password Manager Pro as secondary, during installation process, you need to choose the option "Configure this server as High availability secondary server (Read Only)". After installation, the Password Manager Pro Secondary server should not be started.

Step 7:

After installing the Password Manager Pro secondary server, you need to change it to run with MS SQL by carrying out the following:

Execute ChangeDB.bat

Now, you need to provide the details about the SQL server to Password Manager Pro by editing the file <Password Manager Pro Standby Installation Folder>/bin ChangeDB.bat (Windows) or <Password Manager Pro Standby Installation Folder>/bin sh ChangeDB.sh (Linux)

Select SQL Server and enter other values

    1. Host Name of Slave Database: The name or the IP address of the machine where MS SQL server is installed.
    2. Port: The port number in which Password Manager Pro must connect with the database. Default is 1433.
    3. Database Name: Name of the Slave database. Here, take care to specify the name of the slave database exactly as done in Step 4 above.
    4. Authentication: The way in which you would like to connect to the SQL server. If you are connecting to the SQL server from Windows, you have the option to make use of the Windows Single Sign On facility provided Password Manager Pro service is running with a service account, which has the privilege to connect to SQL server. In that case, choose the option "Windows". Otherwise, select the option "SQL". It is recommended to choose the option 'Windows' as the username and password used for authentication are not stored anywhere.
    5. User Name and Password: If you have selected the option "SQL", specify the user name and password with which Password Manager Pro needs to connect to the database. The username and password entered here will be stored in Password Manager Pro. So, you need to take care of hardening the host.
      Here, you have the option to use even your Windows login credentials, if you are connecting to the database from Windows. In this case, you need to enter the username as <domain-name>\<username>
    6. Encryption Key: The key with which your data is to be encrypted and stored in the SQL server. You may either leave it "Default" making Password Manager Pro to generate a key. If you have configured Master database with custom key, you need to choose 'Custom' here also.
    7. If you have selected the option "Custom:" After doing the above, you need to provide certificate name and symmetric key name in the GUI as mentioned in Master database
    8. Click Test and then Save.

Step 8:

To carry Password Manager Pro license, custom icons and rebranding settings, if any, from Primary to Secondary, go to \replication directory and copy SQLServerHAPack.zip. Put the this zip file copied from the PRIMARY Installation to the <PMP_Secondary_Installation_Folder>and unzip it. Take care to extract the files under <PMP_Secondary_Installation_Folder> only. It will overwrite the existing files. This SHOULD NOT be unzipped under <PMP_Secondary_Installation_Folder>/SQLServerHAPack directory.

Step 9:

After extracting SQLServerHAPack.zip in Password Manager Pro Secondary Server, navigate to <PMP_Secondary_Installation_Folder>/conf folder, edit manage_key.conf and specify the location of pmp_key.key (encryption master key). Then, start Password Manager Pro Secondary Server. Password Manager Pro requires the pmp_key.key file accessible with its full path when it starts up every time. After a successful start up, it does not need the key anymore and so the device with the key file can be taken offline.

5. Verify High Availability Setup

After carrying out the above steps, you can verify if the High Availability setup is working properly by looking at the message under Admin >> General >> High Availability of primary or secondary server. If the setup is proper, you will see the following status message:

High Availability Status: Alive

It indicates that high availability is working fine. In case, if the status turns 'Failed', it indicates failure of the setup.

See Also:

High Availability for MS SQL with Replication using SQL Server Management Studio
Top