10.1.6

Edit

Database

MS SQL server

  • MS SQL version:

    • Recommended: MS SQL Server 2016 SP2 (The RTM version is 13.0.5698.0).
    • Supported: IA Cloud Developer for non-production environments, Standard Edition, IA Cloud Enterprise.

  • 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 feature: DB Engine.

  • 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 regular Transaction log backup must be enabled for the "workfusion" database operating in the Always-On mode. For the detailed guide, see Use the Maintenance Plan Wizard.

    If you don't plan to use Always-On, the recovery mode must be set to SIMPLE.

  • 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

  • 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

  • The mixed authentication mode must be enabled.

    Mixed auth

  • Only local users can be used.

Starting from version 10.1.6.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> (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. For the convenience of product operation, it is advised to clear the Enforce password policy checkbox. Otherwise, when passwords expire, it will be required to perform update-passwords procedure.

    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 the 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

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

    1. In the Login name field, specify the corresponding username.

      Starting from product version 10.1.6.1, you may also select Windows Authentication to create logins based on Active Directory accounts.

    2. For the convenience of product operation, it is advised to clear the Enforce password policy checkbox. Otherwise, when passwords expire, it is required to perform update-passwords procedure.

    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. When configuring the setup, on the APP 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 →