Using MS SQL Cluster as the Backend Database

MS SQL Cluster is used as backend database in Access Manager Plus. The steps to configure Access Manager Plus to use MS SQL cluster as backend are significantly different from using a standalone SQL server as the backend database because Access Manager Plus 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 ampcluster. To use the cluster with Access Manager Plus, you need to obtain a certificate for ampcluster.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 Access Manager Plus and try using MS SQL cluster as backend.

To use MSSQL Always-On failover cluster, you need to add an entry in the amp_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 <AMP-Home>\conf directory.

Summary of Steps

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 Access Manager Plus
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 Access Manager Plus 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.

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:

1. Generating a private key
2. Generating certificate

Use the following commands to create the certificate:

Generating a private key:

openssl genrsa -des3 -out server.key 2048

Generating 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 Access Manager Plus server.

1.3 Create 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.

1.3.1 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:

1. Execute the following command from one of the nodes where SQL server is installed:
2. makecert.exe -r -pe -n "CN=ampcluster.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 ampcluster.yourcompany.com.cer
3. Here, for CN, enter the FQDN of the SQL server cluster instance replacing the example entry ampcluster.yourcompany.com.
4. The above command will install a self signed certificate in your local store. It will also store the certificate in the file ampcluster.yourcompany.com.cer
   

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 Add/Remove Snap-in. Click Add >> Certificates and tap 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.
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 Add/Remove Snap-in. Click Add >> Certificates and tap 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.
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 Access Manager Plus:

1. Copy the server certificate and paste it under <AMP Installation Folder>/bin directory.
2. From <AMP Installation Folder&/bin directory, execute the following command:
   importCert.bat <name of the server certificate>
3. This adds the certificate to the Access Manager Plus 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 Add/Remove Snap-in. Click Add >> Certificates and tap 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.
   

3. Install the CA's Root Certificate/Server Certificate in Access Manager Plus

1. Copy the CA's root certificate and paste it under <AMP Installation Folder >/bin directory
2. From <AMP 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 Access Manager Plus 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.

4.1 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 bat file

After completing the above steps, you need to execute ChangeDB.bat in Access Manager Plus. Refer to Step 3 in the section Using MS SQL Server as Backend of Getting Started with Access Manager Plus chapter of the help documentation for details.

©2025, Zoho Corporation Pvt. Ltd. All Rights Reserved.