Database requirements

Set up the databases that IBM RPA needs to store data generated during its usage. IBM RPA on Red Hat® OpenShift® Container Platform accepts connection with Microsoft™ SQL Server™ installed off-cluster.

This procedure provides the steps that you must follow to enable SQL Server™ authentication and create a user, databases, and connection strings. These settings must be made before you install the product so you can properly define the connection between the databases and the product.

Before you begin

This procedure must be completed by a Database Administrator (DBA). It also assumes that you met all the hardware and system requirements, and have the resources that are described in Pre-installation requirements.

Important:SQL Server® services must not be set to start with the Automatic (Delayed Start) startup type on Windows™.

Procedure

Complete the following steps to prepare the databases that IBM RPA uses:

Enable SQL Server authentication
Create a user
Create the databases
Create the connection strings
Enable TCP protocols and open the port 1433
Optional: Set the SQL Server Browser to start automatically
Optional: Enable remote connections

Enable SQL Server authentication

Open the SQL Server Management Studio.
In the Object Explorer, right-click the server instance and click Properties.
In the Server Properties window, click Security.
In the Server authentication area, click SQL Server and Windows Authentication mode.

Create a user

Create the user that IBM RPA server uses to connect to SQL Server® and manage the databases. This user must be supplied in the connections strings that are referenced when you create the database secret:

Open the SQL Server Management Studio.
Expand the Security folder and right-click Logins > New Login to create a new user.
In the Login name field, enter the username.
Select SQL Server authentication.
In the Password and Confirm password, enter the user password.
Disable the User must change password at next login and Enforce password expiration options.
From the Server Roles menu, select the bulkadmin option.

Create the databases

In the SQL Server Management Studio, use the new bulkadmin user to create the following databases:

ADDRESS
AUTOMATION
KNOWLEDGE
WORDNET
AUDIT

Important:

You must create these databases manually. The IBM RPA installer does not create them automatically.
Do not change the server database schema. If an update is released in which the names of the objects in the database conflict with the changes made by the customer, the update fails with errors.
If you host databases for multiple IBM RPA instances on the same database server, they must have the same dashboard password. You must create a secret that contains the password for each IBM RPA instance that shares the same database server. For more information, see Configuring custom resources.

The following code snippet shows an example of how to create a database with the SQL Server's sqlcmd:

sqlcmd -S localhost\SQLEXPRESS -Q "create database [automation]; create database [knowledge]; create database [wordnet]; create database [address]; create database [audit]"

For more information about how to create a database in SQL Server® by using SQL Server Management Studio or Transact-SQL, see Create a Database 🡥.

Create the connection strings

You must provide connection strings to the databases when you create the database secret. Create one connection string for each database. Remember to test them before you use them in the secret.

The following example shows how you can format the connection strings:

Data Source=<SERVER_ADDRESS>,1433;Initial Catalog=<DATABASE_NAME>;User ID=<USERNAME>;Password=<PASSWORD>;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False

Where:

<SERVER_ADDRESS>
The address to your SQL Server instance. In some cases, the address is composed by your machine's HOSTNAME and the SQL Server instance name, such as: HOSTNAME\SQLSERVER.
<DATABASE_NAME>
The name of the database, such as ADDRESS, AUTOMATION, KNOWLEDGE, WORDNET, or AUDIT.
<USERNAME>
The username of the user that you created at the Create a new user step. If you assigned a new user as the database owner, ensure that the user was either created with User must change password at next login disabled or that the user who is logged in to Microsoft® SQL Server® to update their password before you start the installation.
<PASSWORD>
The password of the user that you created at the Create a new user step.

Add the "MultipleActiveResultSets=True" keyword pair in the connection string for the WORDNET database. The following example shows how you can format the connection string:

Data Source=<SERVER_ADDRESS>,1433;Initial Catalog=wordnet;User ID=<USERNAME>;Password=<PASSWORD>;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;MultipleActiveResultSets=True

Important:Do not use Trusted_Connection=True in the connection string. It overrides SQL Server Authentication and your SQL Server® user might not get access to create the databases.

Enable TCP protocols and open the port 1433

Open SQL Server Configuration Manager.
Expand the SQL Server Network Configuration.
Click the protocols option, then double-click TCP/IP.
In the TCP/IP Properties window, click the Protocol tab.
Select Yes from the Enabled list.
Click the IP Addresses tab and expand IPAll:
- Enter 1433 in the TCP Dynamic Ports field.
- Clear the TCP Port field.
Optional: Run the following query to figure out ports enabled for TCP/IP.
```
SELECT * FROM [sys].[dm_tcp_listener_states]
```

Optional: Set the SQL Server Browser to start automatically

Follow this step if you have created the UMS database.

In the SQL Server Configuration Manager, click SQL Server Services.
Double-click the SQL Server Browser service.
Set the Start Mode to Automatic.

Optional: Enable remote connections