×

Sap Support

- Sap Support - Dump files for support

Log & Trace file

- Log and trace directories - Investigate backup log content
<

Tools : Hana Studio

- Installation - Using hana studio - Using Studio SQL Console

Tools : hdbrsutil

- hdbrsutil tips & tricks

Tools : Hana Cockpit

- Install hana cockpit 2 complete example - Un-Install hana cockpit 2
<

Disk

- Disk space used by log segments - Reclaim space from log segments - Retrieve table index space used on disk

Specific Operations

- Adding physical memory

Sap Hana User Privileges

- Role and privileges - Grant read access to a schema

Stored procedures

- Creating a procedure - Dropping a procedure - Compiling a procedure - Executing a procedure - Working with procedures - Using anonymous block

Sap Hana functions

- DateTime : ADD_YEARS - DateTime : EXTRACT

Parameters

- Configuring sap hana - Kill long queries automatically

SQL

- Sql guide line - How to use SQL -Sql references - Sql tips & tricks for tables

SQL Query

- SQL & Tables - SQL & syntaxes - How to practice SQL
<

Sap Hana Tables

- Managing tables - Various ways to create Tables - Sap Hana Tables - Sap Hana Temporary Tables

Table description & DDL

- Retrieve table description - Table description using table-columns-view - Table ddl with hana studio - Table ddl using 'get_object_definition' procedure - Table ddl using hdbsql

SELECT data

- Various ways to select data

INSERT data

- Various ways to insert data

Sap Hana Users

- Managing Users - Create user with hana studio - Create user with sql Script - Create multiple users in one go - Copy a user - Create User with SQL script - Create User via Hana Studio - Update User - Drop User

Create Standard User

- Create standard & personal User

Create Technical User

- Create a technical User - Create backup users - Create administrator users - Create Modeler User - Create Data provisioning User - Create Cockpit User - Create a user to unlock any users
<

* 413: last n passwords can not be reused: SQLSTATE: HY000

This error is due to passwords security rules. Different parameters exist in order to reinforce security connection. In this context, new password cannot be the same as the previous password. The parameter "last_used_passwords" can be updated according to your need. By default it is set to the 5 latest passwords.


How to troubleshoot hana error "* 413: last n passwords can not be reused:"?

This is an error which will occur when your sap hana user default password content checkup is on. You cannot re-use the same previous password by default. The default is set to the last 5 passwords. Apart from personal users, this situation could be very tricky if you have to update the SYSTEM or a Technical user password. In both cases, it is urgent to get it right quickly.

In most cases, the situation is simple; change your sap hana user password immediately to another new one and you will be fine. DO NOT try to change anything if it is not your personal user. Technical users such as BACKUP, MONITORING, DEVELOPER, ADMIN_USER need more attention as they are linked to batch procedures and team work. A new password could stop batch processing to work and will stop certain team member to login to their working environment.

Beware, if too many unsuccessful login occur, the user will be locked automatically after too many attempts. This is valid for any user. This will also happen for technical users if someone tries to run a procedure which fails to many times due to wrong password.

Procedure to solve sap hana error "* 413: last n passwords can not be reused "

The error "* 413: last n passwords can not be reused " is due to a sap hana default sap hana password policy which enforces security. It is not recommended to de-activate the policy. However you may be forced to do it temporary if you have to reinitiate a technical user password.

1. - Identify the type of user you are dealing with
2. - Check the hana password policy
3. - Check whether the user is locked or not
4. - Update user password
5. - Unlock user
6. - Test the connexion


1. - Identify the type of user you are dealing with

There are 3 different type of users :

-The standard PERSONAL user which is assigned to a specific person.
The sap hana password policy is likely to be based on the following criteria : password has to be changed at regular date interval, the account will expired after a specific date.

-The TECHNICAL user which represents a team or a function.
Userstored key are likely used to connect to a sap hana System. The company may also follow a change of password policy on technical user.

-The SYSTEM or ADMIN user
The SYSTEM user may be locked for security purposes. It could be replaced by one or more admin users with relevant and specific system privileges. Nevertheless, you might come across changing password at one stage.

-- NB : All users are the same as far as the way they are created. Differences will appear with privileges and options activated or not. System User is unique and different from anything else. -- To identify a standard user to a technical user you have to rely on you company naming policy. -- Otherwise you have to find out which sap hana users are used within shell script or within batch procedures, which users have user store keys. Those users will be technical users. -- Normaly, those users have no password expiration date. -- Here we use user name with company naming policy. -- SELECT * from USERS where user_name like 'USR_TECH_BATCH'; SELECT * from USERS where user_name like 'USR_ADMIN'; SELECT * from USERS where user_name like 'SYSTEM'; SELECT * from USERS where user_name like 'USR_PAUL_SMITH';

2. - Check what is the sap hana password policy setting.

There are 2 things to check up: The password policy settings and the user type. The password policy is set by default to 5. In order words, the last 5 passwords have to be different. This "last_used_passwords" password policy parameter can be modified. The user type is important. Certain technical users require keeping the same password until a formal password and user store key update.

● For SYSTEMDB : This is the overall configuration. bobadm@linux06:/usr/sap/bob/HDB00> hdbsql Welcome to the SAP HANA Database interactive terminal. Type: \h for help with commands \q to quit hdbsql=> \c -i 00 -d SYSTEMDB -u system -p TotalSecret01 Connected to bob@linux06.ibm.world:30213 \al hdbsql=> SELECT * from M_INIFILE_CONTENTS where key = 'last_used_passwords'; | FILE_NAME | LAYER_N | TENANT_N | HOST | SECTION | KEY | V | | --------------- | ------- | -------- | -------- | --------------- | ------------------- | - | | indexserver.ini | DEFAULT | | | password policy | last_used_passwords | 5 | | nameserver.ini | DEFAULT | | | password policy | last_used_passwords | 5 | \q ● For a specific TENANT database : bobadm@linux06:/usr/sap/bob/HDB00> hdbsql Welcome to the SAP HANA Database interactive terminal. Type: \h for help with commands \q to quit hdbsql=> \c -i 00 -d AtenantDB -u system -p TotalSecret01 Connected to bob@linux06.ibm.world:30247 \al SELECT * from M_INIFILE_CONTENTS where key = 'last_used_passwords' | FILE_NAME | LAYER_NA | TENANT_N | HOST | SECTION | KEY | V | | --------------- | -------- | -------- | -------- | --------------- | ------------------- | - | | indexserver.ini | DEFAULT | | | password policy | last_used_passwords | 5 | | indexserver.ini | SYSTEM | | | password policy | last_used_passwords | 5 | | indexserver.ini | DATABASE | | | password policy | last_used_passwords | 5 | \q

3. - Check whether the user is locked or not

You have been call to update a user password but the user may be locked as well. Check whether the 'MAXIMUM_INVALID_CONNECT_ATTEMPTS' has been reached or not. (To do that, cross check 'INVALID_CONNECT_ATTEMPTS' column from table USERS, and 'MAXIMUM_INVALID_CONNECT_ATTEMPTS' from M_PASSWORD_POLICY ) If there is a match, the user has to be unlocked.
Important Note : the System User can also be locked for the "too many attempts" reason.

-- Find out why the user is locked : SELECT USER_NAME, USER_DEACTIVATED, DEACTIVATION_TIME, INVALID_CONNECT_ATTEMPTS, VALID_UNTIL FROM "SYS"."USERS" WHERE USER_NAME='USR_TECH_BATCH'; -- Check the password policy setting SELECT * FROM M_PASSWORD_POLICY;

A SYSTEM user locked means too many successive and unsuccessful login. The situation is not normal. Identify when and where was done the last connexion attenpts.

4. - Update user password

Updating a sap hana user password can have bad consequences. A standard user is not too much of a problem but a technical user can be more of an issue especially if it is used within shell script and procedure. No Password should be found in any program but connection key will be affected with a change of password. In order to avoid, invalid user store key, you need to keep the same password in place or regenerate a new user store key.

● For a STANDARD USER: ( force the user to change his password to a new one ) ALTER USER USR_PAULSMITH password ChangePass01 FORCE PASSWORD CHANGE;

● For a TECHNICAL USER: ( either change or re-initiate the old password ) To change the password: To be done only if passwords are securely recorded within your company so that it can be retreived when necessary, and also if the password can be immediately known and used by technical users. SQL> ALTER USER USR_TECH_BATCH password NewSecretPass01 NO FORCE_FIRST_PASSWORD_CHANGE; To re-initiate the old password: 1. Connect to SYSTEMDB or 1.1 Connect to TENANT database 2. De-activate password content policy 3. Change password 4. Re-activate password policy − Connecting to SYSTEMDB. Note : for multi tenants server, parameters are stored in "nameserver.ini" hdbsql=> \c -i 00 -d SYSTEMDB -u system -p TotalSecret01 #-- disable password policy Alter SYSTEM ALTER CONFIGURATION ('nameserver.ini','SYSTEM') set ('password policy','last_used_passwords')='0' with reconfigure #-- update password ALTER USER USR_TECH_BATCH password OLDPassword01 NO FORCE_FIRST_PASSWORD_CHANGE; #-- re activate password policy Alter SYSTEM ALTER CONFIGURATION ('nameserver.ini','SYSTEM') set ('password policy','last_used_passwords')='5' with reconfigure − Connecting to a TENANT database hdbsql=> \c -i 00 -d TenantDB -u system -p TotalSecret01 Alter SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') set ('password policy','last_used_passwords')='0' with reconfigure ALTER USER USR_TECH_BATCH password OLDPassword01 NO FORCE_FIRST_PASSWORD_CHANGE; Alter SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') set ('password policy','last_used_passwords')='5' with reconfigure ● For the SYSTEM USER: ( either change or re-initiate the old password ) The procedure and advices are the same as for technical users. Note: For all users the operation can only be done via another a User with "USER ADMIN" and "INIFILE ADMIN" privileges.

5. - Unlock user

A sap hana user will be locked or deactivated due to various circumstances. Flags are set whenever a particular event is triggered. You have to unset those flags in order to clear the user.

ALTER USER < USERNAME > RESET CONNECT ATTEMPTS; and ALTER USER < USERNAME > ACTIVATE USER NOW;

6. - Test user connexion

Keep in mind a technical user connexion might be linked to a user-store-key. In that case, if the sap hana technical user password has been updated, the user store key has got to be regenerated as well.

● Testing using standard user and password options : bobadm@alinux02:/usr/sap/bob/HDB00> hdbsql
Welcome to the SAP HANA Database interactive terminal. Type: \h for help with commands \q to quit hdbsql=> \c -i 00 -d DBP -u USR_TECH_DEV -p Mypassword01 Connected to bob@linu.ard.usa-fgh.intra:30047 hdbsql DBP=> ● Testing using user stored key : bobadm@alinux02:/usr/sap/bob/HDB00> hdbsql Welcome to the SAP HANA Database interactive terminal. Type: \h for help with commands \q to quit hdbsql=> \c -i 00 -d DBP -U USR_TECH_DEV_KEY Connected to bob@linu.ard.usa-fgh.intra:30047 hdbsql DBP=>

Sap hana SQL

Sap hana tools

- - -