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, click the Configuration > Databases > Connections tab.
2. Click 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: Choose the identity you want to use with this connection.
• Connection Type: A list of supported databases. Choose 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. Choosing a database from this list without installing the corresponding driver will not work. 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, specify the source database time zone for the conversion. When the time zone is set, you have two time zone conversion options depending on the localTimezoneConversionEnabled setting in db_connections.conf:
  • When localTimezoneConversionEnabled is undefined or set to false, the system will only convert index time values read from table columns in the source database into the local time zone. This conversion option only applies to inputs.
  • When localTimezoneConversionEnabled is set to true, the system will convert 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 (e.g., TIMESTAMP WITH TIMEZONE). This conversion option applies to inputs, dbxqueries, and dblookups.
  If you leave the Timezone field blank, the system will assume that the source database time zone and Splunk server's local time zone are the same and will read and index 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:

  • Note that if the column in your database contains timezone information (e.g. column with a TIMESTAMP WITH TIMEZONE type), the timezone you set here will be ignored.
  • The timezone setting defaults to JVM time zone. If there are no JVM time zone settings, the time zone of your operating system will be used.
  • 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 checkbox 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 Enable SSL checkbox is selected Certificate field appears for the MySQL, MSSQL and Oracle connection types. It allows to pass a certificate which will be automatically added to the Java TrustStore and used for the server authentication. If the Enable SSL checkbox will be selected, but the certificate field would be empty - DB Connect will ignore it and work 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 will populate, using placeholder values, with the correct URL format according to the database type you have chosen.
• Connection properties Provided properties are used during the creation of the connection. Properties can be provided either here or through the editing of the JDBC URL. A list of all valid driver connection properties can be found in the official database documentation. Incorrect properties will be ignored.
• You can manually edit the URL format by selecting Edit JDBC URL. When you select this field, the JDBC URL Preview will become editable. This is useful if you need to add customized JDBC URL parameters to the JDBC URL, but is not recommended 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 specify 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 checkbox 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 will do its best to ensure that no changes are made, 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 is limited to 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. Click Save to save the connection.

   Note:If the connection you create is valid, you can save the connection succesfully. Otherwise the error message will prompt up, you need 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 is defined by the permissions that have been 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 clicking Disable/Enable here. You cannot disable a connection if any inputs, outputs, or lookups are using it. In that case, this button is greyed out.
• 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, this button is greyed out.

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 specify the Splunk Enterprise roles that have read, read-write, or no access to the connection.

• Read access means that Splunk Enterprise roles will be able to use the connection.
• Write access means that Splunk Enterprise roles will be able to 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, it will override its connection settings. Consult your JDBC driver vendor's documentation for exact parameter syntax. The db_connection_types.conf.spec file will also help you understand the individual settings. When you're done, restart Splunk Enterprise.