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

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)

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

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.