This is documentation for Apprenda 7 and 8.
Documentation for newer version is available at https://new.docs.apprenda.com.

SQL Account Credential Rotation

The Apprenda Cloud Platform provides mechanisms for Platform Operators, System Administrators, and Database Administrators to change SQL Server account credentials used for accessing and managing Platform Databases. When configuring your Platform, you have the option to run the Platform SQL Server Administrator Account and each database account as the same SQL Server account, unique accounts, or a combination unique accounts and shared users. There are separate steps for credential rotation depending on the role of each account and database/server it needs to access.

Before rotating any credentials, it is recommended to have backups of your databases and the old credentials available. In the event that the new credentials were not rotated correctly, due to a network outage or other disruptive event, you may need to reapply old the credentials in SQL Server or apply the database backup.

Platform SQL Server Admin Account

The Platform SQL Server Admin account(s) is used on each Platform SQL Server instance for storage management and updating mappings between server. You can manage and rotate the credentials for this account directly in the Infrastructure>Servers>Database Nodes page of the SOC.

To change a nodes credentials, click on Edit Database Node Information on the node to update. You may change either the username and password, or just the password.

To change the username and password,

  1. create the user/credentials in SQL Server
  2. update the credentials in the SOC

Changing both the username and password will not cause any downtime for your Platform or applications.

To change only the password, you can update the password in SQL Server and the SOC in any order.

Note that rotating only the password only will cause downtime for that server if there's an active deploy/undeploy on the server occurring at the same time (which requires the Admin credentials).

Auditing, Billing, Load Management, Logging, Scheduling, and Utilization Database Accounts

For username and password (or password only) rotation of Platform DB accounts you can update the new credential(s) directly in the Platform Registry. You also have the option of using sample scripts to automate rotation for any or all Database accounts (see the next section).

Updating the username and password or only the password for these databases in the registry settings will not cause any downtime.

To change the username and password,

  1. create the user/credentials in SQL Server
  2. update the credentials in the Platform Registry
  3. update the owner of the database(s) in SQL Server

To change only the password,

  1. create the user/credentials in SQL Server
  2. update the credentials in the Platform Registry

Note that rotating credentials for the Auditing Database will cause the audit log generated from the change to be delayed. The event will be logged after the credentials are updated.

Platform Registry Settings for Credential Rotation

  • Auditing.DatabaseConnectionString
  • Auditing.DatabasePassword
  • Billing.DatabaseConnectionString
  • Billing.DatabasePassword
  • LoadManagement.DatabaseConnectionString
  • LoadManagement.DatabasePassword
  • Logging.DatabaseConnectionString
  • Logging.DatabasePassword
  • Scheduling.DatabaseConnectionString
  • Scheduling.DatabasePassword
  • Utilization.DatabaseConnectionString
  • Utilization.DatabasePassword

Platform Core Database (SaaSGrid Core and Portals) Account

Credential rotation of the Core DB Account, requires a lot of updates to various places in the Platform. Contact your support representative for sample scripts that will change credentials in all locations where they are stored on Platform. Its highly recommend that you incorporate these scripts into your automated configuration management systems to help manage rotation during routine or incident handling security operations. Note that you are able to build your own or customize the provided scripts to rotate credentials.

It is also possible to automate all steps of credential rotation for other database accounts using these sample scripts, with the exception of the SQL Server Admin Account.

The information below describes how to rotate credentials using the Apprenda sample scripts:

  • Update-ACPSqlCredential.ps1
  • Update-ACPSqlPassword.ps1

Username and Password Rotation

Updating both the username and password for the Core DB account (and other DB accounts) can be accomplished by running the Update-ACPSqlCredential.ps1 script. Before running the script, you should create the user in SQL Server or you can pass the "-createNewUser" flag in the script.

To change credentials using Update-ACPSqlCredential.ps1, you’ll need the following:

  • SOC Credentials
  • SQL Server Instance name
  • SQL Server Admin Account credentials
  • Windows Administrator Account credentials
  • Linux Administrator Account credentials (if necessary)

Run the following command with your Platform's information to rotate credentials for the Core DB:

& Update-ACPSqlCredential.ps1 -platformUriHostname "apps.apprenda.cloud" -socCreds “ops@domain.com” -dbServer “DATABASE-SERVER1” -managementSqlCreds “sql-sa” -newDatabaseCreds “db-user” -winAdminCreds “win-sa” -linuxAdminCreds “lin-sa” -core 

To update other database credentials with this script, include any of the following flags:

-auditing -billing -loadMgt -logging -scheduling -utilization

Once you update both username and password for the Core DB account (and other DB accounts)  using the script, both the old and new credentials will be valid for the period of time while the Platform is updating the changes in all locations. There will be no Platform or application downtime while these changes are being made. After a period of time (recommend 10 minutes), you can remove the old credentials from SQL Server.

Password Only Rotation

If you choose to update only the password for the Core DB account, your Platform will experience some downtime for a short, few minute period. You can accomplish this change by running the Update-ACPSqlPassword.ps1 script first, then make the change in SQL Server.

To change the password only, you’ll need access to the following:

  • SOC Credentials
  • Windows Administrator Account credentials
  • Linux Administrator Account credentials (if necessary)

Run the following command with your Platform's information to rotate credentials for the Core DB:

& Update-ACPSqlPassword.ps1 -platformUriHostname "apps.apprenda.cloud" -socCreds “ops@domain.com” -securePassword -winAdminCreds “win-sa” -linuxAdminCreds “lin-sa” -core

The "-securePassword" value is the new password for the Core DB account.

To update other database credentials with this script, include any of the following flags:

-auditing -billing -loadMgt -logging -scheduling -utilization

The Platform will take a few minutes to update and start using the new password, and won’t fully start authenticating the Core DB user until you make the change directly in SQL Server (after running the script).

Rotation Script Logging

When running Update-ACPSqlCredential.ps1 and Update-ACPSqlPassword.ps1 logs are written to a log file and to the console.

Log File

The default logs location for is %appdata%/Apprenda/Logs/sqlRotation.log.

You are able to set a custom location before running the rotation script by loading the Apprenda SqlRotation.psm1 module and updating the location.

  1. load Apprenda-SqlRotation.psm1
Import-Module "Apprenda-SqlRotation.psm1"
  1. set a new log path using Set-RotationLogPath. Calling Set-RotationLogPath with no parameters will reset the log path back to the default path.
Set-RotationLogPath "custom/log/path.log"
  1. check that the path was updated using Get-RotationLogPath
Get-RotationLogPath

If you have set a custom log location, do not load the Apprenda-SqlRotation.psm1 module when running the rotation script, or the default location will be used instead of the new location you configured. Use the "-doNotLoadModule" option to run the script without reloading Apprenda-SqlRotation.psm1.

Console Logs

In addition to everything being written to the log file, logs are written to the error, warning, verbose, and debug streams as the script runs. Its recommended to set the following global preferences options in Powershell for handling errors and log messages as the rotation script runs.

$DebugPreference        = "continue"
$ErrorActionPreference  = "stop"
$VerbosePreference      = "continue"
$WarningPreference      = "continue"