Securing database connections

Set up security for a database connection, whether it is required by the database provider or optional.

Before you begin

If you are using a JDBC provider, you must first set up the JDBC provider. See Set up your JDBC provider definition.
If you are using an ODBC connection, you must first connect to the database. See Enabling ODBC connections to the databases.

About this task

Some databases require that all access is associated with a known user ID, for others this association is optional. For example, DB2® requires a data source login name and password on all connections.

Use the mqsicredentials command to specify a user ID and password that the integration node can use to access each database. Alternatively, you can use the mqsisetdbparms command.If a user ID and password have not been specified for database access, default values for user ID and password are used which are platform specific:

On Windows: The integration node service ID and password that you specified on the mqsicreatebroker command.
On Linux® and UNIX: The user ID mqsiUser and password ******** (these values are fixed).

Steps for setting up security are specific to the type of database connection that you are using. Choose your database connection type to see the steps:

ODBC connections
JDBC connections

ODBC connections

About this task

If your ODBC data source requires you to define secure access, or if you want to implement security where this is optional, complete the following steps:

Procedure

Identify the user IDs that you want to associate with the database connection, or create a user ID with a password, following the appropriate instructions for your operating system and database.
Define the user IDs and passwords that the integration node can use to access a particular data source.
Create user IDs and passwords that can be used to access the data source from an integration node or integration server.
- Run the mqsicredentials command, using the following format:
```
mqsicredentials broker_name --integration-server dbserver1 --create --credential-type odbc --credential-name data_source_name --username database_userID --password database_userID_password
```
  If the integration server is stopped, you must also specify the vault key by including the --vault-key myVaultKey option, or by using a vaultrc file; for further details, see mqsicredentials command.
  
  Run the command for each integration server in which you require credentials to be created. If you require credentials to be created in all integration servers being managed by the integration node, you can use the --all-integration-servers option on the command.
- Alternatively, you can run the mqsisetdbparms command to create user IDs and passwords that can be used to access the data source from an integration node. Use the following format:
```
mqsisetdbparms broker_name -n data_source_name -u database_userID -p database_userID_password
```
The mqsicredentials command stores the identities in separate vaults for each integration server. The mqsisetdbparms command creates the identity at the level of the integration node level and it is subsequently used by all integration servers that are managed by the integration node.
Optional: Specify whether you want to use same user ID and password for more than one database as a default set of credentials:
- Run the mqsicredentials command to identify a specific credential as the default. Having created a credential in step 3, you can run the command with the following format:
```
mqsicredentials broker_name --integration-server dbserver1 --set-as-default --credential-type odbc --credential-name data_source_name
```
  The Defaults section of the server.conf.yaml configuration file is updated accordingly:
```
Defaults:
  Credentials:
    odbc: 'data_source_name'
```
- Alternatively, you can run the mqsisetdbparms command specifying dsn::DSN in the -n parameter, as shown in the following example:
```
mqsisetdbparms broker_name -n dsn::DSN -u default_userID -p default_password
```

Results

You have secured access to your ODBC data source.

JDBC connections

About this task

If your database requires you to define secure access, or if you want to implement security where this is optional, complete the following steps:

Procedure

Identify the user ID that you want to associate with the database connection, or create a user ID with a password, following the appropriate instructions for your operating system and database.
Define the user IDs and passwords that the integration node can use for the JDBC connections:
- Run the mqsicredentials command, using the following format:
```
mqsicredentials broker_name --integration-server server_name --create --credential-type jdbc --credential-name security_identity --username userID --password password
```
  For example, if you want a user ID myuserid with a password of secretpw to access a database on integration node INODE1, run the mqsicredentials command, as follows:
```
mqsicredentials INODE1 --integration-server ISERVER1 --create --credential-type jdbc --credential-name mySecurityIdentity --usernme myuserid --password secretpw
```
  If the integration server is stopped, you must also specify the vault key by including the --vault-key myVaultKey option, or by using a vaultrc file; for further details, see mqsicredentials command.
  
  Run the command for each integration server in which you require credentials to be created. If you require credentials to be created in all integration servers being managed by the integration node, you can use the --all-integration-servers option on the command.
  
  Alternatively, you can run the mqsisetdbparms command, using the following format:
```
mqsisetdbparms broker_name -n jdbc::security_identity -u userID -p password
```
  For example:
```
mqsisetdbparms INODE1 -n jdbc::mySecurityIdentity -u myuserid -p secretpw
```
  The jdbc:: prefix indicates that the security_identity is to be used for JDBC connections.
- Run the mqsicredentials command to identify a specific credential as the default:
```
mqsicredentials broker_name --integration-server dbserver1 --set-as-default --credential-type jdbc --credential-name mySecurityIdentity
```
  Alternatively, you can run the mqsisetdbparms command specifying jdbc::JDBC in the -n parameter, as shown in the following example:
```
mqsisetdbparms INODE1 -n jdbc::JDBC -u default_userID -p default default_password
```
- Update the corresponding Security identity property for the JDBC Providers policy to associate the connection with the security identity or credential that you have defined. For more information, see JDBC Providers policy (JDBCProviders).

Optional: Set up JVM keystore and truststore configuration if needed.

Many JDBC drivers will use the JVM TLS settings when connecting securely, relying on system properties such as javax.net.ssl.keyStore, javax.net.ssl.trustStore, and javax.net.ssl.keyStorePassword to locate certificates as needed. These settings can be configured by updating the relevant properties in the JVM resource manager section in the server.conf.yaml file:


ResourceManagers:
  JVM:
    ....
    keystoreType: 'P12'                          # JVM key store type (sets javax.net.ssl.keyStoreType)
    keystoreFile: '/path/to/keystore.p12'        # JVM location of the key store (sets javax.net.ssl.keyStore)
    keystorePass: 'brokerKeystore::password'     # Credential alias containing the key store password (sets javax.net.ssl.keyStorePassword)
    truststoreType: 'JKS'                        # JVM trust store type (sets javax.net.ssl.trustStoreType)
    truststoreFile: '/path/to/truststore.jks'    # JVM location of the trust store (sets javax.net.ssl.trustStore)
    truststorePass: 'brokerTruststore::password' # Credential alias containing the trust store password (sets javax.net.ssl.trustStorePassword)

You can avoid storing passwords in plain text if you use credential aliases for passwords, by updating the server.conf.yaml file instead of setting the Java system properties directly or configuring passwords in a JDBC Providers policy.

Results

You have secured access to your JDBC databases. If you need to define user credentials that can be shared across a business area or account, you can reuse the same security identity that you defined in the previous steps in different JDBC Providers policies.