We are using below standard SAP integration solutions

SAP Data Intelligence Cloud,

Integration and Data Management,

SAP Data services.

Enterprise Data Management Solutions

SAP HANA Smart Data Integration(SDI)

To integrate data from diverse systems (data integration) data orchestration services… etc

We are using HANA Cloud Database Technical User for few integration scenarios

What is Technical User

A Technical User should not represent real persons.Technical User is one focused on non-functional support of a system

For example Database Technical User can be used for…

• Recurring schedules,
• Reports,
• Integration systems
• Automated data transmission
• Monitoring purposes
• ..

We were facing connectivity errors due to HANA Technical Communication User locked may be due to failed Login attempts & eventually to the locking of the user

Error Screenshot :  HANA Database User Authentication Failed due to failed Login attempts

Few common Reasons for a HANA User Lockout

• In general  Technical user locked due to expiration date
• Old or wrong password cached
• May be Real users ,developers accidentally locked Technical User due to mistype password several times
• Programs , SAP CPI/CI Iflows , scheduled jobs attempting to run multiple times to connection using the old or outdated password and locking the user but password would be changed by Admin & forgot to update in other systems.

HANA has powerful Cloud Security policies, which are used by administrators to lock out an account when unsuccessful login attempts

One option to resolve “User Locked” issue is HANA Database Admin can reset password either using HANA Database Explorer or SQL

If the user was locked/deactivated ,you may need to reset the password & provide new Password

 ALTER USER <user_name> PASSWORD “<new_password>”

If HANA Database User Locked then you need to Unlock from User Management Application as shown below ->

How to Unlock the User in HANA (Every Time)

Click Unlock Button at the top right of the User from User Management Application

However we realize that it was painful due to frequent lock of technical user , we need to  Unlock manually ,reset password repeatedly.

Then we decide to work an option to prevent locking of technical user permanently so that integration ,connectivity issues won’t happen again

In HANA Cloud , User groups support a separation of user management tasks, allowing you to manage related users together for example Admin Users Group, Technical User Group , Unlockable User Group, Real Users Group (Login via SSO)

You can read more about User Groups

In HANA Cloud ,  passwords of database users are subject to certain rules, which are defined in password policies. You can change the default password policy of the database and maintain user group-specific password policies in line with your organization’s security requirements.

There is Property in “Number of Allowed Failed Logon Attempts” in  password policies should be set to “No Limit” on the Password Policy. This will make sure the User Account is not locked irrespective of failed Login attempts.  But “No Limit” option not available in due to SAP HANA Password Policies due to security reasons i.e avoid any exploit, Security vulnerabilities

Here trick to Prevent Technical user locked is you can adjust below Password policies in User Group Settings & assign User Group to Technical User

Let’s start configuration

Procedure to avoid Technical User Lockout permanently

Via HANA Database Explorer

• Assign 0 to User Lock Time parameter

Explanation : The number of minutes for which a user is locked after the maximum number of failed logon attempts is 0 minutes . If you enter the value 0, the user is unlocked immediately. This disables the functionality of parameter Number of Allowed Failed Logon Attempts ( maximum_invalid_connect_attempts )

• Assign some maximum value to Number of Allowed Failed Logon Attempts parameter

If you set User Lock Time parameter to 0 then no need to change “Number of Allowed Failed Logon Attempts” as it is disables the functionality of parameter “Number of Allowed Failed Logon Attempts” .

However if you have different value set in User Lock Time parameter then you can play with “Number of Allowed Failed Logon Attempts” .I entered “Number of Allowed Failed Logon Attempts” value as maximum 2,147,483,647

This configuration ensures that accounts won’t be locked i.e “Configure the Account lockout threshold policy settings

This settings not recommended for High Privileged Technical User because it may lead to exploit, Security vulnerabilities. Please read below recommendations.

• Assign 0 to Number of Last Used Passwords That Cannot Be Reused parameter

When user locked for existing technical user ,Initially you set value 0 so that you can maintain ,reuse old password.,helps to avoid developers to update password in Integration tools I,e where ever this tech user consumed

After Assigning User Group &  you can maintain same Password

For Safety , you can change  “Number of Last Used Passwords That Cannot Be Reused” may be 5 or any value to not repeat same password

Screenshots

1. Open  SAP BTP and navigate to your Subaccount, and then your Space
2. Chose “SAP HANA Cloud” on the left menu (big cloud icon)
3. Identify your instance, click on “Action”, and pick “Open in SAP HANA Cockpit (To Monitor & administer”)
4. In SAP HANA cockpit, select the “Security and User Management” menu item on the top left-hand side of the screen.

You can see below screenshots

Open UserGroupManagment Application from Security Section

Create New User Group & Edit Password policies as shown below

⚠️ Enter User Lock Time  = 0 minutes as shown below

Assign Your HANA Database Technical user to User Group

Next From User Management Application ,Chose Your Technical User & Disable two properties

Disable Password Expiry Lifetime,

Disable Password Change on Next Logon

Next Use the SAP HANA Database Explorer —>  “Role Assignment” APP, “Privileges Assignment” APP , to grant only necessary privileges or recommended privileges and roles to Technical user

Congratulations! 🎉  , You have learned how to disable technical user lockout ⚠️

via HANA SQL  ⚠️

CREATE USERGROUP NoLockTechuser 
SET PARAMETER 
  'password_lock_time' = '0',
  'last_used_passwords' = '0',
  'maximum_invalid_connect_attempts' = '2147483647',
  'force_first_password_change'='False',
  'minimal_password_length' ='8',
  'password_layout' = 'A1a!'
 ENABLE PARAMETER SET 'password policy';

Note: If you set ‘password_lock_time’  parameter to 0 then no need to change ‘maximum_invalid_connect_attempts’ as it is disables the functionality of parameter “Number of Allowed Failed Logon Attempts” .
In above example i set both parameters just to demonstrate how to create User Group via SQL & update parameters

— After this Create Technical user or use any existing Technical User
— Then assign “NoLockTechuser” group to Technical User

The following example creates a new user called MyProj_Tech_USER with password Password123. Please provide any strong password for MyProj_Tech_USER instead of Password123

CREATE USER MyProj_Tech_USER PASSWORD Password123 NO FORCE_FIRST_PASSWORD_CHANGE;
ALTER USER MyProj_Tech_USER DISABLE PASSWORD LIFETIME;
ALTER USER MyProj_Tech_USER RESET CONNECT ATTEMPTS;
ALTER USER MyProj_Tech_USER ACTIVATE USER NOW;

Good To Know : If you are enjoying reading  this blog post , few additional information for your reference .

-- 1) How to know about HANA Cloud effective password policy settings.

    -- Execute below SQL Statement
       select * from M_PASSWORD_POLICY
   -- M_PASSWORD_POLICY System View Provides information about password policy parameters for database users
-- 2) How to check password policy of User

      select * from users where USER_NAME ='MYPROJ_TECH_User'

     -- To check if the maximum password lifetime is disable or not, you can check the field PASSWORD_CHANGE_TIME under system view USERS.

    -- To check if the Password lifetime enabled or not you can check the field
IS_PASSWORD_LIFETIME_CHECK_ENABLED should be false, then try below 

-- 3) How to Disable Password Expiry for a user in HANA Cloud
    -- You can disable the password lifetime for the technical users using command:
        ALTER USER <technical-user-name> DISABLE PASSWORD LIFETIME

-- However, this is recommended only for technical users only, not database users that correspond to real people, DBADMIN.System 

-- A user administrator can re-enable the password lifetime check for a user with the following SQL statement: 

        ALTER USER <user_name> ENABLE PASSWORD LIFETIME

Reference : M_PASSWORD_POLICY System View

Recommendations ⚠️ 

I will recommend above configuration should be chosen only if

• The Strong password policy setting requires i.e all users to have complex passwords.
• Rotate Password of Technical user frequently & inform actual stakeholders ,Programs who consume this technical user so that it can be updated.
• Do not disable the password lifetime check for database users that correspond to real people. only technical user accounts for the database connection of the application server should have a password with an unlimited lifetime.
• Don’t configure easy security polies for Real Users, DBADMIN, System Users or Powerful Technical Users to avoid any security vulnerabilities, Database exploit
•  Dont use DBADMIN, SYSTEM users as these users are reserved for use by BTP.They are required to manage the database.
• You should not let your DB ADMIN, SYSTEM user unattended! Everyone knows it exists. So anyone finding your instance IP, could potentially try to connect and ultimately lock it.
• HANA Team recommends to create a “copy” of the system user or DBADMIN with a distinct name so that no one can find it ,deactivate
• By default, the lock will remain for 1440 minutes(24h) unless you permanently locked it.
• HANA Technical users should have a clearly identified purpose and the minimum authorization required in SAP HANA Database
• Configure audit mechanism to alert administrators when too many failed log-ins occurs in the HANA Cloud environment.
• Create an audit policy to log activity in the INVALID_CONNECT_ATTEMPTS system view. For example, create an audit policy that logs data query and manipulation statements executed on this view.

These measures help in avoiding security attacks (DoS attack that intentionally attempts to lock accounts, Brute force password attacks)

Please read more about HANA Database User Password Policies