Configuring CloudBees CD/RO to Use an Alternate Database

6 minute readReference

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

To change the database configuration in a CloudBees CD/RO cluster, you must reset CloudBees CD/RO 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 CD/RO web UI to set the external database.

  1. 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.

  2. 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 CD/RO. Any other MariaDB database is not supported; that is, you cannot install another MariaDB instance and use it with CloudBees CD/RO. 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 CD/RO 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.

  3. Click Save and restart server.

  4. When asked to confirm your changes and restart the CloudBees CD/RO server, click OK.

Setting the Database from a Command Line

This section contains topics related to setting an alternate database for CloudBees CD/RO 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 CD/RO 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.

  1. 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.

  2. (CloudBees CD/RO 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 CD/RO database configuration, the server attempts to connect to the database to do the initial schema setup.

      Do not restart the CloudBees CD/RO 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.)
  3. 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.

  4. 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)
  5. 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

  1. 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

  2. Check whether you already have the JDBC driver file, such as sqljdbc4.jar , in the C:\Program Files\CloudBees\Software Delivery Automation\server\wars\commander-server.war\WEB-INF\lib\ directory.

    This file ships with CloudBees CD/RO and should be version 4.0 or later.

  3. Copy the sqljdbc_auth.dll file from Step 1 to the C:/Program Files/cloudbees/sda/server/lib directory, which is the same folder set as the java.library.path property.

  4. Enter the following command to update the COMMANDER_CUSTOM_DB_URL and COMMANDER_DB_URL properties in the C:\ProgramData\CloudBees\Software Delivery Automation\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={PRODUCT} Automation Platform;selectMethod=cursor"
    CloudBees CD/RO uses COMMANDER_DB_URL for information only. If COMMANDER_CUSTOM_DB_URL is set, this value is used instead of COMMANDER_DB_URL.
  5. Make sure that the CloudBees CD/RO Automation Platform Server service is set to run as a user who can log into the SQL Server database using Windows authentication.

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

    The CloudBees CD/RO 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/cloudbees/sda/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 CD/RO. Any other MariaDB database is not supported; that is, you cannot install another MariaDB instance and use it with CloudBees CD/RO. 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

  1. Go to the database.properties file.

  2. Add the explicit connect string to that property.