Database configuration

Database creation workflow

Databases are automatically created using the following workflow:

1. The installer script checks if the sql.create_db parameter is set to true in the cluster_config.json file.
2. If true, the installer automatically generates the service database on your behalf.

3. If you did not define a connection string for your service, the installer uses the connection template to generate all database connection strings.

4. If you did define connection string for your service, the installer uses it for your database.

If you want to bring your own database, set the sql.create_db parameter to true in the cluster_config.json file, then proceed to manually creating it. Make sure to respect the naming convention for the database, as we use this name in conjunction with the connection template to form the database connection string. If you do not follow the recommended naming convention, you must provide the SQL connection strings yourself.

Automatically create the necessary databases

If you want the installer to create the databases, fill in the following fields:

Parameter	Description
sql.create_db	Set to true.
sql.server_url	FQDN of the SQL server, where you want the installer to configure database.
sql.port	Port number on which a database instance should be hosted in the SQL server.
sql.username	Username / user ID to connect to the SQL server.
sql.password	Password of the username provided earlier to connect to the SQL server.

Note:

Ensure the user has the dbcreator role. This grants them permission to create the database in SQL Server. Otherwise the installation fails.

ODBC connection does not support usernames that contain special characters. For database usernames for AI Center, Document Understanding, and Apps, use only uppercase and lowercase letters.

Bring your own database

If you choose to bring your own databases for a new Automation Suite installation, we strongly recommend setting up new databases rather than using existing ones. This precaution is necessary to prevent any conflicts with the operation of Automation Suite that might occur due to leftover metadata from old databases.

If you bring your own database, you must provide the SQL connection strings for every database. Automation Suite supports the following SQL connection string formats:

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, Document Understanding
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 1
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.	Process Mining

¹ To install AI Center on a FIPS 140-2-enabled machine, you must append the following to sql_connection_string_template_jdbc: encrypt=true;trustServerCertificate=false;fips=true;.

Important:

Make sure the SQL account specified in the connection strings is granted the db_securityadmin and db_owner roles for all Automation Suite databases. If security restrictions do not allow the use of db_owner, then the SQL account should have the following roles and permissions on all databases:

• db_securityadmin

• db_ddladmin
• db_datawriter
• db_datareader
• EXECUTE permission on dbo schema

Important:

If you manually set the connection strings in the configuration file, you can escape SQL, JDBC, ODBC, or PYODBC passwords as follows:

• for SQL: 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 }.
• for PYODBC: username and password should be url encoded to account for special characters. Document Understanding database passwords cannot start with {.

Important: The AutomationSuite_ProcessMining_Airflow database for Process Mining product must have READ_COMMITTED_SNAPSHOT enabled.

Note:

By default, TrustServerCertificate is set to False, and you must 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. If you do not provide the SQL Server certificate in this scenario, the prerequisite check will fail.

See Certificate configuration on this page for more details.

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=[e-mail];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://testuser%40sfdev3082457-sql.database.windows.net:[e-mail]:1433/DB_NAME_PLACEHOLDER?driver=ODBC+Driver+17+for+SQL+Server"

sql_connection_string_template and sql_connection_string_template_sqlalchemy_pyodbc example (Process Mining)

"sql_connection_string_template": "Server=tcp:sfdev4515230-sql.database.windows.net,1433;Initial Catalog=DB_NAME_PLACEHOLDER;Persist Security Info=False;User Id=[e-mail];Password='07<l[xj-=~:z`Ds&nl';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Max Pool Size=100;"

"sql_connection_string_template_sqlalchemy_pyodbc": "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"

Default and optional DB names for Automation Suite services

{
  "orchestrator": "AutomationSuite_Orchestrator",
  "orchestrator_ta": "AutomationSuite_Orchestrator",
  "asrobots": "AutomationSuite_Orchestrator",
  "orchestrator_upd": "AutomationSuite_Platform",
  "platform": "AutomationSuite_Platform",
  "test_manager": "AutomationSuite_Test_Manager",
  "automation_ops": "AutomationSuite_Platform",
  "automation_hub": "AutomationSuite_Automation_Hub",
  "insights": "AutomationSuite_Insights",
  "task_mining": "AutomationSuite_Task_Mining",
  "dataservice": "AutomationSuite_DataService", 
  "aicenter": "AutomationSuite_AICenter",
  "documentunderstanding": "AutomationSuite_DU_Datamanager",
  "processmining_airflow": "AutomationSuite_Airflow",
  "processmining_metadata": "AutomationSuite_ProcessMining_Metadata",
  "processmining_warehouse": "AutomationSuite_ProcessMining_Warehouse",
  "apps": "AutomationSuite_Apps",
  
}

Note:

If you you want to override the connection string for any of the services above, set the sql_connection_str for that specific service.

You still have to manually create these databases before running the installer.

Overriding the default connection string for Orchestrator and the platform

{
  "orchestrator": {
    "sql_connection_str": "Server=tcp:sfdev1804627-c83f074b-sql.database.windows.net,1433;Initial Catalog=CustomOrchDB;Persist Security Info=False;User Id=[e-mail];Password=***;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Max Pool Size=100;"
  },
  "platform": {
    "sql_connection_str": "Server=tcp:sfdev1804627-c83f074b-sql.database.windows.net,1433;Initial Catalog=CustomIDDB;Persist Security Info=False;User Id=[e-mail];Password=***;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Max Pool Size=100;"
  }
}

To override the database connection strings for other products, set the sql_connection_str in the corresponding product blocks. The connection string should have a format supported by the respective product.

Example for setting database connection string for AI Center

Parameter	Description
aicenter.sql_connection_str	AI Center JDBC connection string (Refer below for the JDBC format)

Note: Make sure the JDBC connection string has the format in the following sample:

"aicenter": {
    "enabled": true,
    "sql_connection_str": "jdbc:sqlserver://sfdev1804627-c83f074b-sql.database.windows.net;database=aicenter;user=[e-mail];password=TFgID_9GsE7_P@srCQp0WemXX_euHQZJ"
}

Sample Document Understanding connection string

"documentUnderstanding": {
  "datamanager": {
    "sql_connection_str": "SERVER=sql-server.database.windows.net;DATABASE=datamanager;DRIVER={ODBC Driver 17 for SQL Server};UID=testadmin;PWD=myPassword"
  },
  "sql_connection_str": "Server=tcp:database.example.com,1433;Initial Catalog=db;Persist Security Info=False;User Id=[e-mail];Password=***;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Max Pool Size=100;",
  "cjkOcr": 
  {
    "enabled": true
  }
}

Note: The data manager SQL connection string is optional only if you want to overwrite the default with your own.

Handwriting is always enabled for online installation.

The default value for max_cpu_per-pod is 2, but you can adjust it according to your needs.

For more information, check the Document Understanding configuration file.

Sample Process Mining connection string

"processmining": {
    "enabled": true,
    "warehouse": {
        "sql_connection_str": "Server=tcp:sfdev4515230-sql.database.windows.net,1433;Initial Catalog=AutomationSuite_ProcessMining_Warehouse;User Id=[e-mail];Password='07<l[xj-=~:z`Ds&nl';Persist Security Info=False;Integrated Security=true;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Max Pool Size=100;",
	"sqlalchemy_pyodbc_sql_connection_str": "mssql+pyodbc://testadmin%40sfdev4515230-sql.database.windows.net:07%3Cl%5Bxj-%3D~[e-mail]:1433/AutomationSuite_ProcessMining_Warehouse?driver=ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=YES&Encrypt=YES&Trusted_Connection=yes",
        "master_sql_connection_str": "Server=tcp:sfdev4515230-sql.database.windows.net,1433;Initial Catalog=master;Persist Security Info=False;Integrated Security=true;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Max Pool Size=100;"
    },
    "sqlalchemy_pyodbc_sql_connection_str": "mssql+pyodbc://testadmin%40sfdev4515230-sql.database.windows.net:07%3Cl%5Bxj-%3D~[e-mail]:1433/AutomationSuite_Airflow?driver=ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=YES&Encrypt=YES"
    "sql_connection_str": "Server=tcp:sfdev4515230-sql.database.windows.net,1433;Initial Catalog=AutomationSuite_ProcessMining_Metadata;User Id=[e-mail];Password='07<l[xj-=~:z`Ds&nl';Persist Security Info=False;Integrated Security=true;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Max Pool Size=100;",  
},

Important: that the names for template PYODBC connection string sql_connection_string_template_sqlalchemy_pyodbc and the PYODBC connection string sqlalchemy_pyodbc_sql_connection_str used when you bring your own database are different. Also connection string names are different for the template SQL sql_connection_string_template and sql_connection_str used when you bring your own database.

Important:

If you bring your own database and you configured this using the sql_connection_str and sqlalchemy_pyodbc_sql_connection_str connection strings in the processmining section of the cluster_config.jsonfile, the template connection strings sql_connection_string_template and sql_connection_string_template_sqlalchemy_pyodbc are ignored if specified.

Attention:

MultiSubnetFailover=True is not supported. Make sure to remove MultiSubnetFailover=True from any of the Process Mining connection strings.

Note: Depending on the app_security_mode setting either a new SQL user is created for every Process Mining app by the system (app_security_mode="system_managed"), or a single SQL user account is created that is used for all process apps (app_security_mode="single_account"). Note that app_security_mode="system_managed" is the default setting, and that this requires advanced permissions for the database user.

See Configuring process app security.

Sample Process Mining connection string

• Scenario: setup with Kerberos authentication.

"processmining": {
    "enabled": true,
    "warehouse": {
        "sql_connection_str": "Server=tcp:assql2019.autosuitead.local,1433;Initial Catalog=AutomationSuite_ProcessMining_Warehouse;Persist Security Info=False;Integrated Security=true;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Max Pool Size=100;",
        "sqlalchemy_pyodbc_sql_connection_str": "mssql+pyodbc://:@assql2019.autosuitead.local:1433/AutomationSuite_ProcessMining_Warehouse?driver=ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=YES&Encrypt=YES&Trusted_Connection=yes",
        "master_sql_connection_str": "Server=tcp:assql2019.autosuitead.local,1433;Initial Catalog=master;Persist Security Info=False;Integrated Security=true;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Max Pool Size=100;"
      },
    "sqlalchemy_pyodbc_sql_connection_str": "mssql+pyodbc://:@assql2019.autosuitead.local:1433/AutomationSuite_Airflow?driver=ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=YES&Encrypt=YES&Trusted_Connection=yes"
  },

Sample Process Mining connection string

• Scenario: Metadata database and data warehouse use separate SQL server (Non-Kerberos authentication).

"processmining": {
    "enabled": true,
    "warehouse": {
      "sql_connection_str": "Server=tcp:uipath-integration1.database.windows.net,1433;Initial Catalog=AutomationSuite_ProcessMining_Warehouse;Persist Security Info=False;User Id=userid;Password='password';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Max Pool Size=100;",
      "sqlalchemy_pyodbc_sql_connection_str": "mssql+pyodbc://userid:[e-mail]:1433/AutomationSuite_ProcessMining_Warehouse?driver=ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=YES&Encrypt=YES",
      "master_sql_connection_str": "Server=tcp:uipath-integration1.database.windows.net,1433;Initial Catalog=master;Persist Security Info=False;User Id=userid;Password='password';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Max Pool Size=100;"
    },
    "sqlalchemy_pyodbc_sql_connection_str": "mssql+pyodbc://userid:[e-mail]:1433/AutomationSuite_Airflow?driver=ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=YES&Encrypt=YES"
    "sql_connection_str": "Server=tcp:uipath-integration2.database.windows.net,1433;Initial Catalog=AutomationSuite_Airflow;Persist Security Info=False;User Id=userid;Password='password';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Max Pool Size=100;",  
},

Sample Process Mining connection string

• Scenario: using custom app_security_mode.

"processmining": {
    "enabled": true,
    "app_security_mode": "system_managed",
    "warehouse": {
        "sql_connection_str": "Server=tcp:assql2019.autosuitead.local,1433;Initial Catalog=AutomationSuite_ProcessMining_Warehouse;Persist Security Info=False;Integrated Security=true;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Max Pool Size=100;",
        "sqlalchemy_pyodbc_sql_connection_str": "mssql+pyodbc://:@assql2019.autosuitead.local:1433/AutomationSuite_ProcessMining_Warehouse?driver=ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=YES&Encrypt=YES&Trusted_Connection=yes",
        "master_sql_connection_str": "Server=tcp:assql2019.autosuitead.local,1433;Initial Catalog=master;Persist Security Info=False;Integrated Security=true;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Max Pool Size=100;"
      },
    "sqlalchemy_pyodbc_sql_connection_str": "mssql+pyodbc://:@assql2019.autosuitead.local:1433/AutomationSuite_Airflow?driver=ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=YES&Encrypt=YES&Trusted_Connection=YES"
  },