Automating the Daunting Task of Hyperion Database Password Changes

Disclaimer: This is information is for advanced level administrators. Please use at your own risk. 

This post is about changing the underlying database passwords that form the foundation of the Hyperion EPM environment. In Hyperion nearly every product has a corresponding database schema/user, depending on how the environment is setup. In my experience clients infrequently change these database passwords, if ever. Perhaps this is just lax security or maybe it is due to the daunting efforts involved in password changes. I'd like to show a different approach which might help make the process easier.

First, let's look at the typical process for changing database passwords.

1. Change the HSS/Registry password:
   On EACH node in your environment:
   Open config tool on each node and given that the connection to HSS now fails, it will prompt you to re-enter the database credential to HSS registry from scratch. You will then need to run the "Foundation -> Configure database" configure task.
2. For each EPM module with a database connection
   For instance: CalcMgr, Planning, HFM..etc
   Run the config tool and run the database configuration task for each module. Note: when updating it will prompt you to also redeploy the application server to update the WebLogic passwords. You would need to enter "perform first time configuration of database", make certain the schema in the drop down list is pointing at the correct DB for this module, and then it will prompt "would you like to reuse or drop". You need to correctly select "reuse" or risk erasing all your data.
3. Update product specific passwords. There are some passwords which do not fit the mold described in 2. These are things like .UDL files for HFM, FDM Applications, Planning Applications. Each of these components will need evaluated and handled differently. 

The tasks in 1 and 2 can be readily automated.  In my opinion the steps in 1 and 2 are very time consuming and potentially risk prone if you make a mistake. I really like to avoid using config tool and redeploying as much as possible due to its buggy behavior. Let's re-evaluate what happens in step 1 and 2 and try to automate. The third step is harder since it depends based on the application, and perhaps not worth trying to automate.

1. Changing the HSS Registry password consists of two parts. There is a reg.properties file which simply contains the JDBC connection details and is used when opening config tool. If config tool cannot connect to the HSS database, it will prompt to re-enter the DB info for the purpose of recreating the reg.properties file.
   The second part is updating the HSS registry with the correct DB HSS/Registry password.
   
   Consequently, the password is stored in the file system and in the database.
   
   Automation:
   The encrypted value for reg.properties is not possible to create outside of using config tool. So the first node you will need to open config tool and re-enter the database credentials, and run the "Foundation -> configure database" task. This creates a new reg.properties on this node and also updates the HSS password in the database (HSS registry). For automation, do not re-run this on each node, just copy the updated reg.properties to each node:
   copy d:\Oracle\Middleware\user_projects\epmsystem1\config\foundation\11.1.2.0\reg.properties \\server2\d$\Oracle\Middleware\user_projects\epmsystem1\config\foundation\11.1.2.0\reg.properties copy d:\Oracle\Middleware\user_projects\epmsystem1\config\foundation\11.1.2.0\reg.properties \\server3\d$\Oracle\Middleware\user_projects\epmsystem1\config\foundation\11.1.2.0\reg.properties ...
2. Step two is updating the passwords for each EPM module. Behind the scenes this step has two parts. a) Updates EPM module password in the EPM System Registry. b) Redeploying the Application Server updates the JDBC connections in WebLogic Essentially, Oracle\Middleware\user_projects\domains\EPMSystem\config\jdbc Automation for a) The epmsys_registry tool can be used to change the HSS Registry connections. You can find a list of all the database connections stored in the registry by running epmsys_registry without any arguments to generate a registry report. Then search for DATABASE_CONN to identify the database connections. To change from the command line you can use this syntax:
   set SYSREG=D:\Oracle\Middleware\user_projects\epmsystem1\bin\epmsys_registry
   
   call %SYSREG% updateencryptedproperty SYSTEM9/FOUNDATION_SERVICES_PRODUCT/EPMA_PRODUCT/DATABASE_CONN/@dbPassword NewPassword ... Continue for each database connection. Remember, you can skip the EPMRegistry connection since you already updated this in step 1. Automation for b) WebLogic has a scripting language, WLST, which can be used to script the password change in WebLogic. To find the list of datasources which need modfied login to the WebLogic Admin console and look under the JDBC connections. Note: the list of connections does not necessarily match those in part a, above.
   WLST Script
   
   connect('epm_admin','','t3://wl_domain:7001')
   edit()
   startEdit()
   pass_aif = encrypt('NewPassword')
   
   dsName = 'aif_datasource'
   cd('/JDBCSystemResources/'+dsName+'/JDBCResource/'+dsName+'/JDBCDriverParams/'+dsName)
   set('PasswordEncrypted', pass_aif)
   
   save()
   activate()

Conclusion: 

The steps shown here can be prepared and scripted before the maintenance. This dramatically eliminates the time it takes to perform the maintenance since the time consuming steps are prepared up front and not being performed on demand when the environment is already down.