Configuring CloudBees Flow to Use an Alternate Database

If you deselected the “database” check box during installation, you cannot log into CloudBees Flow until you set up a database configuration pointing to an external database. a CloudBees Flow enterprise license is required; for details, see External Database Configuration . You can change the database configuration through the CloudBees Flow web interface or the CloudBees Flow command-line tool.

To change the database configuration in a CloudBees Flow cluster, you must reset CloudBees Flow to single-server mode beforehand. For details, see Running a Cluster in Single-Server Mode.

Setting the Database with the Web Interface

Use these instructions to use the CloudBees Flow web UI to set the external database.

Open the Database configuration page using one of the following methods:
- From the Deploy UI: browse to https://<cloudbees-flow-server>/flow/, and then from the main menu, click Administration > Configurations > Database configuration.
- From the Automation Platform UI: browse to https://<cloudbees-flow-server>/commander/, and then from the main menu, click Administration > Database configuration.

Complete the menus and fields in the Database configuration screen as follows.

Menu or Field

Description

Database type

Database server type. Choose Built-in (MariaDB), MySQL, Oracle, or Microsoft SQL Server.

The Built-in (MariaDB) option is supported only for the built-in database that is installed by CloudBees Flow. Any other MariaDB database is not supported; that is, you cannot install another MariaDB instance and use it with CloudBees Flow. Also, changing the database configuration options (such as the database name, host name, and credentials) to use any other database such as MySQL when using the Built-in (MariaDB) option is not supported.

Database name

Database instance name. The default name is commander.

Host name

Host name of your database server. You can enter either the host name or the IP address of the server.

Port

Port on which the database server is listening. The default is 3306.

Username

Username that the CloudBees Flow server will use to access the database on the database server.

Password

Password for the database user that you specified.

Retype password

Confirm the password for the database user that you specified.

Click Save and restart server.
When asked to confirm your changes and restart the CloudBees Flow server, click OK.

Setting the Database from a Command Line

This section contains topics related to setting an alternate database for CloudBees Flow through a command line.

SQL Server Authentication

SQL Server supports two types of user authentication:

SQL Server Authentication
Windows Authentication

You must find out from your DBA which authentication type is required for the CloudBees Flow user because, the authentication type influences how information is provided in the ectool command setDatabaseConfiguration. See setDatabaseConfiguration Command Examples for example command syntax.

Setting the Database with ectool

You use the ectool setDatabaseConfiguration command to change the database configuration from the command line.

Determine if your database is SQL Server. The type of user authentication used by the database impacts the syntax of the setDatabaseConfiguration command that you use in this procedure.

(CloudBees Flow 5.0 and later) Set the database configuration based on the type of user authentication used by the database:

For SQL server authentication or a SQL Server with NTLM login, enter

setDatabaseConfiguration ←-options>

where ←-options> are the options that you specify based on the type of user authentication.

For a SQL server with the SSO login on Windows, see Setting the Database as a SQL Server with SSO Login on Windows .

After you change the CloudBees Flow database configuration, the server attempts to connect to the database to do the initial schema setup.

Do not restart the CloudBees Flow server at this time. (You should restart the server only if it was already connected to a built-in or external database and the ectool setDatabaseConfiguration command has been used to connect the server to an entirely new database.)

Enter the following command and wait for the output:
```
ectool --server localhost --timeout 900 getServerStatus --block 1 --serverStateOnly 1▼
```
This command runs for 900 seconds (15 minutes), or until Commander finishes creating all the schema objects, or until getServerStatus displays either bootstrap or running.

If the output says bootstrap, enter the command again until it says running.

The commander.log file shows commanderServer is running as in the following snippet:

2016-02-10T19:19:06.582 | 10.0.2.206 | INFO | bootstrap |  |  | ServerStatus | commanderServer is running▼

Failing to wait and restarting Commander while it is creating schema objects will cause it to fail to start (during the next manual start) with an error:

2016-09-07T15:08:55.825 | DEBUG | bootstrap |  |  | upgradeData | OperationInvoker | Exception: InvalidSchema: Unable to validate the database schema: could not extract ResultSet 2016-09-07T15:08:55.831 | ERROR | bootstrap  |  |  |  | BootstrapCommanderServerImpl | Unable to validate the database schema: could not extract ResultSetcom.electriccloud.errors.EcException: Unable to validate the database schema: could not extract ResultSetat com.electriccloud.errors.EcException.create(EcException.java:165)at com.electriccloud.errors.EcExceptionBuilder.build(EcExceptionBuilder.java:34)at com.electriccloud.upgrade.UpgradeManagerImpl.doDataMaintenance(UpgradeManagerImpl.java:672)at com.electriccloud.upgrade.UpgradeDataOperation.perform(UpgradeDataOperation.java:50)at com.electriccloud.upgrade.UpgradeDataOperation.perform(UpgradeDataOperation.java:26)▼

Use getServerStatus to look for problems logging into the database or creating the schema. This command shows log prompts from the server bootstrap process.

Before the server is completely up, getServerStatus does not require a login session, but after the server is up, it does. Thus, if you enter the ` getServerStatus` call and get a “session expired” error, the server is up.

setDatabaseConfiguration Command Examples

Following are examples of how to use the setDatabaseConfiguration command.

SQL Server Authentication:

ectool setDatabaseConfiguration --databaseType sqlserver
--databaseName commander --hostName localhost --port 1433
--userName commander --password commander▼

The --userName and --password options must be included in the setDatabaseConfiguration command.

SQL Server with NTLM login:

ectool setDatabaseConfiguration --databaseType sqlserver --databaseName commander --hostName localhost --port 1433 --userName commander@example.com --password commander

The user name must include the domain name. For example, user@example.com or domain\user.

Setting the Database as a SQL Server with SSO Login on Windows

Download the JDBC driver from Microsoft.

Select the appropriate JDBC driver version from the Microsoft JDBC driver download page. See the information in the "Details" and "System Requirements" sections to help select the correct driver.

For example, if you download the sqljdbc_4.0.2206.100_enu.tar.gz file and unzip it, you get this file:

sqljdbc_4.0.2206.100_enu.tar.gz\sqljdbc_4.0.2206.100_enu.tar\sqljdbc_4.0\enu\auth\x64\sqljdbc_auth.dll
Check whether you already have the JDBC driver file, such as sqljdbc4.jar , in the C:\Program Files\Electric Cloud\ElectricCommander\server\wars\commander-server.war\WEB-INF\lib\ directory.

This file ships with CloudBees Flow and should be version 4.0 or later.
Copy the sqljdbc_auth.dll file from Step 1 to the C:/Program Files/ElectricCloud/ElectricCommander/server/lib directory, which is the same folder set as the java.library.path property.
Enter the following command to update the COMMANDER_CUSTOM_DB_URL and COMMANDER_DB_URL properties in the C:\ProgramData\Electric Cloud\ElectricCommander\conf\database.properties file with the JDBC URL specified in the customDatabaseUrl argument:
```
ectool setDatabaseConfiguration --databaseType sqlserver --databaseName commander --hostName localhost --port 1433 --customDatabaseUrl "jdbc:sqlserver://localhost:1433;integratedSecurity=true;databaseName=commander;applicationName=CloudBees Flow Automation Platform;selectMethod=cursor"▼
```
CloudBees Flow uses COMMANDER_DB_URL for information only. If COMMANDER_CUSTOM_DB_URL is set, this value is used instead of COMMANDER_DB_URL.
Make sure that the CloudBees Flow Automation Platform Server service is set to run as a user who can log into the SQL Server database using Windows authentication.

If you needed to set the CloudBees Flow Automation Platform Server service to run as a user who can log into the SQL Server database using Windows authentication, restart the CloudBees Flow server. Otherwise, check the commander.log file to verify that the CloudBees Flow server connects to the SQL Server database with SSO login.

The CloudBees Flow server connects to the SQL Server with SSO login. If this is successful, the following lines appear in the commander.log file:

USERDNSDOMAIN=ELECTRIC‐CLOUD.COM
USERDOMAIN=ELECTRIC‐CLOUD
USERNAME=<user who can log into the SQL Server database using Windows authentication>
USERPROFILE=C:\Users\<user who can log into the SQL Server database using Windows authentication>
..
java.library.path=C:/Program Files/ElectricCloud/ElectricCommander/server/lib
...
2015‐07‐07T18:08:13.255 | INFO | bootstrap | | | 2015‐07‐07T18:08:13.319 | WARN | bootstrap | | |
...
2015‐07‐07T18:08:14.479 | INFO | bootstrap | | | 2015‐07‐07T18:08:14.479 | INFO | bootstrap | | |▼

setDatabaseConfiguration Command Options

The following options are available for use with the ectool command setDatabaseConfiguration. The option command syntax is:

setDatabaseConfiguration

[--databaseType <mysql|sqlserver|Oracle|builtin>]
[--databaseName <database name>]
[--hostName <host name>]
[--ignorePasskeyMismatch <Boolean flag>]
[--ignoreServerMismatch <Boolean flag>]
[--password <password>]
[--port <port number>]
[--preserveSessions <Boolean flag>]
[--userName <user name>]
[--customDatabaseDialect <custom database dialect>]
[--customDatabaseDriver <custom database driver>]
[--customDatabaseUrl <custom database URL>]▼

The following table describes the command options:

Option Description

databaseType

Selects the database type—supported options are mysql|sqlserver|Oracle|builtin

The builtin option is supported only for the built-in MariaDB database that is installed by CloudBees Flow. Any other MariaDB database is not supported; that is, you cannot install another MariaDB instance and use it with CloudBees Flow. Also, changing the database configuration options (such as the database name, host name, and credentials) to use any other database such as MySQL when using the builtin option is not supported.

databaseName

The name of your alternate database—this is not the host name, but the name the DBA gave the database object.

hostName

The host name where your database is running

ignorePasskeyMismatch

< Boolean flag - 0|1|true|false > - If the server is started with a different passkey, ignore the mismatch if “true”.

This action discards all saved passwords.

ignoreServerMismatch

< Boolean flag - 0|1|true|false > - If the server is started on a different host than where the server previously started, ignore the mismatch if “true”.

port

The port number used by the database Server—if omitted, port 1433 is used

preserveSessions

< Boolean flag - 0|1|true|false > - If ignoring a server mismatch, default behavior invalidates all sessions. Setting this flag to “true” saves all sessions, allowing the server to reconnect to running jobs. This option is used in combination with ignoreServerMismatch.

userName

The user name to use when connecting to the database

password

The password to use to connect to the database

customDatabaseDialect

Internal option—use only at the request of CloudBees support

customDatabaseDriver

Internal option—use only at the request of CloudBees support

customDatabaseUrl

Internal option—use only at the request of CloudBees support

Enabling Full Database URL Control

Go to the database.properties file.
Add the explicit connect string to that property.