How to change your Jaspersoft Database password integrated with Clarity PPM

Document ID : KB000033318
Last Modified Date : 21/03/2019
Show Technical Document Details
Introduction:

Problem:

Unable to Login to Jaspersoft Console after changing the database password. Also all the reports are failing from Clarity with below
Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [99999]; error code [28000]; ORA-28000: the account is locked 
    Environment:
    Clarity PPM Integrated with Jaspersoft 
    Instructions:

    Resolution:

    To recreate the password either in plain text or encrypted as per organizational security policy using below steps

    Recreate the password in plain text:

    1. Open the following file with a text editor: $JSTomcat \webapps\reportservice\META-INF\context.xml
    2. Replace the details on the connection, enter the password in non-encrypted way for database, save and restart Jaspersoft.
    Recreate the password in encrypted way in Jaspersoft 5.6.1:
    1. Go to your Jaspersoft Install files: $jasper_install\jasperreports-server-5.6.1-bin\buildomatic\default_master.properties
    2. Enter the correct value for Jasper DB Password in plain text
    3. Ensure all other values are still valid, such as Tomcat directory etc.
    4. Change the encrypt.done=true to encrypt=true
    5. Save the file.
    6. Now navigate to $jasper_install\jasperreports-server-5.6.1-bin\buildomatic
    7. Run the command: js-ant refresh-config this will remove and recreate all the configuration files without deploying them to the application server.
    8. Once done, get the context file from: $jasper_install\jasperreports-server-5.6.1-bin\buildomatic\build_conf\default\webapp\META-INF\context.xml and replace the one in $JSTomcat\webapps\reportservice\META-INF\context.xml
    9. Restart Jaspersoft Tomcat
    Recreate the password in encrypted way in Jaspersoft 7.1:
    1. Go to your Jaspersoft Install files: $jasper_install\jasperreports-server-7.1-bin\buildomatic\default_master.properties
    2. Add the below parameters to the $jasper_install\jasperreports-server-7.1-bin\buildomatic\default_master.properties
    3. dbPassword=<Enter your Database Password>
      propsToEncrypt=dbPassword
      appServerType=tomcat
      dbType=<Enter your Database Type> (Example oracle or sql
      appServerDir==<Enter your Jaspersoft Installed Path> (Example C:\\App\\Jasper)
      encrypt=true
      Enter the correct value for dbPassword, dbType, appServerDir in plain text
       
    4. Save the file.
    5. Navigate to $jasper_install\jasperreports-server-7.1-bin\buildomatic
    6. Run the command: js-ant refresh-config and you should see the Build Success once the execution is completed.
    7. Now again go to your Jaspersoft Install files: $jasper_install\jasperreports-server-7.1-bin\buildomatic\default_master.properties
    8. You will see the dbPassword encrypted (Example: dbPassword=ENC-5024da1014088fdf4dc15a8f797f98f6d976ffd98e692173674e090778296f-)
    9. Stop the Jaspersoft services
    10. Open the following file with a text editor: $JSTomcat \webapps\reportservice\META-INF\context.xml
    11. Copy the encrypted database password generated in Step 9 and update the password in $JSTomcat \webapps\reportservice\META-INF\context.xml <Resource name="jdbc/jasperserver" auth="Container" type="javax.sql.DataSource"maxActive="100" maxIdle="30" maxWait="10000"username="JASPER" password="ENC-b16918c25f30f1cc643a40d7fefb0918c592a5ccccb6043cd6712ca64c088c28-" driverClassName="oracle.jdbc.OracleDriver" accessToUnderlyingConnectionAllowed = "true" validationQuery="SELECT 1 FROM DUAL"testOnBorrow="true"url="jdbc:oracle:thin:@Oracle:1521/jspdb"factory="com.jaspersoft.jasperserver.tomcat.jndi.JSCommonsBasicDataSourceFactory"/>
    12. Restart the Jaspersoft Services
     
     
    Additional Information:
    Note: In order to encrypt the password you will need to have the Jaspersoft Installable on the Jaspersoft server. After the Jaspersoft Database password is corrected you should be able to login to Jaspersoft console. However running reports from Clarity --> Advance Reporting will continue to fail. In order to correct the same you will need to perform the below steps
    1. Login to Clarity --> Run the Job Create and Update Jaspersoft Users
    2. Navigate to Clarity à Advance Reporting Link à Users
    3. Search for user jasperadmin and edit the user
    4. Look for tab Attributes on the right-hand panel
    5. Delete all the attributes except domainWhitelist as that is read only
    6. Now login to Clarity application server
    7. Navigate to CLARITY_HOME\bin and execute update jasperParameters