Configuring MS SQL Cluster as the Backend Database

PAM360 supports using an MS SQL cluster as the backend database, providing a secure and scalable option. Configuring PAM360 to use an MS SQL cluster differs significantly from setting it up with a standalone SQL server. This is because PAM360 is designed to connect to the SQL server cluster exclusively through SSL, ensuring a high level of security.

When using a standalone SQL server as the backend database, the SSL certificate is created using the fully qualified DNS name of the SQL server and imported into the local computer's Personal Certificate store. The SQL Server Configuration Manager then lists the certificates that match the DNS name of the SQL server in the certificate configuration screen. However, this method is not applicable when setting up an SQL server cluster.

For an SQL server cluster, you should obtain a server certificate that uses the fully qualified DNS name of the failover clustered instance and install it on all nodes within the failover cluster. For example, if you have a two-node cluster with nodes named test1.yourcompany.com and test2.yourcompany.com, and a failover clustered instance named pam360cluster, you would need to obtain a certificate for pam360cluster.yourcompany.com and install it on both nodes.

Caution

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

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

ENCRYPTIONKEY=n2Z(-*zcPioHfYpmrQwrmICiXmiRUbhQ

		MASTERKEY=s4X)6@ajSXCETRC

You can find the master encryption key in masterkey.key file which is placed under <PAM360 Installation Directory>\conf folder.

The below sections will guide you on configuring MS SQL Cluster as backend database for PAM360:

  1. Creating the SSL Certificate and Installing it in the Windows Certificate Store
  2. Installing the Server Certificate in all the Nodes where SQL Server is Running
  3. Install the Server Certificate in PAM360
  4. Enabling SSL Encryption in all the SQL Server Nodes
  5. Execute ChangeDB.bat
  6. Troubleshooting Tips

1. Creating the SSL Certificate and Installing it in the Windows Certificate Store
(In the machine where SQL server is running)

Prior to connecting PAM360 with the SQL server, you need to enable SSL encryption in the SQL Server. For this SSL encryption, you need an SSL Certificate either signed by a Certificate Authority (CA) or self-signed.

Caution

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.1 Generating a Certificate and Getting it Signed by a Third-Party CA

If you have a CA-signed SSL certificate, proceed directly with the certificate installation from step 4. Else, follow the below steps 1 to 3 to generate and install a new CA-signed certificate:

  1. First, generate a private key using openssl. Execute the following command in your terminal:
    openssl genrsa -des3 -out server.key 2048
    This command generates a 2048-bit RSA private key and saves it to server.key, protected by a passphrase.
  2. Use the private key to create a Certificate Signing Request (CSR). This request will be sent to a CA for signing. Execute the following command:
    openssl req -new -key server.key -out server.csr
    During this process, you will be prompted to enter the Passphrase for the key and provide information such as the Common Name, Hostname or IP Address, where Common Name should be the Fully Qualified Domain Name (FQDN) of your SQL server.
  3. Submit the generated CSR (server.csr) to a third-party CA such as VeriSign, Thawte, or RapidSSL for signing. Refer to the respective CA’s documentation for instructions on submitting the CSR and completing the signing process. Note that this service usually requires a fee. In a few days, you will receive your signed SSL certificate and the CA's root certificate as `.cer` files.
  4. Install the server certificate in all the nodes where the SQL server is running.
  5. Install the CA root certificate in PAM360 using the following steps:
    1. Copy the CA's root certificate to the <PAM360 Installation Folder>\bin directory.
    2. From the <PAM360 Installation Folder>\bin directory, execute the following command:
      importCert.bat <Absolute-Path-of-the-Certificate>
      Replace <Absolute-Path-of-the-Certificate> with the actual path of the root certificate file you copied. This command adds the root certificate to the PAM360 certificate store.

1.2 Creating a Self-Signed Certificate using Powershell

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

  1. Navigate to the SQL Server and open Powershell (run as Administrator).
  2. Execute the following command; this command will install and store a self-signed certificate in your local store.
    New-SelfSignedCertificate -DnsName FQDN of the SQL server -CertStoreLocation cert:\LocalMachine\My
    For further installation of server certificate in all the nodes, the created server certificate is required. Export the created self-signed certificate as a .pfx file using the following steps:
    1. Click Start and select Run. In the Run dialog box, type MMC and press enter. This opens the Microsoft Management Console (MMC).
    2. From the Console menu, select Add/Remove Snap-in.
    3. Click Add, then select Certificates and click Add again.
    4. When prompted, choose to manage certificates for the Computer Account.
    5. Navigate to Manage User Certificates (Local Computer) >> Personal >> Certificates.
    6. Locate the created self-signed certificate, right click and export it as a .pfx file.

2. Installing the Server Certificate in all the SQL Server Nodes

  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 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 >> Click All Tasks >> Import.
  6. Browse and select the certificate to be installed.
    importcertificate

3. Installing the Server Certificate in PAM360

  1. Copy the server certificate and paste it under <PAM360 Installation Folder >/bin directory.
  2. From <PAM360 Installation Folder>/bin directory, execute the following command; this adds the certificate to the PAM360 certificate store.
    importCert.bat <Absolute-Path-of-the-Certificate>

4. Enable SSL Encryption in all the Nodes where SQL Server is Running

To encrypt connections using SSL, SQL Server relies on a specific certificate, identified by its thumbprint, which is stored in the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate

This registry key contains the certificate's thumbprint, a unique identifier that distinguishes each certificate on the server.
registry

To properly configure SSL encryption, follow these steps to copy and update the thumbprint in the registry:

  1. Copy the Thumbprint: Start by copying the thumbprint from the certificate. Paste it into a text editor, such as Notepad to ensure the thumbprint is free from any hidden Unicode characters.
    certificate
  2. Convert to ANSI Format: Save the Notepad file in ANSI format. When prompted with a dialog box, click OK to proceed with the conversion.
  3. Remove Extraneous Characters: Reopen the ANSI-formatted file and remove any unwanted "?" characters that may have appeared during the conversion. Also, eliminate any spaces between the characters in the thumbprint.
  4. Update the Registry: Once you have the cleaned thumbprint, save it to the Certificate property located in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate.

By carefully following these steps, you can ensure that the SSL encryption is properly configured on all nodes where SQL Server is running.

5. Execute ChangeDB.bat

After completing the above steps, you need to execute ChangeDB.bat in PAM360. Refer to section 4 in Configuring MS SQL Server as Backend Database for more details.

6. Troubleshooting Tips


1. Server certificate is not available in the certificate drop down while enabling SSL encryption in the SQL server?

If the server certificate added to the MMC console is not visible in the certificate dropdown, you have to manually update the SQL server certificate for SSL encryption. 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

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

The Thumbprint value of the certificate which we have created for our server, should be copied and updated in the registry's certificate property.
certificate

Note: To copy the thumbprint properly and to update it follow the below steps:

  1. Eliminate the unicode characters 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.
  3. Open the ANSI format file and remove the ? characters from the file.
  4. Remove 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.




Top