Configure DB Connect version 3.6.x inputs for the Splunk Add-on for Microsoft SQL Server

To gather trace logs, audit logs, and data from Dynamic Management Views, the Splunk Add-on for Microsoft SQL Server leverages Splunk DB Connect. Follow the instructions that correspond to the version of DB Connect that you have installed. This topic presents the instructions for DB Connect Version 3.6 and above.

To prepare your environment and configure your inputs, follow these steps.

1. Set up the database connection
2. Configure the inputs
3. Adjust your auto KV extraction settings
4. Configure the database server lookup

Set up the database connection

Setting up the database connection involves three steps:

1. Download and install the Microsoft JDBC driver for SQL Server.
2. Create an identity in Splunk platform.
3. Use the Splunk DB Connect GUI to create a database connection or use db_connections.conf to create a database connection.

Download and install the Microsoft JDBC driver for SQL Server

To enable Microsoft SQL Server connections, download and install the Microsoft JDBC Driver for SQL Server as described in the Install database drivers section of the Deploy and Use Splunk DB Connect manual.

Create an identity in the Splunk platform

1. Restart the Splunk platform instance.
2. Create an identity for establishing a connection to the database. Make sure the user for this identity has the system role. You can use a username and password for authentication, or Windows Authentication. However, using DB Connect version 3.1 with Windows Authentication and the JDBC driver for SQL Server requires additional steps. See Can't use Windows authentication for Microsoft SQL Server with Microsoft JDBC Driver for SQL Server in the DB Connect manual for more information.
3. Next, you need to create a database connection to the SQL Server using either the Splunk DB Connect GUI or the db_connections.conf file as described in the following sections.

Use the Splunk DB Connect GUI to create a database connection

To create a database connection to the SQL Server database using the Splunk DB Connect GUI, refer to Create and manage database connections in the Splunk DB Connect manual for step-by-step instructions.

Enter the following parameters:

Parameter	Value
Connection Name	Enter a unique connection name.
Identity	Use the identity you created above.
Connection Type	Choose MS-SQL Server Using MS Generic Driver or MS-SQL Server Using jTDS Driver based on which driver you are using.
Host	Enter the host IP address where the SQL Server database is running.
Port	The default port for SQL Server database is 1433.
Default Database:	Enter the database name on SQL Server.

Note: If you need to define IPv6 address as a host, you might need to edit JDBC URL and use specific notation, such as: jdbc:sqlserver://;serverName=::1;portNumber=1433;databaseName=master;selectMethod=cursor;encrypt=true;trustServerCertificate=true

Use db_connections.conf to create a database connection

If you do not want to use the DB Connect GUI, you can create a database connection to the SQL Server database using the db_connections.conf file.

1. Create a file called db_connections.conf in the %SPLUNK_HOME%\etc\apps\splunk_app_db_connect\local directory.
2. Copy the stanza below to db_connections.conf and edit the values of each field to reflect your production environment.

   <connection name>
   connection_type = <connection type: generic_mssql or mssql>
   database = <database name>
   host = <host or ip address of the SQL Server database>
   identity = <identity name used for the connection>
   jdbcUseSSL = <enable SSL>
   port = <network port of the SQL Server database>
   

When you create a database connection object for your Microsoft SQL Server, select the appropriate database and driver from the Database Types pop-up menu. There are two options: MS-SQL Server Using MS Generic Driver if you downloaded the Microsoft driver, or MS-SQL Server Using jTDS Driver if you are using the open source jTDS driver.

Configure database inputs using the Splunk DB Connect GUI

Refer to Create and manage database inputs in the Splunk DB Connect manual for step by step instructions configuring your database inputs in the GUI.

If you want to create Microsoft SQL Server input, choose the template created for Splunk Add-on for Microsoft SQL Server under Template field of DB Connect. .

Adjust your auto KV extraction settings

Some source types, such as mssql:execution:dm_exec_query_stats retrieve fields with multiple lines. To ensure that your fields show the full values that you expect, adjust your KV extraction settings.

1. Open %SPLUNK_HOME%\etc\system\local\limits.conf.
2. Add or change this stanza:

[kv]
maxchars = 20480

Configure the database server lookup

Supply the host and port values for each of your database servers. This step is required to integrate with Splunk IT Service Intelligence.

1. On each of your search heads, open %SPLUNK_HOME%\etc\apps\Splunk_TA_microsoft-sqlserver\lookups\sqlserver_host_dbserver_lookup.csv.
2. Edit this file to include correct host and port values for each of the database_server in your event data.
3. Save the file.
4. Restart the search head.

Configuring MSSQL to collect data through Windows AD

All the data obtained via DB Connect are from Dynamic Management View or Dynamic Management Function including both server-scoped and database-scoped. This requires a user with both VIEW SERVER STATE permission and VIEW DATABASE STATE permission.

For example, If a user called 'splunk' is created, admin user needs to grant the permission to the user using the following SQL command:

GRANT VIEW SERVER STATE TO splunk; GRANT VIEW DATABASE STATE TO splunk;

For more information, check the oficial SQL Server Doc