Integrating with a Microsoft SQL Server database

This topic describes how to create and configure a Microsoft SQL Server database to integrate it with Netcool/OMNIbus.

The JDBC Gateway requires the Microsoft JDBC Driver for SQL Server to integrate with Microsoft SQL Server. The JDBC driver can be downloaded at the Microsoft JDBC Driver for SQL Server website.

The JDBC Gateway can integrate with the Microsoft SQL Server using either a secure or a non-secure connection. However, the JDBC Gateway will always authenticate using a secure connection. When the JDBC Gateway begins to connect with Microsoft SQL Server, a secure (TLS) connection is initiated and the expected process of a TLS handshake occurs. Once the handshake is completed successfully, the JDBC Gateway authenticates with Microsoft SQL Server using the provided user credentials. Once the authentication is completed successfully, subsequent communication with Microsoft SQL Server will be performed using either a secure or non-secure connection as per the gateway configuration.

To integrating the JDBC Gateway with Microsoft SQL Server, use the following steps:

Step 1: Installing the Microsoft JDBC Driver
Step 2: Preparing tables for Netcool integration
Step 3: Configuring the gateway

Step 1: Installing the Microsoft JDBC Driver

For OMNIbus installations using JRE 1.7, install the JRE 1.7 version of the JDBC driver. Otherwise for OMNIbus installations using JRE 1.8, install the JRE 1.8 version of the JDBC driver instead. Refer to the Microsoft JDBC Driver for SQL Server Support Matrix website for more information on the currently supported driver versions.

Copy the Microsoft JDBC Driver for example, mssql-jdbc-8.2.2.jre8.jar to the $OMNIHOME/gates/java directory. Alternatively, the JDBC driver may be copied to the directory defined in Gate.Java.ClassPath in the properties file. Ensure that only one copy of the JDBC Driver is installed in the directory.

Step 2: Preparing tables for Netcool integration

Preparing REPORTER mode:

Use the scripts bundled in the $OMNIHOME/gates/jdbc2/scripts package under the directory mssql.

At the command prompt, navigate to the mssql directory and run the following command to create the primary REPORTER tables in your database:

> sqlcmd -S sqlserver -U netcool -P password -i mssql.reporting.sql

Where sqlserver is the database name, netcool is the username, password is the password for the netcool user and mssql.reporting.sql is the database script to execute.

At the command prompt, navigate to the mssql directory and run the following command to create the secondary REPORTER audit tables, triggers, views in your database:

 > sqlcmd -S sqlserver -U netcool -P password -i mssql.reporting_audit.sql

You may check that the tables have been created using the following command:

> sqlcmd -S sqlserver -U netcool -P password
1> select table_name from REPORTER.information_schema.tables;
2> go

Preparing AUDIT mode:

Use the scripts bundled in the nco-g-jdbc-audit-scripts package under the directory mssql.

In the command prompt, navigate to the mssql directory and run the following command to create the AUDIT tables in your database:

> sqlcmd -S sqlserver -U netcool -P password -i mssql.sql

Where sqlserver is the database name, netcool is the username, password is the password for the netcool user and mssql.sql is the database script to execute.

You can check that the tables have been created using the following command:

>
1> select table_name from information_schema.tables;
2> go

Step 3: Configuring the gateway

Note: Microsoft SQL Server 2016 and higher versions strictly require clients to securely connect using TLS v1.2 or higher. To ensure that the JDBC Gateway always initiates a TLS connection using TLS v1.2 or higher, the following argument must be specified in the gateway properties file:

Gate.Java.Arguments: ‘-Dcom.ibm.jsse2.overrideDefaultTLS=true’

Specify the correct mode to be used namely, AUDIT or REPORTING:

Gate.Jdbc.Mode: 'REPORTING' # STRING (JDBC gateway mode (AUDIT|REPORTING))

Specify the JDBC connection properties:

Gate.Jdbc.Driver: 'com.microsoft.sqlserver.jdbc.SQLServerDriver' # STRING (JDBC Driver)
Gate.Jdbc.Url: 'jdbc:sqlserver://<hostname or IP address of Microsoft SQL Server>:1433;databaseName=REPORTER' # STRING (JDBC connection URL)
Gate.Jdbc.Username: 'netcool' # STRING (JDBC username)
Gate.Jdbc.Password: 'password' # STRING (JDBC password)

In the above example, the gateway is configured to connect to the REPORTER database using the user netcool.

Additional parameters can be specified as needed. In the following example the connection URL specifies the instance name and that a non-secure connection should be used:

Gate.Jdbc.Url: 'jdbc:sqlserver://<hostname or IP address of Microsoft SQL Server>:1433;databaseName=REPORTER;instanceName=MSSQLSERVER;encrypt=false' # STRING (JDBC connection URL)

If a secure connection is required to be used by the gateway, the following should be configured:

Gate.Java.Arguments: ‘-Dcom.ibm.jsse2.overrideDefaultTLS=true -Djavax.net.ssl.trustStore=<path to truststore file> -Djavax.net.ssl.trustStorePassword=<truststore file password>’
Gate.Jdbc.Url: 'jdbc:sqlserver://<hostname or IP address of Microsoft SQL Server>:1433;databaseName=REPORTER;instanceName=MSSQLSERVER;encrypt=true' # STRING (JDBC connection URL)