Cloud-delivered Data LakeExabeam Data Lake Collector Guide

Create self-signed SSL certificates and configure MS-SQL server in Windows.

Open the Internet Information Services (IIS) Manager of your MS-SQL server.

Select your MS-SQL host and then Server Certificates.

Select Create Self-Signed Certificate.

Name the certificate and then click OK to save.

Your self-signed certificate should appear in the Server Certificates listing.

Close the IIS Manager.

Open the SQL Server Configuration Manager.

Select SQL Server Network Configuration > Protocols [mssql_servername] > Properties.

In the Flags tab, enable Force Encryption by selecting Yes.

In the Certificate tab, select your self-signed certificate in the Certificate drop-down.

At the SQL Server Configuration Manager, select your server. Right-click to open the submenu and select Restart.

At the IIS Manager, Export the certificate to a PFX-formatted file.

In the Export Certificates menu, enter a PFX filename and password.

Click OK to create the file.

Copy the PFX file to the Data Lake master host and generate a truststore and keystore using keystore. (Install Java SDK or JRE to obtain the keystore tool, if Java has not been installed on the host.)

keytool -importkeystore -srckeystore mssql-final.pfx -srcstoretype pkcs12 -destkeystore /opt/exabeam/config/common/kafka/ssl/db.keystore.jks -deststoretype JKS
keytool -importkeystore -srckeystore mssql-final.pfx -srcstoretype pkcs12 -destkeystore /opt/exabeam/config/common/kafka/ssl/db.truststore.jks -deststoretype JKS

MS-SQL server command line, generate the self-signed SSL key using the domain name or IP address of server where MS-SQL is installed. This example produces a 1-year key.

openssl req -x509 -nodes -newkey rsa:2048 -subj '/CN=<domainname_or_ip>' -keyout mssql.key -out mssql.pem -days 365

Create pkcs12 storage with the certificate and key.

openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -name "mysqlclient" -passout pass:mypassword -out store.p12

Send this store to the Data Lake master host and generate truststore and keystore.

keytool -importkeystore -srckeystore store.p12 -srcstoretype pkcs12 -srcstorepass mypassword -destkeystore /opt/exabeam/config/common/kafka/ssl/db.keystore.jks -deststoretype JKS -deststorepass mypassword 
keytool -importkeystore -srckeystore store.p12 -srcstoretype pkcs12 -srcstorepass mypassword -destkeystore /opt/exabeam/config/common/kafka/ssl/db.truststore.jks -deststoretype JKS -deststorepass mypassword

To configure access permissions and move to keys for the MS-SQL server on the Data Lake master host, at the Data Lake host, run the following:

sudo chown mssql:mssql mssql.pem mssql.key 
sudo chmod 600 mssql.pem mssql.key 
sudo mv mssql.pem /etc/ssl/certs/ 
# Create this directory if it does not already exist. 
sudo mkdir /etc/ssl/private/ 
sudo mv mssql.key /etc/ssl/private/ 
sudo systemctl stop mssql-server 
sudo /opt/mssql/bin/mssql-conf set network.tlscert /etc/ssl/certs/mssql.pem 
sudo /opt/mssql/bin/mssql-conf set network.tlskey /etc/ssl/private/mssql.key 
sudo /opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2 
sudo /opt/mssql/bin/mssql-conf set network.forceencryption 0 
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true 
# The next line will print out the MS SQL server configuration. 
sudo cat /var/opt/mssql/mssql.conf sudo systemctl start mssql-server

Download the Microsoft JDBC Driver for SQL Server and place it in /opt/exabeam/data/lms/dblog/.

Edit configurations in /opt/exabeam/config/lms/dblog/conf/ folder in Data Lake node. The following example is for /opt/exabeam/config/lms/dblog/conf/logstash-dblog-input.conf:

input {
    # config example for connecting to MSSQL
    jdbc {
        jdbc_driver_library =>"/opt/jdbc-drivers/sqljdbc_6.0/enu/jre8/sqljdbc42.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        jdbc_connection_string => "jdbc:sqlserver://<ms-sql_ip>:1433;databasename=TestDB;encrypt=true;trustServerCertificate=false"
        jdbc_user => "<adminrole_user>"
        jdbc_password => "<adminrole_user_password>"
        schedule => "* * * * *"
        statement => "SELECT * from Inventory"
        last_run_metadata_path => "/opt/logstash/config/.last_run"
    }
}

Add truststore and keystore parameters to jvm.options.

sudo vim /opt/exabeam/config/lms/dblog/jvm.options

Then add the following parameters:

-Djavax.net.ssl.trustStore=/opt/logstash/ssl/<truststore_filename>
-Djavax.net.ssl.trustStoreType=JKS
-Djavax.net.ssl.trustStorePassword=<truststore_password>
-Djavax.net.ssl.keyStore=/opt/logstash/ssl/<keystore_filename> 
-Djavax.net.ssl.keyStoreType=JKS
-Djavax.net.ssl.keyStorePassword=<keystore_password>

Install and start dblog.

sudo sh /opt/exabeam/bin/lms/lms-dblog-install.sh

Allow 3 minutes of processing and then check dblog logs to verify activity.

sudo journalctl -u exabeam-lms-dblog -e

Inspect the output that it does not contain error messages. Messages like this should be found:

[INFO ][logstash.inputs.jdbc ] (0.118623s) SELECT * from Inventory

Run the following commands to create the Certificate Authority (CA) keys:

openssl genrsa 2048 > /mysql_keys/ca-key.pem
openssl req -sha1 -new -x509 -nodes -days 3650 -key /mysql_keys/ca-key.pem > /mysql_keys/ca-cert.pem

Run the following commands to create the server SSL key and certificate:

openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout /mysql_keys/server-key.pem > /mysql_keys/server-req.pem
openssl x509 -sha1 -req -in /mysql_keys/server-req.pem -days 3650 -CA /mysql_keys/ca-cert.pem -CAkey /mysql_keys/ca-key.pem -set_serial 01 > /mysql_keys/server-cert.pem
openssl rsa -in /mysql_keys/server-key.pem -out /mysql_keys/server-key.pem

Run the following commands to create the client SSL key and certificate:

openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout /mysql_keys/client-key.pem > /mysql_keys/client-req.pem
openssl x509 -sha1 -req -in /mysql_keys/client-req.pem -days 3650 -CA /mysql_keys/ca-cert.pem -CAkey /mysql_keys/ca-key.pem -set_serial 01 > /mysql_keys/client-cert.pem
openssl rsa -in /mysql_keys/client-key.pem -out /mysql_keys/client-key.pem

Import client private key and certificate to a keystore:

openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -name "mysqlclient" -passout pass:mypassword -out client-keystore.p12
keytool -importkeystore -srckeystore client-keystore.p12 -srcstoretype pkcs12 -srcstorepass mypassword -destkeystore keystore -deststoretype JKS -deststorepass mypassword

Import CA certificate to a trustore:

keytool -importcert -alias MySQLCACert -file ca.pem \ -keystore truststore -storepass mypassword

Send the truststore and keystore to the LMS server.

Open the /etc/my.cnf file with your preferred text editor.

Insert the following lines in the [mysqld] section of the my.cnf file:

ssl-cipher=DHE-RSA-AES256-SHA
ssl-ca=/mysql_keys/ca-cert.pem
ssl-cert=/mysql_keys/server-cert.pem
ssl-key=/mysql_keys/server-key.pem

Insert the following lines in the [client] section of the my.cnf file (If the [client] section does not exist, you must add a [client] section):

ssl-cert=/mysql_keys/client-cert.pem
ssl-key=/mysql_keys/client-key.pem

Your updated my.cnf file should resemble the following example:

[mysqld]
max_connections=500
log-slow-queries

max_allowed_packet=268435456
open_files_limit=10000
default-storage-engine=MyISAM
innodb_file_per_table=1
performance-schema=0

ssl-cipher=DHE-RSA-AES256-SHA
ssl-ca=/mysql_keys/ca-cert.pem
ssl-cert=/mysql_keys/server-cert.pem
ssl-key=/mysql_keys/server-key.pem
[client]
ssl-cert=/mysql_keys/client-cert.pem
ssl-key=/mysql_keys/client-key.pem

Save your changes to the /etc/my.cnf file and exit your text editor.

Run the following command to update the file permissions of the /mysql_keys directory and its files:

chown -Rf mysql/mysql_keys

Restart MySQL:

sudo systemctl restart mysqld

View MySQL's active SSL configuration to verify activity:

mysql -e "show variables like '%ssl%';"
The output will resemble the following example:
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /mysql_keys/ca-cert.pem |
| ssl_capath | |
| ssl_cert | /mysql_keys/server-cert.pem |
| ssl_cipher | DHE-RSA-AES256-SHA |
| ssl_key | /mysql_keys/server-key.pem |
+---------------+------------------------+   

Create the wallet:

orapki wallet create -wallet <WALLET DIRECTORY>

Add the self-signed certificate:

orapki wallet add -wallet <WALLET DIRECTORY> -dn CN=<CERTIFICATE NAME>,C=US -keysize 2048 -self_signed -validity 3650

Check the wallet:

orapki wallet display -wallet <WALLET DIRECTORY>

Export certificate:

orapki wallet export –wallet <WALLET DIRECTORY> -cert <SERVER CERTIFICATE FILE NAME>

Add certificate to truststore:

keytool -importcert -alias <ALIAS NAME> -keystore <PATH TO TRUSTSTORE> -file <FILE PATH TO SERVER CERTIFICATE>

Create keystore with client certificate (only for two-way authentication):

keytool -genkeypair -alias <ALIAS NAME> -keyalg RSA -validity 365 -keysize 2048 -keystore <PATH TO KEYSTORE>

Export client certificate from keystore (only for two-way authentication):

keytool -export -alias <ALIAS NAME> -keystore <PATH TO KEYSTORE> -rfc -file <FILE PATH TO CLIENT CERTIFICATE>

Import client certificate into wallet (only for two-way authentication):

orapki wallet add -wallet <WALLET DIRECTORY> -trusted_cert -cert <FILE PATH TO CLIENT CERTIFICATE>

Send truststore and keystore to Data Lake server.

Add TCPS protocol to the listener.ora file.

LISTENER =
(DESCRIPTION_LIST =
...
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = <SERVER HOST NAME>)(PORT = 2484))
)
...
)

Add TCPS protocol to the tnsnames.ora file, you need it for connection to oracle database with SQLPLUS command line tool.

<SERVICE NAME>_secure =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCPS)
(HOST = <SERVER HOST NAME>)
(PORT = 2484)))
(CONNECT_DATA =(SERVER = DEDICATED)
(SERVICE_NAME = <SERVICE NAME>))
(SECURITY =(MY_WALLET_DIRECTORY = <WALLET DIRECTORY>)))
*<SERVICE NAME> - name of your main service, which stores your data

Add the Oracle Wallet location to the sqlnet.ora and listener.ora files.

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = <WALLET DIRECTORY>)))

Set client authentication to the sqlnet.ora and listener.ora files.

For two-way authentication:

SSL_CLIENT_AUTHENTICATION = TRUE

For one-way authentication:

SSL_CLIENT_AUTHENTICATION = FALSE

Add TCPS protocol to the sqlnet.ora file.

SQLNET.AUTHENTICATION_SERVICES= (..., TCPS)

Restart oracle listener:

lsnrctl stop
lsnrctl start

Open port 2484 in firewall rules or stop firewall on the server.

Put truststore and keystore to this directory: /opt/exabeam/config/common/kafka/ssl/

Put downloaded Oracle JDBC driver (ojdbc8.jar) to this directory: /opt/exabeam/data/lms/dblog

Add truststore and keystore to dblog jvm.options file:

1. Open jvm.options:

   sudo vim /opt/exabeam/config/lms/dblog/jvm.options 

2. Add this to the end of file:

   -Djavax.net.ssl.trustStore=/opt/logstash/ssl/<TRUSTSTORE FILE NAME>
   -Djavax.net.ssl.trustStoreType=JKS
   -Djavax.net.ssl.trustStorePassword=<TRUSTSTORE PASSWORD>
   # Uncomment and edit next three lines for two-way authentication
   #-Djavax.net.ssl.keyStore=/opt/logstash/ssl/<KEYSTORE FILE NAME>#-Djavax.net.ssl.keyStoreType=JKS
   #-Djavax.net.ssl.keyStorePassword=<KEYSTORE PASSWORD>

Configure jdbc_connection_string in logstash dblog input file for oracle database connection:

Location of file:

sudo vim /opt/exabeam/config/lms/dblog/conf/logstash-dblog-input.conf

Set connection string:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<hostname>)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=<service_name>))

Example of logstash input file:

input {
# config example for connecting to Oracle
jdbc {
jdbc_driver_library =>"/opt/exabeam/data/lms/dblog/ojdbc8.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"jdbc_connection_string => "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=10.10.19.79)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"# jdbc_user_id => "USERID"
jdbc_user => "system"
jdbc_password => "password"schedule => "* * * * *"
statement => "SELECT * from customers"
# use_column_value => true
# tracking_column => ID_FIELD
last_run_metadata_path => "/opt/logstash/config/.last_run"
}
}

Allow 3 minutes of processing and then check dblog logs to verify activity.

sudo journalctl -u exabeam-lms-dblog -e

A message like this should be found:

[2018-09-28T14:26:00,157][INFO ][logstash.inputs.jdbc ] (0.000767s) SELECT * from customers