How to quickly change all the password for all ALM related databases using MSSQL when SQL Authentication (mixed-mode) is used
1) In the ALM deployment path open the "siteadmin.xml" for editing
2) Change the <DefaultUserPassword> value to TXT:<cleartext_password>
For example, the password being changed to ârootrootââ¦
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â¦
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
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>'
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>'
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