Set up the databases

Set up the databases that IBM RPA needs to store data generated during its usage. These settings must be made before you install the product so you can properly define the connection between the databases and the product.

This procedure provides the steps that you must follow to create a user, databases, and connection strings, enable SQL Server authentication and TCP protocols, open the port 1433, and optionally, set the SQL Server Browser to start automatically and enable remote connections.

Before you begin

This procedure must be completed by a Database Administrator (DBA). It also assumes that you met all the hardware, system, and network requirements that are described in Prerequisites to install the server.

IBM RPA supports only Microsoft™ SQL Server™. Install SQL Server🡥 and SQL Server Management Studio (SSMS)🡥 before you start this procedure.

Procedure

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

  1. Enable SQL Server authentication
  2. Create a new user
  3. Create the databases
  4. Create the connection strings
  5. Enable TCP protocols and open the port 1433
  6. Optional: Set the SQL Server Browser to start automatically
  7. Optional: Enable remote connections

Enable SQL Server authentication

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

Create a new 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 installing the server:

  1. Open the SQL Server Management Studio.
  2. Expand the Security folder and right-click Logins > New Login to create a new user.
  3. In the Login name field, enter the username.
  4. Select SQL Server authentication.
  5. In the Password and Confirm password, enter the user password.
  6. Disable the User must change password at next login and Enforce password expiration options.
  7. From the Server Roles menu, select the bulkadmin option.
  8. On User Mappings, select the databases that the user has access to, and select the following permissions:
    • db_datareader
    • db_datawriter
    • db_ddladmin

Create the databases

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

  • ADDRESS
  • AUTOMATION
  • KNOWLEDGE
  • WORDNET
  • AUDIT
  • UMS (Only if you want the SSO authentication method)
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.
  • You only need to create the UMS database if you choose SSO as the authentication method. Read more about SSO and its advantages in Single Sign On.
  • For the UMS database you can use an SQL Server instance that is on another computer and provide its hostname during the IBM RPA server installation. Remember to set up the UMS database on the proper database instance.
  • For the UMS database, the database user must be a bulkadmin. You can use the same user that creates the other databases, if this user is a system administrator as well. After you install the server, you can rollback the database user permissions.
  • For the UMS database you can use an SQL Server instance that is on another computer and provide its hostname during the IBM RPA server installation. Remember to set up the UMS database on the proper database instance.
  • For the UMS database, the database user must be a bulkadmin. You can use the same user that creates the other databases, if this user is a system administrator as well. After you install the server, you can rollback the database user permissions.
  • The database collation must be case-insensitive. For example, on the database settings, set the collation property to SQL_Latin1_General_CP1_CI_AS. You can either change the collation on each database or change the collation at server level.

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

Without UMS

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

With UMS

sqlcmd -S localhost\SQLEXPRESS -Q "create database [automation]; create database [knowledge]; create database [wordnet]; create database [address]; create database [ums]; 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 in the IBM RPA server installer. Create one connection string for each database. Remember to test them before you use them in the installer.

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

Server=<SERVER_ADDRESS>;Database=<DATABASE_NAME>;User Id=<USERNAME>;Password=<PASSWORD>;

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 have 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:

Server=<SERVER_ADDRESS>;Database=<DATABASE_NAME>;User Id=<USERNAME>;Password=<PASSWORD>; 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

  1. Open SQL Server Configuration Manager.
  2. Expand the SQL Server Network Configuration.
  3. Click the protocols option, then double-click TCP/IP.
  4. In the TCP/IP Properties window, click the Protocol tab.
  5. Select Yes from the Enabled list.
  6. Click the IP Addresses tab and expand IPAll:
    • Enter 1433 in the TCP Dynamic Ports field.
    • Clear the TCP Port field.
  7. 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.

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

Optional: Enable remote connections

Follow this step if you have created the UMS database.

  1. Open SQL Server Management Studio.
  2. Right-click the server instance in the Object Explorer, and click Properties.
  3. Click the Connections menu.
  4. Select the Allow remote connections to this server checkbox.
  5. Restart SQL Server.