How to reset a corrupted SYSTEM User Password in sap hana?

There are several ways to change the system user password. Whether it is at the SYSTEMDB level or at the tenant database level, the advisable procedure to follow is to use the standard SQL syntax: ALTER USER SYSTEM PASSWORD < new_password > to change the password. It can be done using hana studio SQL console or at the server level using hdbsql.
The second method is when you lost control, you cannot connect anymore or the system password is lost. Be Aware, this method is disruptive as the entire system has to be shutdown. It is not a complicated procedure but be careful and plan for service interruptions before going ahead. Take into account the time it takes to shutdown and restart the entire system, the time it takes for any application to become available to users. Production environment is obviously to be scheduled carefully but other environments such as development and validation will also affect projects, test and validation processes if it become unavailable.

: Last updated : 2018-10-14

Which procedure to follow to reset sap hana system user password

The procedure is simple but steps are different depending on whether you are dealing with the SYSTEMDB or with a single tenant database part of a multitenant database system. For the system database ( SYSTEMDB ) you just need to re-initiate a new password on the nameserver. For individual tenant database, resetting password will have to be done at the indexserver level. Keep in mind, dealing at the SYSTEMDB level required shutting down the whole system with all associated tenant databases. Dealing with a tenant database requires shutting down the database but does not affect other tenant databases nor the SYSTEMDB.

Steps to reset sap hana system password at the SYSTEMDB level

The following system password resetting method is for a system database of a multitenant database system. Note the procedure is the same for all hana versions 1 and 2. Keep in mind though, methods could change with time and with newer sap hana versions. It is advisable to check your hana system version in the first place and the sap documentation. The Sap Documentation below gives you access to all hana versions.

Preparing the operation.
- Contact all users who will be affected and agree about a date and time.
- Decide who will be dealing with the stop and start of the application server.
- Scheduled the operation.

0. ( Open an OS Session Appli Server )Shutdown all related application servers.

1. ( Open an OS Session 1 ) : connect as (< sid >adm) to the server.

1.1 Check current services status
$> sapcontrol -nr < instance > -function GetProcessList

2. ( Open an OS Session 2 ) : Shut down the instance :
/usr/sap/< SID >/HDB< instance >/exe/sapcontrol -nr -function StopSystem HDB
2.1 Check current services status - (There should be nothing running)
$> sapcontrol -nr < instance > -function GetProcessList

3. In a new session ( OS Session 3 ) start the name server with the -resetUserSystem option :

/usr/sap/HAS/HDB00/hdbenv.sh
/usr/sap/HAS/HDB00/exe/hdbnameserver -resetUserSystem

4. Enter a new password for the SYSTEM user.
The following message will be showing but others messages and details will keep on appearing as well: "resetting of user SYSTEM - new password" : Wait until no message comes up anymore and enter the new password ( Copy / Paste password would be best method )

Important Notes : This procedure resets the password for SYSTEMDB and shutdown the nameserver.

5. ( OS Session 4 ) start the nameserver which has just been stopped :
/usr/sap//HDB/exe/sapcontrol -nr < instance > -function StartSystem HDB

Important Notes : Whether the SYSTEM user was deactivated volontary or not, it will be reactivated. It is your responsability to deactivate it again if it is your company policy.

6. Check system user connection with the new password.

Using hdbsql would be advisable in order to avoid misspelling with the password.

tawadm@linux07$> hdbsql
. . .
hdbsql=> \c -i 07 -d SYSTEMDB -u system -p DoNotForgetThisTime01

7. ( Session Appli Server )Start all related application servers.

. . .
. . .

Reset SystemDb System password example?
How to connect to SYSTEMDB with hdbsql?
Sap Documentation

Steps to reset sap hana system password for a TENANT database

The following system password resetting method is for a tenant database of a multitenant database system sap hana version 2. Again cross check hana version and sap documention. The Sap document link below will give you access to any hana version on the reset password subject for a tenant database.

1. ( OS Session 1 )
Stop the tenant database using the SQL statement:
ALTER SYSTEM STOP DATABASE < database_name >
You can execute the command using hana studio or hdbsql.
ex : hdbsql

tawadm@linux07> hdbsql => \c -i 07 -d SYSTEMDB -u admin_user -p ToKeepSecret01
Connected to TAW@localhost:30013
ALTER SYSTEM STOP DATABASE DBHR2;
0 rows affected (overall time 18.077877 sec; server time 18.074765 sec)

2. ( OS Session 2 )
Check the tenant database is down.
sapcontrol -nr < instance > -function GetProcessList | grep -i < tenant database name >

2. ( OS Session 3 ) : connected as < sid >adm to the server.

2.1 Export the tenant database name then start the index server
/usr/sap/< SID >/HDB< instance > export DBNAME=< database_name >
/usr/sap/< SID >/HDB< instance >/hdbenv.sh
DBNAME=the_database_name /usr/sap/< SID >/HDB< instance >/exe/hdbindexserver �port < internal port > -resetUserSystem
2.2 Enter a new password when the message "resetting of user SYSTEM - new password" appears.

Important Notes : This procedure resets the password for tenant database and shutdown the related indexserver.

3. ( OS Session 1 )
Restart the tenant database with the SQL syntax:
ALTER SYSTEM START DATABASE < database_name >

Important Notes : Whether the SYSTEM user was deactivated volontary or not, it will be reactivated. It is your responsability to deactivate it again if it is your company policy.

6. ( OS Session 2 )
Check system user connection with the new password.

How to connect to a tenant database using hdbsql?
Sap Documentation

How long does it take to reset system user password in sap hana?

Without taking into account the system shutdown and startup, the procedure ( preparing and executing ) could take 15 to 30 minutes to reset a system user password. It is long comparing to the simple SQL command which takes few seconds. The global time to get back to a usable database system depends on how long it takes to restart either the overall system or just the tenant database. Either way, a production database system downtime could be dangerously affected by this reset password operation.

How to prevent resetting sap hana system password at the OS level?

Like any other users the system user can be locked. The system user password can also be forgotten or lost. In order to face those 2 possibles situations and find an immediate non disruptive solution here is what can be done. Create a very specific user to unlock other user accounts or to change user password. The other possibility is to create a unlock and change user password stored procedure.

How to create a sap hana user to handle lost of password and locked user problems?

Putting in place a user administrator account has got many advantages. The task of dealing with everything regarding user accounts can be given to a specific team. New user, De-activated user, Change of password can be independantly dealt with. The system administrator can concentrate on more important issues such as performance, error and alert messages. In order the create a user manager account with the ability to unlock user and reset password, you have to assign the system privilege USER ADMIN to the account profile.

How to put in place a sap hana administrator user?

How to create a sap hana procedure to unlock user or change user password?

Resetting system password at Systemdb level example?

A/. Get task details ready.

Server name : linux07
Instance number : 07
SID : TAW
Password ready: DoNotForgetThisTime01 ( cut and paste operation )
Different Contacts : Application users: ..., Operation and monitoring: ..... Project team leader:....

- Everyone concerned has been informed about the shutdown of the entire sap hana system.
- Application server stop and start task is being synchronized with the operation.
- Who will stop and start the application server has been decided.
- 4 differents os sessions ready.

B/. Let s start the procedure.


O.   ( Os session Appli ) . Stop application server.

												
1.   ( OS Session 1 : connecter as tawadm ) Check current services status:

tawadm@linux07:/usr/sap/TAW/HDB07> 
sapcontrol -nr 07 -function GetProcessList | awk -F"," '{printf "%20s %20s %10s %10s %23s %15s %10s\n",$1, $2, $3, $4, $5, $6, $7 }'

 21.09.2018 13:26:52
      GetProcessList
                  OK
                name          description  dispstatus  textstatus               starttime     elapsedtime        pid
           hdbdaemon    HDB Daemon             GREEN    Running     2018 09 21 13:17:22         0:09:30      66124
    hdbcompileserver    HDB Compileserver      GREEN    Running     2018 09 21 13:17:32         0:09:20      67221
       hdbindexserver   HDB Indexserver-DBHR2  GREEN    Running     2018 09 21 13:17:34         0:09:18      63324
      hdbindexserver    HDB Indexserver-DBBW2  GREEN    Running     2018 09 21 13:17:34         0:09:18      67326
       hdbindexserver   HDB Indexserver-DBTBW  GREEN    Running     2018 09 21 13:17:34         0:09:18      67520
       hdbnameserver    HDB Nameserver         GREEN    Running     2018 09 21 13:17:23         0:09:29      66956
     hdbpreprocessor    HDB Preprocessor       GREEN    Running     2018 09 21 13:17:32         0:09:20      67703
     hdbwebdispatcher   HDB Web Dispatcher     GREEN    Running     2018 09 21 13:20:39         0:06:13      73938
	 
	 
2.   ( OS Session 2 : connecter as tawadm ) : Shut down the instance : 

/usr/sap/TAW/HDB07/exe/sapcontrol -nr 07 -function StopSystem HDB

21.09.2018 11:43:50
StopSystem
OK



3.   ( OS Session 1 : connecter as tawadm )  Check current services status - (All databases should be down)

tawadm@linux07:/usr/sap/TAW/HDB07> 
sapcontrol -nr 07 -function GetProcessList | awk -F"," '{printf "%20s %20s %10s %10s %23s %15s %10s\n",$1, $2, $3, $4, $5, $6, $7 }'

 21.09.2018 15:17:21
      GetProcessList
                  OK
                name          description  dispstatus  textstatus               starttime     elapsedtime        pid
           hdbdaemon           HDB Daemon       GRAY    Stopped                                              77506

		   
		   
3.1   ( OS Session 1 : connecter as tawadm )  Check for any remaining processes. Nameseerver in particular.

tawadm@linux07:/usr/sap/TAW/HDB07> ps -ef | grep -i TAW
tawadm@linux07:/usr/sap/TAW/HDB07> ps -ef | grep -i hdbnameserver 

#
#  If you find a process, investigate before going any further  How to check for sap hana zombie process?     	   
#

		   
4.  ( OS Session 3 : connecter as tawadm )  start the name server with the -resetUserSystem option :

    /usr/sap/TAW/HDB07/hdbenv.sh
    /usr/sap/TAW/HDB07/exe/hdbnameserver -resetUserSystem

4.1 Enter a NEW PASSWORD for the SYSTEM user.   

    when the following message  "resetting of user SYSTEM - new password" appears :
    wait until no other messages come up anymore and enter the new password ( Copy / Paste password ) 

	
	
5. ( OS Session 4 : connecter as tawadm ) start the instance :

    /usr/sap/TAW/HDB07/exe/sapcontrol -nr 07 -function StartSystem HDB
	
	 
	 
6. ( OS Session 1 : connecter as tawadm ) Check current services status - 
   - All databases should be up and running,  
   - Rerun the following command wait until everything is GREEN 

   
tawadm@linux07:/usr/sap/TAW/HDB07> 
sapcontrol -nr 07 -function GetProcessList | awk -F"," '{printf "%20s %20s %10s %10s %23s %15s %10s\n",$1, $2, $3, $4, $5, $6, $7 }'

 21.09.2018 13:26:52
      GetProcessList
                  OK
                name          description  dispstatus  textstatus               starttime     elapsedtime        pid
           hdbdaemon    HDB Daemon             GREEN     Running     2018 09 21 13:17:22         0:09:30      66124
    hdbcompileserver    HDB Compileserver      GREEN     Running     2018 09 21 13:17:32         0:09:20      67221
       hdbindexserver   HDB Indexserver-DBHR2  GREEN     Running     2018 09 21 13:17:34         0:09:18      63324
      hdbindexserver    HDB Indexserver-DBBW2  YELLOW    Running     2018 09 21 13:17:34         0:09:18      67326
       hdbindexserver   HDB Indexserver-DBTBW  YELLOW    Running     2018 09 21 13:17:34         0:09:18      67520
       hdbnameserver    HDB Nameserver         GREEN     Running     2018 09 21 13:17:23         0:09:29      66956
     hdbpreprocessor    HDB Preprocessor       GREEN     Running     2018 09 21 13:17:32         0:09:20      67703
     hdbwebdispatcher   HDB Web Dispatcher     GREEN     Running     2018 09 21 13:20:39         0:06:13      73938

	 
tawadm@linux07:/usr/sap/TAW/HDB07> 
sapcontrol -nr 07 -function GetProcessList | awk -F"," '{printf "%20s %20s %10s %10s %23s %15s %10s\n",$1, $2, $3, $4, $5, $6, $7 }'

 21.09.2018 13:26:52
      GetProcessList
                  OK
                name          description  dispstatus  textstatus               starttime     elapsedtime        pid
           hdbdaemon    HDB Daemon             GREEN    Running     2018 09 21 13:17:22         0:09:30      66124
    hdbcompileserver    HDB Compileserver      GREEN    Running     2018 09 21 13:17:32         0:09:20      67221
       hdbindexserver   HDB Indexserver-DBHR2  GREEN    Running     2018 09 21 13:17:34         0:09:18      63324
      hdbindexserver    HDB Indexserver-DBBW2  GREEN    Running     2018 09 21 13:17:34         0:09:18      67326
       hdbindexserver   HDB Indexserver-DBTBW  GREEN    Running     2018 09 21 13:17:34         0:09:18      67520
       hdbnameserver    HDB Nameserver         GREEN    Running     2018 09 21 13:17:23         0:09:29      66956
     hdbpreprocessor    HDB Preprocessor       GREEN    Running     2018 09 21 13:17:32         0:09:20      67703
     hdbwebdispatcher   HDB Web Dispatcher     GREEN    Running     2018 09 21 13:20:39         0:06:13      73938

	
	
7. Check connection with new password ( OS Session 1 : connecter as tawadm)

   - hdbsql	
    \c -i 07 -d SYSTEMDB -u system -p  DoNotForgetThisTime01
	Connected to localhost:30013
	hdbsql SYSTEMDB=>


8.   ( Os session Appli ) . Start application server