Establishing secure connection between OpManager and MSSQL database
The following steps are to establish a secure connection between MSSQL database and OpManager versions 127131 and above.
Prerequisites:
- A valid SSL certificate in PFX format that won't be expiring soon is required. If the certificate in another format, please convert it into a PFX file.
- The Common Name in the Subject field of the certificate must be the same as the Fully Qualified Domain Name (FQDN) of the device in which the MSSQL Server is installed.
Follow the below steps to ensure secure communication between OpManager and MSSQL
Associating the SSL certificate with MSSQL
Follow the below steps to select and associate the required SSL certificate with the MSSQL server
- Open SQL Server Configuration Manager.

- Navigate to SQL Server Network Configuration.

- Right-click on the protocols for the specific MSSQL instance you wish to associate the certificate with (e.g., 'protocols for SQLSERVER'). Select Properties.

- In the Flags window, set Force Encryption to YES.

- In the Certificate tab, select the required certificate from the dropdown menu, and click 'OK'. The SSL certificate will be displayed in the dropdown menu, only if the device had the required certificate saved/installed in it.

- Note that the certificate association will take effect only after the MSSQL instance is restarted. Make sure to restart the database after completing the certificate association.
Steps to save the SSL certificate in the server:
Follow the below steps to save a copy of the SSL certificate to the local file directory,
- Open IIS manager on your server.

- Navigate to the "Server Certificates" section.

- Select the specific certificate you want to export, and open it.

- Switch to the Details tab to view the certificate details.

- Click on the "Copy to File" option to open the Certificate Export wizard.

- Follow the wizard's prompts, and click "Next" to proceed.

- On export private key window, select the option that says 'NO, do not export the private key'. Then click Next.

- In the "Export File Format" window, ensure that either "DER encoded binary X.509 (.CER)" or "Base-64 encoded X.509 (.CER)" is chosen, and then click "Next."

- Provide a name for the export file.

- Click Next, and then click Finish to finish exporting the certificate.
Steps to enable MSSQL SSL in OpManager:
- Replace the <certificateservername> with the certificate name (CN) from the IIS manager and then replace the following parameters in database_params.conf under
<OpManagerHome>/conf : encrypt=true;trustServerCertificate=false;hostNameInCertificate=<CertificateServerName> 

Example:
URLurl=jdbc:sqlserver://localhost:1433;databaseName=<DBName>encrypt=true;trustServerCertificate=false;hostNameInCertificate=<CertificateServerName>
- Make a copy of MSSQL SSL certificate which were exported from the above steps to save certificate, and put it under <OpManagerHome>/conf.
- Open cmd prompt under <OpManagerHome>/bin and run importCertificate.bat. Note that, MSSQL will be enabled only if the certificate names in database_params.conf and IIS manager are the same.
Example: importCertificate.bat sslcertificate.cer
Now start the product.
New to ADSelfService Plus?
Related Articles
Installing OpManager on Linux with MSSQL database
From version 12.8.330, MSSQL database is supported for Linux setups. As of now MSSQL database cannot be configured directly from the installation wizard for OpManager's Linux installation. Instead, perform a default installation with PgSQL database ...
Installing OpManager with Amazon RDS MSSQL instance
Installing MSSQL instance in Amazon RDS Step 1: While installing MSSQL instance in Amazon RDS, please make sure that the edition and version we support are selected. Step 2: Make a note of the Master username and password provided (these credentials ...
MSSQL Server Credential Configuration for OpManager
If you choose to use MSSQL as the backend database for OpManager, we recommend that you create a seperate account for OpManager in your MSSQL database server. This ensures proper functionality. However, if you wish to proceed with your existing ...
Reindex & Vacuum OpManager Database
Reindex & Vacuum OpManager Database Stop OpManager service Make sure there is no running postgres process running in the Task Manger Open a command prompt as an administrator and go to /OpManager/bin directory Execute startPgsql.bat to start PGSQL ...
No Database listed while adding MSSQL Monitors
Issue/Scenario: When I try to add MSSQL performance Monitors, I get to the stage to add databases but there were no databases listed . This issue could occur if 32bit MSSQL is installed on a 64bit Widows2008 R2. Solution/Workaround: Apparently ...