As part of your cybersecurity assessment, perhaps you’re wanting to use Kerberos authentication to query a Microsoft SQL Server and output that data in syslog format. Apache NiFi is capable of doing all of this with minimal configuration.
In this post, the third of our series on using NiFi, you’ll learn how to set up Apache NiFi to pull from a SQL database with Kerberos authentication, convert the output to JSON format, then stream that to a remote log collection platform.
Figure 1: Start with the host details.
Test Domain Configuration
Note: If you already have SQL Server connected to Active Directory and use Kerberos authentication, you can likely skip to the Creating the NiFi Flow subheader.
For our test domain, yourdomain.corp, we deployed Windows Server 2019 (AD-00) for its Active Directory services and another Windows Server 2019 (SQL-00) running SQL Server 2019. Figure 1 shows that we also setup NiFi on a Ubuntu server (NIFI-DEV).
Both the SQL-00 and NIFI-DEV servers point to the AD-00 server for authentication. A simple NSLOOKUP query lets you check Kerberos settings (Figure 2).
Figure 2: Enter DNS details.
on the SQL Server. Because we didn’t have an actual SQL database to test against, we installed Microsoft SQL Server Management Studio and downloaded the AdventureWorks database for this purpose.
Next we created a user account — nifilogin — for database access, using “Windows authentication” for the user authentication type.
You can configure NiFi using our prior instructions found here. It’s a Java app that can run on various platforms, but here we’re using Ubuntu 20.04 and OpenJDK 11.
Ensure the NiFi server uses the same DNS server as your Active Directory domain. It doesn’t need to be part of the AD environment, but does need to use the same DNS server. For our testing, we used AD-00 as the DNS resolver. This lets the NIFI-DEV server resolve everything associated with the YOURDOMAIN.CORP domain.
Adding SQL drivers and configuring Kerberos
There are a number of JAR files depending upon your Java version. We downloaded the version 11 SQL driver from this GitHub repository. Next we created the Kerberos configuration directory:
sudo mkdir /opt/nifi/kerberos
Then it was on to adding its configuration files (Figure 3).
Figure 3: Next input the Kerberos configuration.
- debug=true may be removed from a production version.
- Kerberos likes everything to be in uppercase (Figure 4).
Figure 4: The Kerberos configuration.
java.argshould be sequential, e.g.,
java.arg.20 is only included for debugging purposes and may be left out.
Figure 5: Configuring Bootstrap.
Figure 6: Restarting NiFi.
Creating the NiFi Flow
Figure 7: Login to NiFi.
Now you’ll log into the NiFi interface and listen on the default port 8080 (Figure 7). Then you’ll add DBCPConnectionPool controller service to provide the NiFi flow with the SQL connection details, including its authentication components. To do this:
- Click a blank portion of the NiFi canvas, such that nothing is selected.
- Click the gear icon in the lower-left portion of the canvas.
- Go to Controller Services and click the +
- Select DBCPConnectionPool, then click Add.
Figure 8: Renaming the database connection.
Now rename DBCPConnectionPool to MS-SQL(Figure 8) for easy later identification and add the following settings to it (Figure 9):
Database Connection URL:
Database Driver Class Name: com.microsoft.sqlserver.jdbc.SQLServerDriver
Database Driver Location: /opt/nifi/drivers/mssql-jdbc-8.3.1.jre11-preview.jar
Database User: nifilogin@YOURDOMAIN.CORP
For a production environment, you’ll want to swap out its host name, database name, driver location, and credentials with your own values. Also change Database Connection URL to reflect your SQL server and the database name you’ll be querying.)
In addition, the Database Driver Location will include the name of the MS-SQL JAR file you downloaded earlier. Be sure to change the Database Name and Password (Figure 9).
Figure 9: Renaming the database connection.
Figure 10: Click the lightning bolt to enable the service.
Figure 11: Enable the controller service.
Figure 12: Add QueryDatabaseTable as the first processor.
Scheduling (query the table for new data once per minute):
Run Schedule: 1 min
|Database Connection Pooling Service: MS-SQL||The controller service you created earlier|
|Database Type: MS SQL 2012+||This version should cover most use cases|
|Table Name: HumanResources.Employee||Essentially a “select * from HumanResources.Employee”|
|Maximum-value Columns: BusinessEntityID||An auto-incrementing value to prevent NiFi from fetching the entire table each time|
Table 1: QueryDatabaseTable processor properties
The next processor you’ll add instructs NiFi to take the database query results and convert them to JSON format. This works great with a SQL query since you’re dealing with name:value pairs.
Using the same method as with the first processor, add the ConvertAvroToJSON processor. To configure it, simply select the failure option in Settings > Automatically Terminate Relationships (Figure 13).
Figure 13: Check ConvertAvroToJSON settings.
The final processor to add takes the individual events and converts them to native syslog format. So for this last PutUDP processor, select both the failure and success options in Settings > Automatically Terminate Relationships. This terminates the data flow. Add the following values to its Properties:
Now you’ll connect the four processors (Figure 14).
Figure 14: Connect the four processors.
Figure 16: The syslog output in Elastic.