How to configure SQL connection strings when you bring your own databases

Help Automation Suite
,
1

When you install Automation Suite 2023.4 in your own environment, you can either let the installer create SQL databases for you or, alternatively, you can bring your own databases.

In case the installer creates databases for you, the default database names for each product are as follows:

product database name
platform AutomationSuite_Platform
orchestrator AutomationSuite_Orchestrator
orchestrator_ta AutomationSuite_Orchestrator
orchestrator_upd AutomationSuite_Platform
asrobots AutomationSuite_Orchestrator
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

Now, let’s assume you install the following 4 products:

platform
orchestrator
aicenter
documentunderstanding

and you bring your own databases with the following names:

platform_db
orchestrator_db
aicenter_db
documentunderstanding_db

In order to override the default settings, you must provide the SQL connection strings for every database in cluster_config.json before you proceed to the next steps. Please note that each product requires different format as described in the table in this page.

image
image2348×836 137 KB

For platform, orchestrator and documentunderstanding, you must provide SQL connection strings in the following format:

Server=tcp:<fqdn_of_sql_server>,<port>;Initial Catalog=<database_name>;Persist Security Info=False;User Id=<user_id>;Password=<password>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Max Pool Size=100

For aicenter, you must provide JDBC connection string in the following format:

jdbc:sqlserver://<fqdn_of_sql_server>:<port>;database=<database_name>;user=<user_id>;password=<password>;encrypt=true;trustServerCertificate=true;Connection Timeout=30;hostNameInCertificate=<fqdn_of_sql_server>

For documentunderstanding.datamanager, you must provide ODBC connection string in the following format:

SERVER=<fqdn_of_sql_server>,<port>;DATABASE=<database_name>;DRIVER={ODBC Driver 17 for SQL Server};UID=<user_id>;PWD=<password>;MultipleActiveResultSets=False;Encrypt=YES;TrustServerCertificate=YES;Connection Timeout=30

So, for your setup, cluster_config.json should define the following connections strings. We assume that the user id is admin, the password is password, the FQDN of SQL Server is sql.mydomain.com and the port is 1433.

"platform": {
  ...
  "sql_connection_str": "Server=tcp:sql.mydomain.com,1433;Initial Catalog=platform_db;Persist Security Info=False;User Id=admin;Password='password';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Max Pool Size=100"
  ...
}

"orchestrator": {
  ...
  "sql_connection_str": "Server=tcp:sql.mydomain.com,1433;Initial Catalog=orchestrator_db;Persist Security Info=False;User Id=admin;Password='password';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Max Pool Size=100"
  ...
}

"aicenter": {
  ...
  "sql_connection_str": "jdbc:sqlserver://sql.mydomain.com:1433;database=aicenter_db;user=admin;password={password};encrypt=true;trustServerCertificate=true;Connection Timeout=30;hostNameInCertificate=sql.mydomain.com"
  ...
}

"documentunderstanding": {
  ...
  "sql_connection_str": "Server=tcp:sql.mydomain.com,1433;Initial Catalog=documentunderstanding_db;Persist Security Info=False;User Id=admin;Password='password';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Max Pool Size=100"
  "datamanager": {
    "sql_connection_str": "SERVER=sql.mydomain.com,1433;DATABASE=documentunderstanding_db;DRIVER={ODBC Driver 17 for SQL Server};UID=admin;PWD={password};MultipleActiveResultSets=False;Encrypt=YES;TrustServerCertificate=YES;Connection Timeout=30"
  }
  ...
}

IMPORTANT:

Make sure the SQL account specified in the connection strings is granted with 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_ddladmin
  • db_datawriter
  • db_datareader
  • EXECUTE permission on dbo schema

You must escape 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.
1 Like