Reactivating SYSTEM user in SPS11 without another admin user

[Former Member]

We have been using an SPS11 instance for testing which has been going well up to the point we discovered that the SYSTEM returns "authentication failed"

We assumed that someone had accidentally changed the SYSTEM password so set out to reset the SYSTEM user using this method here: SAP HANA Academy - SAP HANA Security: Reset SYSTEM Password [SPS 09] - YouTube there is no video for SPS11 so I assume this is still correct, it looks like it works:

my-host1:/usr/sap/SM1/HDB31> exe/hdbindexserver -resetUserSystem
starting interactive mode for resetting user SYSTEM...
service startup...
accepting requests at 127.0.0.1:33103; 127.0.0.2:33103
assigning to volume 3 ...
run as transaction master
resetting of user SYSTEM - new password:
Passw0rd123
new pw accepted.
(re)activating user SYSTEM...
done
prepare for shutting service down...
preparing to shutdown...
stopping dynamic range partitioning
stopping federation statistics collection
stopping extended storage heartbeat thread
stopping LOBGarbageCollectorThread  '' 
stopping EPM  '' 
stopping Embedded Catalyst Services  '' 
stopping PlanningEngine  '' 
stopping SQL session service...
stopping SQL Plan cache...
stopping QueueServer...
stopping planviz...
stopping TRexApiSystem...
stopping executor...
DebuggingBackend shutdown...
CalculationEngineManager shutdown...
TaskManager shutdown...
TaskStatisticsManager shutdown...
CacheMgr shutdown...
Sampling Session Monitor shutdown...
GraphEngineMgr shutdown...
Invalidator shutdown...
RClient shutdown...
SAMLFactory shutdown...
VSI shutdown...
unassign all...
udivmgr shutdown...
close trace files...
PersistenceLayer shutdown...
joinengine shutdown...
prepared to shutdown.
Disabling signal handler...
Stopping self watchdog...
Stopping request dispatcher...
Stopping responder...
Stopping channel waiter...
Shutting service down...
libxml shutdown...
QueryMediator cleanup...
Stopping threads...
Stopping communication...
Deleting self watchdog...
Deleting request dispatcher...
Deleting responder...
Deleting service...
Deleting threads...
Deleting pools...
Deleting configuration...
Removing pidfile...
shutdown is completed.

However, this did not allow us access to the user still. After some digging we found that SPS11 locks the SYSTEM user after too many invalid connection attempts (Quite possible in a sand boxed development environment) if force_first_password_change is set to true (the default value), since we had not changed it we're now locked out.

We have no user with any admin permissions what so ever so can not unlock this user via HANA Studio.

After a little bit more staring at the screen we did notice that in the output it claims to activate the SYSTEM user:

new pw accepted.
(re)activating user SYSTEM...
done

This lead us to assume there was an internal issue with HANA and decided to try restarting the instance to see if it was a temporary issue, sadly this did not fix our authentication problems.

So at this point we're stuck, any help would be greatly appreciated!

Thanks

[davidebruno]

You can follow this thread

[Former Member]

Is it possible to start the database in console mode in SPS11? It does not seem to work.

Also no users have development permissions

[lbreddemann]

The process to reset the SYSTEM user password is documented for each current SPS in the SAP HANA Administration Guide. For SPS 11 it's in chapter 4.2.1.3 Reset the SYSTEM User's Password.

And no, there is no general console mode for SAP HANA systems available.

For your situation (system user locked out) you should be able to repeat the reset password procedure.

It not only sets the password but also runs the following commands which will unlock the user account and force you to change the password:

ALTER USER SYSTEM ACTIVATE;

ALTER USER SYSTEM VALID FROM NOW UNTIL FOREVER;

ALTER USER SYSTEM RESET CONNECT ATTEMPTS;

ALTER USER SYSTEM ENABLE PASSWORD;

ALTER USER SYSTEM FORCE PASSWORD CHANGE;

[Former Member]

How do I run this SQL without having admin access to the system?

Thanks

[lbreddemann]

You misunderstood me.

These commands are automatically executed when you reset the system user. I only posted them for illustration purposes, so that you get a better understanding what happens internally when you execute the resetUserSystem procedure.

[Former Member]

Oh yes, sorry.

But why did they not work when I did reset the SYSTEM user? (shown above)

Thanks

[lbreddemann]

Here I have to guess what might have happened in your landscape: somebody tried to log in again and again with the old (now wrong) password.

This can easily be the case when the instance gets restarted after the user reset and there are e.g. SAP HANA Studio instances with auto-reconnect and stored credentials open.

Maybe checking the indexserver trace file provides more insight into this.

But to unlock the user in your system I would actually just try the procedure again, following the documentation step by step.

[Former Member]

I think perhaps the user isn't locked and there is something else happening here. I got another user locked on purpose and it gave a different error message when trying to log in, saying the user was locked and to try again in x. This is different to when I try to log in with the SYSTEM user where it says I have invalid credentials.

All services are reporting fine and there are no alerts that are out of the ordinary. SO I believe the HANA instance is fine. I shall try again though just to be sure (Attempt 5) although I'm not hopeful.