Updating the SQL connection strings

There are multiple scenarios where you may want to update the connection string used by Automation Suite products to connect to the SQL database, such as the following:

• When periodically rotating the password used to connect to the database, for security and compliance;
• When changing the FQDN for the SQL server;
• When migrating the database to another SQL server for maintenance purposes;
• When adding, modifying, or removing one or more connection attributes, such as MultiSubnetFailover;
• When switching from basic authentication to integrated authentication using Kerberos and AD.
  Important:

  Products in Automation Suite do not create tables or schema at the time of updating the SQL database connection string. Make sure your new connection string refers to the same database that you currently use.

  To avoid downtime during the update process, make sure that your current connection string is valid at the time of the update process. You can revoke your old connection string after the update.

Updating the connection strings for installed products

To update the connection string for installed products in Automation Suite, connect to any of the server nodes and perform the following operations.

1. If you use the Account lockout threshold policy setting, enable maintenance mode.
2. Generate the cluster_config.json file.
3. Provide the new connection strings for the installed products in the cluster_config.json.
4. Run the service installer.
5. If you use the Account lockout threshold policy setting, disable maintenance mode.

Generating the cluster_config.json file

Generate the latest cluster_config.json file using the following command:

./bin/uipathctl manifest get-revision > ./cluster_config.json

Note: For details on how to configure the cluster_config.json parameters, see Advanced installation experience.

Providing the new connection strings for installed products

There are two ways to provide the connection strings for products running in Automation Suite:

• A: Provide a connection string template that will be common to all the products running in Automation Suite. This approach will assume the default database names for all the products.

• B: Provide connection strings specific to each product.

Important:

Make sure you escape NET, JDBC, or ODBC passwords as follows:

• for NET: add ' at the beginning and end of the password, and double any other '.
• for JDBC/ODBC: add { at the beginning of the password and } at the end, and double any other }. Do not double {.

If you set TrustServerCertificate=False, then you may have to provide an additional CA certificate for the SQL Server. This is required if the SQL Server certificate is self-signed or signed by an internal CA. For details, see Updating the CA certificates.

A: Providing a common connection string for all products

All the products running in Automation Suite refer to a common template connection string. One use case for choosing this method would be when you want to change the password for all the products at once. Note that, in this case, the password will be the same for all the products.

In this scenarios, the database names for all products must be the default ones, as required by Automation Suite. If the database names you configured do not meet the Automation Suite requirements, follow the next step.

Tip:

Check the list of the databases and their default names:

• Single-node evaluation: Configuring Microsoft SQL server

• Multi-node HA-ready production: Configuring Microsoft SQL Server

The following table explains which template format the product services accept:

Parameter	Description	Products
sql_connection_string_template	Full ADO.NET connection string where Catalog name is set to DB_NAME_PLACEHOLDER. The installer will replace this placeholder with the default database names for the installed suite services.	Platform, Orchestrator, Automation Suite Robots, Test Manager, Automation Hub, Automation Ops, Insights, Task Mining, Data Service, Process Mining
sql_connection_string_template_jdbc	Full JDBC connection string where database name is set to DB_NAME_PLACEHOLDER. The installer will replace this placeholder with the default database names for the installed suite services.	AI Center
sql_connection_string_template_odbc	Full ODBC connection string where database name is set to DB_NAME_PLACEHOLDER. The installer will replace this placeholder with the default database names for the installed suite services.	Document Understanding, Apps
sql_connection_string_template_sqlalchemy_pyodbc	Full SQL alchemy PYODBC connection string where database name is set to DB_NAME_PLACEHOLDER. The installer will replace this placeholder with the default database names for the installed suite services.	Document Understanding, Process Mining

sql_connection_string_template example

Server=tcp:sfdev1804627-c83f074b-sql.database.windows.net:1433;Initial Catalog=DB_NAME_PLACEHOLDER;Persist Security Info=False;User Id=testadmin;Password=***;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Max Pool Size=100;

sql_connection_string_template_jdbc example

jdbc:sqlserver://sfdev1804627-c83f074b-sql.database.windows.net:1433;database=DB_NAME_PLACEHOLDER;user=testadmin;password=***;encrypt=true;trustServerCertificate=false;Connection Timeout=30;hostNameInCertificate=sfdev1804627-c83f074b-sql.database.windows.net"

sql_connection_string_template_odbc example

SERVER=sfdev1804627-c83f074b-sql.database.windows.net,1433;DATABASE=DB_NAME_PLACEHOLDER;DRIVER={ODBC Driver 17 for SQL Server};UID=testadmin;PWD=***;MultipleActiveResultSets=False;Encrypt=YES;TrustServerCertificate=NO;Connection Timeout=30;"

sql_connection_string_template_sqlalchemy_pyodbc example

"mssql+pyodbc://testadmin%40sfdev4515230-sql.database.windows.net:07%3Cl%5Bxj-%3D~[e-mail]:1433/DB_NAME_PLACEHOLDER?driver=ODBC+Driver+17+for+SQL+Server"

Note: Update the cluster_config.json with the new connection string template you want to update.

B: Providing connection strings specific to each product

Platform

The Platform service provides administrative capabilities such as organization and tenant management, licensing management, user management, etc. The Platform service is enabled by default and cannot be removed. Its default database name is AutomationSuite_Platform.

To provide a connection string for the Platform service, add or update the following section in the cluster_config.json file:

"platform": {
  "sql_connection_str": "***" // dotnet connection string 
}

Orchestrator

The default database name for Orchestrator is AutomationSuite_Orchestrator.

To provide a connection string for the Orchestrator service, add or update the following section in the cluster_config.json file:

"orchestrator": {
  "sql_connection_str": "***" // dotnet connection string
}

Automation Suite Robots

Automation Suite Robots and Orchestrator share the same database by default. The database name is AutomationSuite_Orchestrator.

To provide a connection string for Automation Suite Robots, add or update the following section in the cluster_config.json file:

"asrobots": {
  "sql_connection_str": "***" // dotnet connection string
}

Automation Hub

The default database name for Automation Hub is AutomationSuite_Automation_Hub.

To provide a connection string for the Automation Suite service, add or update the following section in the cluster_config.json file:

"automation_hub": {
  "sql_connection_str": "***" // dotnet connection string
}

Automation Ops

The default database name for Automation Ops is AutomationSuite_Platform.

To provide a connection string for the Automation Ops service, add or update the following section in the cluster_config.json file:

"automation_ops": {
  "sql_connection_str": "***" // dotnet connection string
}

AI Center

The default database name for AI Center is AutomationSuite_AICenter.

To provide a connection string for the AI Center service, add or update the following section in the cluster_config.json file:

"aicenter": {
  "sql_connection_str": "***" // jdbc connection string
}

Important:

To enable FIPS 140-2 on the machine on which AI Center is installed, you must append the following to sql_connection_string_template_jdbc:

encrypt=true;trustServerCertificate=false;fips=true;

Apps

The default database name for Apps is AutomationSuite_Apps.

To provide a connection string for the Apps service, add or update the following section in the cluster_config.json file:

"apps": {
  "sql_connection_str": "***" // odbc connection string
}

Data Service

The default database name for Data Service is AutomationSuite_DataService.

To provide a connection string for the Data Service service, add or update the following section in the cluster_config.json file:

"dataservice": {
  "sql_connection_str": "***" // dotnet connection string
}

Document Understanding

The default database name for Document Understanding is AutomationSuite_DU_Datamanager.

To provide a connection string for the Document Understanding service, add or update the following section in the cluster_config.json file:

"documentunderstanding": { 
"enabled": true,
"sql_connection_str": "***" // dotnet connection string,
"datamanager": {
 "sql_connection_str": "***" // odbc connection string
 "pyodbc_sql_connection_str": "***" // python sql connection string
 }
}

Insights

The default database name for Insights is AutomationSuite_Insights.

To provide a connection string for the Insights service, add or update the following section in the cluster_config.json file:

"insights": {
  "sql_connection_str": "***" // dotnet connection string
}

Process Mining

The default database name for Process Mining is AutomationSuite_ProcessMining_Metadata.

To provide a connection string for the Process Mining service, add or update the following section in the cluster_config.json file:

"process_mining": {
  "enabled": true,
  "sql_connection_str": "***" // dotnet connection string
  "sqlalchemy_pyodbc_sql_connection_str": "", 
  "warehouse": {
     "sql_connection_str": "",
     "master_sql_connection_str": "",
     "sqlalchemy_pyodbc_sql_connection_str": ""
  }
}

Task Mining

The default database name for Task Mining is AutomationSuite_Task_Mining.

To provide a connection string for the Task Mining service, add or update the following section in the cluster_config.json file:

"task_mining": {
  "sql_connection_str": "***" // dotnet connection string
}

Test Manager

The default database name for Test Manager is AutomationSuite_Test_Manager .

To provide a connection string for the Test Manager service, add or update the following section in the cluster_config.json file:

"test_manager": {
  "sql_connection_str": "***" // dotnet connection string
}

Running the service installer

To run the service installer, use the following command:

cd /opt/UiPathAutomationSuite/{version}/installer

./install-uipath.sh -i ./cluster_config.json -s -o output.json --accept-license-agreement