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
Just a reminder. You can use the default dwh / sanscreen credentials as below:
ReplyDeleteC:\Program Files\SANscreen\mysql\bin>mysql -u dwh -p
Enter password: *********
Note: This output is from a lab where the install location was C:\Program Files. The install location will be different on production OCI DWH servers.