It is a good security practice to regularly change database access credentials. Whenever the database user password is changed, it is necessary to update connection properties on several places. The article below provides guidelines for:

• PostgreSQL server
• MS SQL Server with SQL authentication
• MS SQL Server with domain authentication and service account

How to Encrypt Password

Passwords in Configuration Files

Passwords in the configuration files can be in plain text or encrypted by the utility provided by Dispatcher Paragon as a widget on Dashboard:

1. Sign into Management Service interface as an administrator (into a tenant scheme if it is in a multi-tenant environment).
2. Stay on Dashboard.
3. Find the Text Encryption widget or enable it (click the Add widget button).
4. Enter the text to encrypt.
5. Click the Encode button at the bottom of the widget.
6. Copy the text to the clipboard or transcribe it and replace the original password in the property file.

Passwords in Database

• Passwords in the database are in plaintext.

Dispatcher Paragon Management Service

Dispatcher Paragon Management Service - SQL Authentication

Use this procedure to change password for:

• PostgreSQL server

• MS SQL Server with SQL authentication

  For MS SQL Server with domain authentication skip this section. Continue with Dispatcher Paragon Management Service - Domain Authentication instead.
  

STEP 1 - CONFIGURATION FILES

When using SQL authentication (not domain authentication), update the following configuration files:

• <install_dir>\Management\ims\application.properties:
  • spring.datasource.password – password of a user account for IMS database, typically a database with suffix _IMS. This is the account provided by the customer.
• <install_dir>\Management\conf\safeq.properties:
  
  • database.global.management.password – password for a common connection to the database. This is the account provided by the customer.

  • databaseWarehouse.global.management.password – password for a common connection to the database. This is the account provided by the customer.

    Internal accounts

    Those accounts were created automatically during installation by Dispatcher Paragon installer. Those are SQL logins, not domain accounts.

    • database.cluster.management.password – password of a cluster management user, typically called cluster_mngmt).
    • database.cluster.guest.password – password of a cluster guest user, typically called cluster_guest).

    • databaseWarehouse.cluster.management.password – password of data warehouse guest user, typically called cluster_guest).

    • databaseWarehouse.cluster.guest.password – password of data warehouse guest user, typically called cluster_guest).

• <install_dir>\Management\validator\conf\DBValidator.properties:
  
  • connectionInfoSQ.userPassword – password for a common connection to the database. This is the account provided by the customer.
  • connectionInfoDW.userPassword – password for a common connection to the database. This is the account provided by the customer.

STEP 2 - DATABASE

Execute the following query to reset stored procedures' connection strings:

• For PostgreSQL:

  PostgreSQL

  SELECT cluster_mngmt.spu_recover_tenant_db_passwords();

• For MS SQL Server:

  MS SQL Server

  EXEC cluster_mngmt.spu_recover_tenant_db_passwords;

Execute database validator:

1. Execute the following query

   1. For PostgreSQL:

      PostgreSQL

      SELECT cluster_mngmt.spu_clean_validator_tables();

   2. For MS SQL Server:

      MS SQL Server

      EXEC cluster_mngmt.spu_clean_validator_tables();
2. Navigate to <install_dir>\Management\validator\conf\DBValidator.properties and verify the database passwords.
3. Navigate to <install_dir>\Management\validator\bin\validatorRunner.exe
   1. Run it.

Dispatcher Paragon Management Service - Domain Authentication

Use this procedure to change password for:

• MS SQL Server with domain authentication. The installation followed this procedure: Installing Dispatcher Paragon Management Server on external MSSQL using domain users
  

STEP 1 - CONFIGURATION FILES

When using DOMAIN authentication (not sql login authentication), update the following configuration file:

• <install_dir>\Management\validator\conf\DBValidator.properties:
  
  • connectionInfoSQ.userPassword – password for a common connection to the database. This is the account provided by the customer.
  • connectionInfoDW.userPassword – password for a common connection to the database. This is the account provided by the customer.

STEP 2 - DATABASE

Execute database validator:

STEP 3 - WINDOWS SERVICES

Update service account password on affected Windows services (run services.msc):

• Dispatcher Paragon Infrastructure Service
• Dispatcher Paragon LDAP Replicator
• Dispatcher Paragon Management Service

Dispatcher Paragon Management Service - etcd (not needed from Build 67 and above) 

1. Open PowerShell window and navigate to directory: <install_dir>\Management\etcd

2. Run this command to dump etcd content

   rm etcddump.ps1 -ea silentlycontinue; .\etcdctl.exe ls / | %{" .\etcdctl.exe --endpoint http://127.0.0.1:2379 update $($_ -replace `"/`", `"`") `"$(.\etcdctl.exe get $_)`"" | out-file etcddump.ps1 -append }
3. Open newly created file etcddump.ps1 for editing
4. Adjust password value on those lines:

   1. This is the account provided by the customer:

      .\etcdctl.exe --endpoint http://127.0.0.1:2379 update encryptedUserPassword "code,104,119,-55,-123,-120,-2,-11,-38,44,-42,70,123,-64,-125,30,69"

      If you have SafeQ DataWarehouse database deployed on a different server (MSMD deployment), adjust also this line:

      .\etcdctl.exe --endpoint http://127.0.0.1:2379 update DWencryptedUserPassword "code,104,119,-55,-123,-120,-2,-11,-38,44,-42,70,123,-64,-125,30,69"

   2. Internal accounts:

      Internal acounts

      Those accounts were created automatically during installation by Dispatcher Paragon installer. Those are SQL logins, not domain accounts.

      .\etcdctl.exe --endpoint http://127.0.0.1:2379 update encryptedClusterPassword "code,-57,-15,18,97,115,-62,79,17,-93,-27,25,13,61,124,37,34"

      .\etcdctl.exe --endpoint http://127.0.0.1:2379 update encryptedClusterGuestPassword "code,-68,22,-35,-10,33,-82,24,42,-64,81,-56,112,54,83,121,76"

      .\etcdctl.exe --endpoint http://127.0.0.1:2379 update encryptedTenantPassword "code,-105,-107,-24,-63,-8,29,43,-21,14,64,68,91,-106,60,-59,94"

      If you have SafeQ DataWarehouse database deployed on a different server (MSMD deployment), the following lines are also present:

      .\etcdctl.exe --endpoint http://127.0.0.1:2379 update DWencryptedClusterPassword "code,-57,-15,18,97,115,-62,79,17,-93,-27,25,13,61,124,37,34"

      .\etcdctl.exe --endpoint http://127.0.0.1:2379 update DWencryptedClusterGuestPassword "code,-68,22,-35,-10,33,-82,24,42,-64,81,-56,112,54,83,121,76"

5. Run this command to restore etcd content:

   powershell.exe -executionpolicy bypass .\etcddump.ps1

6. Review the content using this PowerShell command:

   .\etcdctl.exe ls / | %{write-host "$($_): $(.\etcdctl.exe get $_)" }

Dispatcher Paragon Management Service - Apply the Change

The following services need to be restarted to apply the changes:

• Dispatcher Paragon Management Service
• Dispatcher Paragon Infrastructure Service

Dispatcher Paragon Payment System

Dispatcher Paragon Payment System - SQL Authentication

Use this procedure to change password for:

• PostgreSQL server

• MS SQL Server with SQL authentication

  For MS SQL Server with domain authentication skip this section.

The passwords must be changed in configuration files when the user password of a connection to the database is changed:

• <install_dir>\YPS\ps-conf\environment-configuration.properties

database.password – the password of a user for a common connection to the database (typically, a default user with the username "postgres" or "sa")

Dispatcher Paragon Payment System - Domain Authentication

No extra step required.

Dispatcher Paragon Payment System - Apply the Change

The following services need to be restarted to apply the changes:

• Dispatcher Paragon Payment System Service