IBM Support

QRadar: Configuring JDBC Over SSL with a Self-signed certificate

Question & Answer


How to configure a QRadar® log source that uses the option "JDBC Over SSL" with a self-signed certificate.


Before you begin

These instructions were written from a configuration completed on Windows Server 2008 R2. There might be some variation in these instructions and dialog boxes during your configuration. The dialog boxes and user interface options are dependent on the Windows Operating System that the SQL Server is hosted on.

**IMPORTANT**: Administrators using JDBC log source to connect that require TLS v1.2 must have Microsoft SQL Server version 10.0.6547.0 installed as noted by Microsoft in KB4057114:
Administrators who do not have this version installed and attempt to connect using TLS v1.2 might experience the following error messages in /var/log/qradar.log: "[ERROR] Chained SQL Exception [1/1]: I/O Error: Read timed out".

Step 1: Determine the fully qualified domain name of the database server (not the database listener host) hosting the SQL Server instance

Administrators can use these steps to determine the FQDN for the database server host. It is important you do not use the listener host information when you create your certificate.

  1. Log in to the Windows host with the SQL Server Instance and open a command prompt.
  2. To view network connection information from the command prompt, type: ipconfig /all
  3. Make note of the Fully Qualified Domain Name (FQDN), which is the Host Name + Primary DNS Suffix. This information is required when you generate your self-signed certificate.

Step 2: Generate a self-signed certificate and key

  1. Using SSH, log in to the QRadar Console.
  2. Navigate to the following directory: /opt/qradar/conf/trusted_certificates/
  3. To create a self-signed certification, type the following command:  openssl req -x509 -nodes -days <days> -sha512 -newkey rsa:2048 -keyout <filename>.key -out <filename>.cert

    Replace <filename> with an appropriate and identifiable name for the SQL Server Instance and specify <days> as the number of days to certify the certificate.

    NOTE: You will be asked to enter some information such as the country name, organization name, Common Name. You must type the FQDN of the server hosting the SQL Server instance in the Common Name field. The rest of the values can be populated with any values the administrator deems appropriate.
  4. Verify that the certificate and key are generated in the directory.

Step 3: Export certificate and key into a PKCS #12 key store

  1. To export the certificate and key generated to a PKCS #12 key store, type the following command: openssl pkcs12 -export -in <filename>.cert -inkey <filename>.key > <store_name>.p12

    Replace <filename> with the file name of the certificate and key file, then replace the <store_name> with a name for the keystore.
  2. Provide a password for the private key being generated.
  3. Verify the password of the private key.
  4. Verify that the keystore (.p12 file) is generated in the directory.
  5. Copy the keystore (.p12 file) to the Windows host with the SQL Server Instance.

    What to do next
    Now the administrator can log in to the Windows host and can import the .p12 keystore file to the Windows host.

Step 4: Import the PKCS #12 key store using the Microsoft Management Console (MMC)

Procedure 1: Import the keystore
  1. Log in to the Windows host with the SQL Server Instance.
  2. From the Start menu, click Run.
  3. In the search box, type mmc and click OK.
    Microsoft Management Console (MMC) is displayed.
  4. From the navigation bar, select File > Add/Remove Snap-in.
  5. Select Certificates and click Add.
  6. Select Computer account and click Next.
  7. Select Local computer and click Finish.
    The Add or Remove Snap-in window is displayed.
  8. Click OK.
  9. Expand the Certificates tree and right-click Personal > All Tasks > Import.
  10. In the Certificate Import Wizard, click Next.
  11. Click Browse to locate the p12 keystore file and click Next.
  12. Type the password for the private key and click Next.
  13. Select Place all certificates in the following store.
  14. Verify that the Certificate Store field lists Personal as the store location and click Next.
    Note: If Personal is not selected as the Certificate store, click Browse and select Personal as the certificate store. Then click OK and click Next
  15. Verify the summary for the certificate import and click Finish. A status message informs you if the import was successful and a Certificates folder is created in the Personal folder with the certificate.

    What to do next
    You are now ready to import the private key to the Windows host.

Procedure 2: Adding the private key
  1. In the Certificates snap-in, expand the Persnal folder to view the hostname key.
  2. Right-click on the imported certificate and select All Tasks > Manage Private Keys.
  3. In the Permissions dialog, Click Add.
  4. Click Advanced.
  5. From the Common Queries dialog click Find Now.
    This option populates a search result for possible users and groups..
  6. Select your SQL service account SQLServerMSSQLUser$<Host Name>$<SQL Server Instance> and click OK.

    NOTE: If the above steps are not followed to grant the SQL service account read permissions, then the SQL Server will not load the imported certificate due to insufficient permissions. If the certificate is not imported, the following error is displayed in SQL Server log:

    Server Unable to load user-specified certificate [Cert Hash(sha1) "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See “Configuring Certificate for Use by SSL” in Books Online.
  7. In the Select Users or Groups dialog, click OK.
    The SQL service account user is added to the permission list.
  8. Click OK to return to the Certificates snap-in.
  9. Right-click on the imported certificate and select Copy.
  10. Expand the Trusted Root Certification Authorities, right-click in an open area and select Paste.

    Now to limit the personal certificate to enable only server authentication.
  11. Expand Personal and select your imported certificate.
  12. Right-click and select Properties.
  13. Select Enable only the following purposes.
  14. From the list of purposes, uncheck all values except for Server Authentication and click OK.

    The Certificates snap-in window is displayed.
  15. Expand Personal and double-click on the imported certificate.
  16. Review the General tab to ensure the following:
    1. The message You have a private key that corresponds to this certificate is displayed.
    2. The Issued to field is correct.
    3. The Valid to/from dates are correct.
  17. Click the Details tab and verify the following:
    1. The Subject field must contain the server name.
    2. The Enhanced Key Usage field must display Server Authentication.
    3. Note the value in the Thumbprint field. This value is used later to verify the correct certificate is being loaded by the SQL Server.
  18. Click the Certification Path tab and verify the server name is correctly displayed Certification path.

    What to do next
    Now the SQL Server must be configured to accept the certificate and force encryption on connections made to the SQL Server Instance.


Step 5: Modify the SQL Server configuration to force encryption

  1. From the Start menu, click Run.
  2. In the text box, type SQLServerManager10.msc

    NOTE: This command launches the configuration manager for SQL Server 2008. For other SQL Server versions, use SQLServerManager.msc or SQLServerManager12.msc.
  3. In the SQL Server Configuration Manager, expand SQL Server Network Configuration.
  4. Right click on Protocols for <SLQ Server Instance> and click Properties.
  5. From the Properties dialog click the Flags tab.
  6. Locate the Force Encryption flag check box and select Yes.
  7. Click the Certificate tab.
  8. From the Certificate drop-down, select your imported certificate and click OK.
    The system will display a warning message that you are forcing an encryption check for all connections.
  9. Click OK.

    What to do next
    Now you are ready to verify that encryption is configured. The next step uses the Thumbprint value you recorded from an earlier step.


Step 6: Verify that SQL Server is setup for using encryption

  1. On the Start menu, click Run.
  2. In the text box, type ssms and click OK.

    NOTE: This command opens the SQL Server Management Studio for SQL Server 2008. For SQL Server 2005, use 'sqlwb'.
  3. In the Connect to Server dialog, type the information required to connect to the SQL Server.
  4. In object Explorer, right click on the Server, click Restart. Note that the service can be restarted from SQL Server Configuration Manager (under SQL Server Services) as well.
  5. Click Yes on the restart dialog.
  6. In Object Explorer, expand Management, expand SQL Server Logs, double click on the Current log. Log File View will open.
  7. Scroll down through the log entries or use the search functionality to find an entry similar to the following

    The certificate [Cert Hash(sha1) "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"] was successfully loaded for encryption.
  8. Verify that the hash value matches the Thumbprint value from your imported certificate.

    What to do next
    Now the administrator can enable the Use SSL check box in the JDBC log source configuration. As long as the certificates reside on the proper QRadar appliance making the JDBC connection, then the log source will be able to retrieve the event data from the remote SQL Server.


Step 7: Enable “Use SSL” option

NOTE: Administrators using JDBC log source to connect that require TLS v1.2 must have Microsoft SQL Server version 10.0.6547.0 installed as noted by Microsoft in KB4057114: .
  1. Log in to QRadar as an admin user.
  2. Click the Admin tab.
  3. Click the Log Sources icon.
  4. Either create a new log source or edit the appropriate JDBC log source for your Microsoft SQL Server.
  5. In the log source configuration, select the Use SSL check box.
  6. Click Save.
  7. Review the Log Activity tab to verify that data is retrieved.

Microsoft, Windows, Windows NT, and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both.

[{"Type":"SW","Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSBQAC","label":"IBM Security QRadar SIEM"},"ARM Category":[{"code":"a8m0z000000cwt0AAA","label":"Log Source"}],"ARM Case Number":"","Platform":[{"code":"PF016","label":"Linux"}],"Version":"All Version(s)"}]

Document Information

Modified date:
14 April 2021