Wednesday, 28 June 2017

Resetting OnCommand Insight 7.3 DWH admin Credentials the MySQL Way

The Scenario

Lost credentials to log into OnCommand Insight DWH - cannot login to either:

https://OCI_DWH_FQDN/dwh
https://OCI_DWH_FQDN/p2pd/servlet/dispatch

Image: Cannot login to OnCommand Insight DWH

The Story

After restoring a historical database into my OnCommand Insight 7.3 Data Warehouse, I managed to overwrite the admin user password with something that I didn’t know (same too the oadmin password.) It took a while to twig what had happened. I did some troubleshooting, twigged what the issue was, and then set about resolving the issue (without recourse to use tech support since this was just a lab system.)

Initial Troubleshooting

Are all the services running? YES

 Status    Service Name
 Started   MySQL
 Started   SANscreen Server
 Started   IBM Cognos*
 Started   IBM Cognos Content Database*

*At this stage we’re not really concerned about IBM Cognos services.

Can I login with DbVisualiser? YES


What do the logs say?

SANscreenAP.log @ {OCI_INSTALL_PATH}\SANscreen\cognos\c10_64\logs

The SANscreenAP.log contains login and role assignment entries (for Cognos).
When trying to log into the IBM Cognos Reporting Engine (https://OCI_DWH_FQDN/p2pd/servlet/dispatch), I was seeing:

Default Executor-thread-153 2017-06-27 10:05:00,613  [ERROR com.netapp.sanscreen.authentication.SANscreenAP] - The provided credentials are invalid:Server returned HTTP_UNAUTHORIZED for user admin

wildfly.log @ {OCI_INSTALL_PATH}\SANscreen\wildfly\standalone\log

Jboss/Wildfly is the Java app server that is ‘SANscreen Server’. No smoking gun was found in this log.

Then it twigged - the passwords had been overwritten when I restored a historical database!

Resetting SANscreen admin Credentials via MySQL

(1) Stop ‘SANscreen Server’>

net stop "SANscreen Server"

Wait for it to stop!

(2) Stop MySQL>

net stop MySQL

Wait for it to stop!

(3) In ‘Windows Explorer’ navigate to {OCI_INSTALL_PATH}\SANscreen\mysql

SHIFT + Right-click the bin folder and select ‘Open command window here’

(4) Execute the following command in the command prompt

mysqld.exe -u root --skip-grant-tables

and leave the command prompt window as it is.

Image: The First Command Prompt (this will run until we close the window)

(5) Open a new command prompt in {OCI_INSTALL_PATH}\SANscreen\mysql\bin and run>

mysql

Image: The Second Command Prompt runs mysql (MySQL 5.7.17)

(6) To show databases type>

show databases;

(7) To switch to the SANscreen database type>

use sanscreen;

(8) To show the tables type>

show tables;

(9) We’re interested in the user table. To show the columns>

show columns from sanscreen.user;

 (10) To show the username and passwords (encrypted)>

select username,passwd from sanscreen.user;

(11) I’m going to reset the admin password back to ‘admin123’. I happened to check on another install and see the passwd for ‘admin123’ = +GW1NiOxIf007lQmx5Llwzr4wic=

UPDATE user SET passwd = '+GW1NiOxIf007lQmx5Llwzr4wic=' WHERE username = 'admin';

(12) Close the first command prompt with the X in the top-right corner.

(13) In the second command prompt type>

exit;

(14) Next I had to reboot the OCI Data Warehouse server.

(15) When everything’s back up, try logging in as username = admin, password = admin123.

THE END

Credits


No comments:

Post a Comment