Create and manage database connections

A database connection object contains the necessary information for connecting to a remote database.

Create a database connection

To create a new connection:

1. From within Splunk DB Connect, select the Configuration > Databases > Connections tab.
2. select New Connection.

   Note: If you have not yet created an identity, the New Identity window appears. Create a new identity, and then you can create a new connection.

3. On the New Connection page, complete the following fields:
• Connection name
• Identity: Select the identity you want to use with this connection.
• Connection Type: A list of supported databases. Select the type of database to which you're connecting.

  Note: This list contains all supported databases, regardless of whether you installed their drivers. You must first install the database driver for the database type you want to use. You can't select a database from this list without first installing the corresponding driver. For information about the Microsoft SQL Server options listed here, see "Microsoft SQL Server".

• Timezone: If you want to convert data of date and time types read from the database into the Splunk server's local time zone, enter the source database time zone for the conversion. When you set the time zone, you have two time zone conversion options depending on the localTimezoneConversionEnabled setting in db_connections.conf:
  • When you don't define localTimezoneConversionEnabled or set it to false, the system only converts index time values read from table columns in the source database into the local time zone. This conversion option only applies to inputs.
  • When you set localTimezoneConversionEnabled to true, the system converts all date and time data type (datetime, time, date, timestamp) values into the local time zone except source datetime values that already include time zone information For example, TIMESTAMP WITH TIMEZONE). This conversion option applies to inputs, dbxqueries, and dblookups.
  If you leave the Timezone field blank, the system assumes that the source database time zone and Splunk server's local time zone are the same and reads the date and time data as is.
  Example

  	Timestamp in database	Database time zone	Splunk Server (JVM) time zone	Indexed timestamp value
  Without timezone conversion	2006-02-15 04:34:33	UTC	Asia/Shanghai (GMT+8)	2006-02-15 04:34:33 in Asia/Shanghai time zone
  With timezone conversion	2006-02-15 04:34:33	UTC	Asia/Shanghai (GMT+8)	2006-02-15 12:34:33 in Asia/Shanghai time zone

  Note:

  • If the column in your database contains timezone information For example, for the column with a TIMESTAMP WITH TIMEZONE type), Splunk ignores the timezone you set here.
  • The timezone setting defaults to JVM time zone. If there are no JVM time zone settings, Splunk uses the time zone of your operating system.
  • For MySQL database driver, you need to set useLegacyDatetimeCode setting to false in the JDBC URL if you want to use the timezone setting.

JDBC URL Settings

• Host: Enter the address, or host, of the database.
• Port: (Optional.) Enter the port number of the database. You don't need to enter a port number here if your database is using its default port.
• Default Database: Enter the default database or catalog name for the database type you chose. The usage and meaning of this parameter varies between database vendors, so check your database vendor's documentation. For more information, see supported databases matrix.
• Enable SSL: Select this check box to enable Secure Sockets Layer (SSL) encryption for the connection. SSL support is not available for all connection types. For further information, see supported databases matrix and Enable SSL for your database connection. To find out how to connect to Oracle using SSL for encryption, see Connect to Oracle using SSL (for encryption only).
• Certificate: When you select the Enable SSL check box, the Certificate field appears for the MySQL, MSSQL and Oracle connection types. You can pass a certificate which Splunk automatically adds to the Java TrustStore and uses it for the server authentication. If you select the Enable SSL check box but leave the certificate field empty, then DB Connect ignores the certificate and functions as before.
• JDBC URL Preview A Java Database Connectivity (JDBC) Uniform Resource Locator (URL) is a URL that encodes all the necessary information for connecting to your database. The JDBC URL Preview field is not editable by default, but Splunk populates this field, using placeholder values, with the correct URL format according to the database type you have chosen.
• Connection properties Splunk uses the provided properties during the creation of the connection. Splunk provides properties either here or through the editing of the JDBC URL. You can find a list of all valid driver connection properties in the official database documentation. Splunk ignores incorrect properties.
• You can manually edit the URL format by selecting Edit JDBC URL. When you select this field, the JDBC URL Preview becomes editable. This is useful if you need to add customized JDBC URL parameters to the JDBC URL, but that is not Splunk best practice unless you already know what customizations you want to add. Connection parameters vary among JDBC drivers. Consult your database vendor's documentation for a list of supported parameters and values.

Note: DB Connect supports LDAP connection, you need to select the LDAP URL in JDBC URL field. Consult your database vendor's documentation on how to generate LDAP URL.

Advanced Settings

• Fetch Size: (Optional.) Enter the number of rows to return at a time from the database. If you leave this field blank, it defaults to 300.
• Readonly: Select this check box to indicate your intention for users to only use SELECT statements with the database. Be aware that this cannot always guarantee read-only access. DB Connect tries to ensure that it's read-only, but it is the database driver that ultimately allows or prevents changes. If you intend to use the read-only option, ensure that, on the database itself, the user you're connecting as only has read-only access. See Read-only connections for more details.
4. In the Permissions table, update the Splunk Enterprise permissions for this database connection. For more information, see Permissions.
5. Select Save to save the connection.

   Note:If the connection you create is valid, you can save the connection succesfully. Otherwise Splunk displays an the error message for you to check the configuration of the connection and save again.

Edit connections

Go to Configuration > Databases > Connections to see a list of the defined database connections.
Note: The list of connections that you can see depends on the permissions set on each connection. For more information, see Permissions.

Connection actions

You can make changes to a connection using the following buttons on the connection page under the Edit tab:

• Disable/Enable: Disable/Enable a connection by selecting Disable/Enable here. You cannot disable a connection if any inputs, outputs, or lookups are using it. In that case, Splunk greys out this button.
• Edit: Edit the connection by clicking its name or edit button.
• Clone: Creates a copy of the connection. You must give the copy a new name.
• Delete: Deletes the connection. You cannot delete a connection if any inputs, outputs, or lookups are using it. In that case, Splunk greys out this button.

You can also edit the attributes of a connection listed in Create a database connection, except its name. To change the name of a connection, clone it, give the clone the name you want, and then delete the original connection.

Permissions

The Permissions table is where you select the Splunk Enterprise roles that have read, read-write, or no access to the connection.

• Read access means that Splunk Enterprise roles can use the connection.
• Write access means that Splunk Enterprise roles can use and modify the connection.
  

By default, the Splunk Enterprise "admin" and "db_connect_admin" roles have write access to a new connection, the "db_connect_user" role has read access, and all other roles have no access.

Override db_connection_types.conf

For fine-grained control over your database connections, you can override the JDBC connection strings for your database driver. The db_connection_types.conf file lists the supported database types, driver parameters, and test queries. To override it, copy the db_connection_types.conf file under $SPLUNK_HOME/etc/apps/splunk_app_db_connect/default to $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local.

You can now fine-tune your database connection by editing its JDBC parameters. If the stanza name in the db_connection_types.conf in local is identical to the name in the same file in default, Splunk overrides its connection settings. Consult your JDBC driver vendor's documentation for exact parameter syntax. The db_connection_types.conf.spec file helps you understand the individual settings. When you're done, restart Splunk Enterprise.