10.2.3

Edit

Database

MS SQL server

  • MS SQL version:

    • Recommended: Microsoft SQL Server 2019 (The RTM version is 15.0.2000.5)
    • Also supported: MS SQL Server 2016 SP2 (The RTM version is 13.0.5698.0)

  • The activated instance of MS SQL server must be provided.

  • If a named SQL instance is used, it must listen for connections on a statically-assigned TCP port.

  • Required MS SQL Server features: DB Engine.

  • SQL Server and SQL Server Agent services must be up and running in the Automatic start mode.

  • The default server-level collation must be SQL_Latin1_General_CP1_CI_AS. See Collation support.

  • Database-level collation must be set.

  • The separate database with the name "workfusion" must be created.

  • The following MS SQL logins must be created:

    ParameterDefault loginDescription
    mssql_dba_user'wf_dba'The name of the database owner, which provides permissions for the database to other users and schemas
    mssql_ct_user'workfusion'The name of the MS SQL Login and the corresponding MSSQL DB user for the Control Tower component
    mssql_ws_user'workspace'The name of the MS SQL Login and the corresponding MSSQL DB user for the Workspace component
    mssql_sqc_user'sqc'The name of the MS SQL Login and the corresponding MSSQL DB user for the SQC component
    mssql_ds_user'ds'The name of the MS SQL Login and the corresponding MSSQL DB user for the CT datasource connection
    mssql_rpa_user'rpa'The name of the MS SQL Login and the corresponding MSSQL DB user for the RPA component
    mssql_pm_user'pm'The name of the MS SQL Login and the corresponding MSSQL DB user for the monitoring tools
    mssql_dm_user:'dm'The name of the MS SQL Login and the corresponding MSSQL DB user for the Analytics component
    mssql_rapi_user'rapi'The name of the MS SQL Login and the corresponding MSSQL DB user for the Analytics remote api
    mssql_ocr_user'ocr'The name of the MS SQL Login and the corresponding MSSQL DB user for the OCR component
    mssql_automl_mms_user'automl_mms'The name of the MS SQL Login and the corresponding MSSQL DB user for the AutoML Model Management component
    mssql_keycloak_user'keycloak'The name of the MS SQL Login and the corresponding MSSQL DB user for the Keycloak component

  • The following password policies are applied for the MS SQL logins:

    • For passwords mssql_dm_pass and mssql_rapi_pass:
      • Permitted symbols: #*@/`+-=_$([]:,.;!?
      • Forbidden symbols: {}'<>"|^%&)
    • For all other DB passwords:
      • Permitted symbols: %^#*@/|`+-=_<>&$(){}[]:,.;!?
      • Forbidden symbols: "'

  • In the workfusion database, the user must be created for the <mssql_dba_user> MSSQL login with the same name. This user must be granted the db_owner role in the database.

  • Execute the following SQL script to apply the necessary database settings and enable snapshot isolation:

    ALTER DATABASE  *mssql_db_name* SET single_user with rollback immediate;
GO

ALTER DATABASE *mssql_db_name* SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE *mssql_db_name* SET READ_COMMITTED_SNAPSHOT ON
GO

ALTER DATABASE *mssql_db_name* set multi_user;
GO
ONLY FOR HIGH-AVAILABILITY INSTALLATION
  • The database must be created and configured under the AlwaysOn Active-Active cluster with synchronous replication between primary MSSQL servers.
  • The database must be configured with a static AG listener.
  • When deploying MSSQL on the Disaster Recovery site, asynchronous replication must be set up between primary and secondary replicas.

Authentication

Starting from version 10.2.1, the Product supports Active Directory logins on the MSSQL server. You no longer need to use mixed authentication mode and local users.

When using AD logins in MS SQL, make sure to meet the following requirements:

  • The MS SQL server must be joined to the domain.
  • Server Principal Name (SPN) for the MS SQL server must exist within the Active Directory schema.
  • SPN for the MS SQL server must correspond to the value provided in hosts.yml for the mssql_hostname option.
  • SPN for the MS SQL server must be registered for the account used to run the MS SQL service process.
  • All servers within the environment must have network access to the Domain Controllers' ports).
  • The username format must be: 'DOMAIN\username'

How to prepare MS SQL server

To prepare an MS SQL server:

  1. Create the new database, and specify the database name: workfusion. For other options in this section, you may use the default values.

    Create DB

  2. Create the <mssql_dba_user> owner (wf_dba) with the full-access permissions for the workfusion database. This user must be later specified in config.yml.

    Note: the following password policies are applied for the MS SQL logins:

    • For passwords mssql_dm_pass and mssql_rapi_pass:
      • Permitted symbols: #*@/`+-=_$([]:,.;!?
      • Forbidden symbols: {}'<>"|^%&)
    • For all other DB passwords:
      • Permitted symbols: %^#*@/|`+-=_<>&$(){}[]:,.;!?
      • Forbidden symbols: "'

    To create <mssql_dba_user>:

    1. On an MS SQL server, create the mssql login, specify the login name.

    2. Make sure to clear the Enforce password policy checkbox.

    3. In the Default database box, specify the <mssql_db_name> database and then choose English as the Default language.

    4. In the Select a page group, click User mapping, and then select the <mssql_dba_user> checkbox to map the user to the <mssql_db_name> database. Leave all other options unchanged.

      Create DB User

    5. In the database Users, select the db_owner checkbox to assign <mssql_dba_user> to the db_owner role.

      Assign to group

  3. Create the following MS SQL logins for the Workfusion components. Note that MS SQL logins are not the same as database users:

    Parameter in config.ymlDefault valueDescription
    mssql_ct_user'workfusion'The name of the MS SQL Login and the corresponding MSSQL DB user for the Control Tower component
    mssql_ws_user'workspace'The name of the MS SQL Login and the corresponding MSSQL DB user for the Workspace component
    mssql_sqc_user'sqc'The name of the MS SQL Login and the corresponding MSSQL DB user for the SQC component
    mssql_ds_user'ds'The name of the MS SQL Login and the corresponding MSSQL DB user for the CT datasource connection
    mssql_rpa_user'rpa'The name of the MS SQL Login and the corresponding MSSQL DB user for the RPA component
    mssql_pm_user'pm'The name of the MS SQL Login and the corresponding MSSQL DB user for the monitoring tools
    mssql_dm_user:'dm'The name of the MS SQL Login and the corresponding MSSQL DB user for the Analytics component
    mssql_rapi_user'rapi'The name of the MS SQL Login and the corresponding MSSQL DB user for the Analytics remote api
    mssql_ocr_user'ocr'The name of the MS SQL Login and the corresponding MSSQL DB user for the OCR component
    mssql_automl_mms_user'automl_mms'The name the MS SQL Login and of the corresponding MSSQL DB user for the AutoML Model Management component
    mssql_keycloak_user'keycloak'The name the MS SQL Login and of the corresponding MSSQL DB user for the Keycloak component

  4. For each login, specify the following parameters of the SQL Server authentication:

    1. In the Login name field, specify the corresponding username.
    2. Make sure to clear the Enforce password policy checkbox. See the passwords policies earlier.
    3. In the Default database box, specify the <mssql_db_name> database.
    4. Choose English as the Default language.

    SQL auth

  5. Execute the following SQL script to apply the necessary database settings and enable snapshot isolation:

    
ALTER DATABASE  *mssql_db_name* SET single_user with rollback immediate;
GO

ALTER DATABASE *mssql_db_name* SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE *mssql_db_name* SET READ_COMMITTED_SNAPSHOT ON
GO

ALTER DATABASE *mssql_db_name* set multi_user;
GO

  6. In the msdb database, create <mssql_pm_user> user(make sure to replace <mssql_pm_user> with the actual username:

    USE msdb;
IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = '<mssql_pm_user>')
CREATE user [<mssql_pm_user>] for login [<mssql_pm_user>] with default_schema = dbo;
GO
ALTER role SQLAgentUserRole add member [<mssql_pm_user>];
GO

  7. Provide <mssql_pm_user> with permissions to view server state:

    use master;
GRANT view server state to [<mssql_pm_user>];

  8. When configuring the setup, on the Master server, in the config.yml file, in the #MSSQL section, remember to specify the corresponding usernames and passwords that you have created on the previous step.

← PortsRPA server →