How to reset a corrupted SYSTEM User Password in sap hana?
There are several ways to change the system user password.
First of all 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.
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
-- In this example all services are running $> sapcontrol -nr 07 -function GetProcessList 12.07.2017 09:31:15 GetProcessList OK name description dispstatus textstatus starttime elapsedtime pid hdbdaemon HDB Daemon GREEN Running 2017 03 13 14:07:46 2898:23:29 132500 hdbcompileserver HDB Compileserver GREEN Running 2017 03 13 14:08:46 2898:22:29 133329 hdbindexserver HDB Indexserver-BBT GREEN Running 2017 04 13 17:12:20 2152:18:55 96965 hdbindexserver HDB Indexserver-BHP GREEN Running 2017 05 23 10:51:11 1198:40:04 97453 hdbindexserver HDB Indexserver-LTB GREEN Running 2017 03 13 14:08:50 2898:22:25 133372 hdbindexserver HDB Indexserver-DBW GREEN Running 2017 03 13 15:00:47 2897:30:28 30612 hdbnameserver HDB Nameserver GREEN Running 2017 03 13 14:07:47 2898:23:28 132517 hdbpreprocessor HDB Preprocessor GREEN Running 2017 03 13 14:08:46 2898:22:29 133331 hdbwebdispatcher HDB Web Dispatcher GREEN Running 2017 03 13 14:10:49 2898:20:26 137711 hdbindexserver HDB Indexserver-GRB GREEN Running 2017 06 22 10:39:45 478:51:30 129126
2. ( Open an OS Session 2 )
: Shut down the instance :
sapcontrol -nr
2.1 Check current services status
- (Nothing should be running)
$> sapcontrol -nr < instance > -function GetProcessList
3. In a new session ( OS Session 3 )
start the name server with the -resetUserSystem option :
/usr/sap/BAT/HDB07/hdbenv.sh
/usr/sap/BAT/HDB07/exe/hdbnameserver -resetUserSystem
4. Enter a new password for the SYSTEM user.
The following message will be showing up but dont be surprise 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 :
$> 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 part of 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.
batadm@linux07$> hdbsql
hdbsql=> \c -i 07 -d SYSTEMDB -u system -p DoNotForgetThisTime01
7. ( Session Appli Server )Start all related application servers.
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
batadm@linux07> hdbsql => \c -i 07 -d SYSTEMDB -u USR_ADM -p ToKeepSecret01
Connected to BAT@localhost:30013
ALTER SYSTEM STOP DATABASE BBT;
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 number > -function GetProcessList | grep -i < database name >
sapcontrol -nr 07 -function GetProcessList | grep -i BBT
2. ( OS Session 3 ) : connected as < sid >adm to the server.
2.1 Export the tenant database name to a OS variable then start the index server
export DBNAME=BBT
/usr/sap/BAT/HDB07/hdbenv.sh
/usr/sap/BAT/HDB07/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 part of 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.
Resetting system password at Systemdb level example
Following a "how to do" description is an important part but getting an example help to understand.
The following sap hana system user reset password example has been created out of notes from a real situation.
A/. Get task details ready.
Server name : linux07
Instance number : 07
SID : BAT
Password ready: DoNotForgetThisTime01 ( cut and paste operation )
Different Contacts : Application users, Operation and monitoring, Project team leader, Operator
- Everyone concerned has been informed about the shutdown of the entire sap hana system.
- Application server stop and start tasks have been synchronized with the operator team.
- 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 batadm ) Check current services status:
batadm@linux07:/usr/sap/BAT/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 batadm ) : Shut down the instance :
/usr/sap/BAT/HDB07/exe/sapcontrol -nr 07 -function StopSystem HDB
21.09.2018 11:43:50
StopSystem
OK
3. ( OS Session 1 : connecter as batadm ) Check current services status - (All databases should be down)
batadm@linux07:/usr/sap/BAT/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 batadm ) Check for any remaining processes. Nameseerver in particular.
batadm@linux07:/usr/sap/BAT/HDB07> ps -ef | grep -i BAT
batadm@linux07:/usr/sap/BAT/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 batadm ) start the name server with the -resetUserSystem option :
/usr/sap/BAT/HDB07/hdbenv.sh
/usr/sap/BAT/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 batadm ) start the instance :
/usr/sap/BAT/HDB07/exe/sapcontrol -nr 07 -function StartSystem HDB
6. ( OS Session 1 : connecter as batadm ) Check current services status -
- All databases should be up and running,
- Rerun the following command wait until everything is GREEN
batadm@linux07:/usr/sap/BAT/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
batadm@linux07:/usr/sap/BAT/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 batadm)
- hdbsql
\c -i 07 -d SYSTEMDB -u system -p DoNotForgetThisTime01
Connected to localhost:30013
hdbsql SYSTEMDB=>
8. ( Os session Appli ) . Start application server
