How to quickly change all the password for all ALM related databases using MSSQL when SQL Authentication (mixed-mode) is used

  • KM03650008
  • 04-Jun-2020
  • 04-Jun-2020

Summary

It may be necessary to change the password "td" in MSSQL when SQL auth is used

Question

How to quickly change all the password for all ALM related databases using MSSQL when SQL Authentication (mixed-mode) is used

Answer

Instructions...

1)    In the ALM deployment path open the "siteadmin.xml" for editing
    Windows: <deployment_path>\ALM\webapps\qcbin\WEB-INF\siteadmin.xml
    Linux: <deployment_path/ALM/webapps/qcbin/WEB-INF/siteadmin.xml
2)    Change the <DefaultUserPassword> value to TXT:<cleartext_password>
    For example, the password being changed to “rootroot”…
    <DefaultUserPassword>TXT:rootroot</DefaultUserPassword>
3)    Save Changes
4)    Examine the value for the element “DBName” this is the name of the site admin database
5)    Login to SQL Management Studio and open a query to the database noted in Step 4
6)    Backup the site admin database
7)    Issue the following query to set the clear-text password for all ALM projects, for example, the password being changed to “rootroot”
    update td.projects set DB_USER_PASS = 'TXT:rootroot'
8)    Stop the ALM service
9)    In SQL Management Studio change the password for the user “td”
10)    Start ALM. ALM should come back online and all the projects available
11)    Login to the Site Admininistation
12)    Select the "Site Projects" tab.
13)    Select one of the projects. Note the value of the "Physical Directory"
14)    Remove the project
15)    Navigate to the path noted in Step 13 and open the “dbid.xml” file in that path
16)    Edit the element <DB_USER_PASS> and specify the TXT:<password>
For example, using the password “rootroot”: <DB_USER_PASS>TXT:rootroot</DB_USER_PASS>
17)    Save changes
18)    Return to the ALM Site Administration and restore the project by pointing to the “dbid.xml” file edit in Step 16
19)    Once the project is restored open the same dbid.xml file and examine the value for the <DB_USER_PASS> element. It will look something like this…
    <DB_USER_PASS>QCC:z0Z5aNvQ9ehN/rsRNPDlmg==</DB_USER_PASS>
20)    Copy the value for the DB_USER_PASS element, in this example: QCC:z0Z5aNvQ9ehN/rsRNPDlmg==
21)    Open the “siteadmin.xml” file, same as Step 1
22)    Change the <DefaultUserPassword> value to the value noted in Step 20
    For example…
    <DefaultUserPassword> QCC:z0Z5aNvQ9ehN/rsRNPDlmg==</DefaultUserPassword>
23)    Save changes
24)    Login to SQL Management Studio and open a query to the database noted in Step 4
25)    Issue the following query to set the clear-text password for all ALM projects, for example, the password being changed to the encrypted value taken from Step 20
    update td.projects set DB_USER_PASS = '<value>'
For example…
    update td.projects set DB_USER_PASS = 'QCC:z0Z5aNvQ9ehN/rsRNPDlmg=='
26)    Issue the following statement to set the same encrypted password so new projects use the same new password…
    update td.DBSERVERS set DB_USER_PASS = '<value>'
    For example…
    update td.DBSERVERS set DB_USER_PASS = 'QCC:z0Z5aNvQ9ehN/rsRNPDlmg=='
27)    Start ALM. It should start and all the projects available with encrypted passwords

Next, the "dbid.xml" files for each project will have the old DB_USER_PASS value instead of the new one.
With the new encrypted password in hand, Step 10 above, use the Modify Dbid utility from here: https://support.microfocus.com/kb/kmdoc.php?id=KM02267685
This utility can be used to update some or all project "dbid.xml" files at once