High Availability for Microsoft SQL Server

In mission-critical environments, one of the crucial requirements is to provide uninterrupted access to passwords. PAM360 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 PAM360. This document walks you through the process of configuring MS SQL server replication between the master and slave MS SQL databases using PAM360 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.

At the end of this document, you will have learned 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 PAM360
  3. Example Scenarios

    3.1 What happens to Audit Trails?

  4. Setting up High Availability

    4.1 With Replication using PAM360

    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 PAM360 servers and database instances.
  • One PAM360 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.
  • PAM360 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 PAM360

3. Example Scenarios

The HA architecture in PAM360 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

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 - With Replication using PAM360

Pictorial Representation of How the High Availability Works:

Steps Required

Step 1:

  • Stop primary server, if running

Step 2:

  • The MS SQL server, which is used by the PAM360 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 PAM360 Primary server. Before proceeding with this step, ensure that the MS SQL slave server is also configured with SSL. You can do this by carrying out Steps 1, 2, 3 in this document.

To import the SSL certificate of slave SQL server into PAM360 Primary:

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

Step 3:

  • Navigate to <PAM360 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 <PAM360 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 PAM360. If instance name is not specified, PAM360 will try establishing connection with the default instance on port 1433.

      3. Since PAM360 connects to MS SQL only in SSL mode, it is recommended that you create a dedicated database instance running in a specific port for PAM360. 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 PAM360 needs to connect to the database. (You need to specify the username having SQL role as sysadmin. PAM360 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 PAM360 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 PAM360. If instance name is not specified, PAM360 will try establishing connection with the default instance on port 1433.

      3. Since PAM360 connects to MS SQL only in SSL mode, it is recommended that you create a dedicated database instance running in a specific port for PAM360. 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 PAM360 needs to connect to the database. (You need to specify the username having SQL role as sysadmin. PAM360 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 PAM360 database. Default is "pam360standby". (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 PAM360 as secondary server in a separate workstation. To install PAM360 as secondary, during installation process, you need to choose the option "Configure this server as High availability secondary server (Read Only)". After installation, the PAM360 Secondary server should not be started.

Step 7:

After installing the PAM360 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 PAM360 by editing the file <PAM360 Standby Installation Folder>/bin ChangeDB.bat (Windows) or <PAM360 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 PAM360 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 PAM360 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 PAM360 needs to connect to the database. The username and password entered here will be stored in PAM360. 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 PAM360 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 PAM360 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 <PAM360_Secondary_Installation_Folder>and unzip it. Take care to extract the files under <PAM360_Secondary_Installation_Folder> only. It will overwrite the existing files. This SHOULD NOT be unzipped under <PAM360_Secondary_Installation_Folder>/SQLServerHAPack directory.

Step 9:

After extracting SQLServerHAPack.zip in PAM360 Secondary Server, navigate to <PAM360_Secondary_Installation_Folder>/conf folder, edit manage_key.conf and specify the location of pam360_key.key (encryption master key). Then, start PAM360 Secondary Server. PAM360 requires the pam360_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:
Top