3795928 - How to identify why a SAP Datasphere Database User gets locked?

• A database user generated in SAP Datasphere gets locked shortly after resetting password.
• The requirement is to identify which logs/tools in SAP Datasphere should be used to determine who and why the user was locked and to locate the source of failed authentication attempts.
• DB user is getting automatically locked. How to troubleshoot this as what would be cause and how we can avoid this?

• HANA ODBC Connection Error

  SQLSTATE:    HY000
  NATIVE ERROR:    10
  MESSAGE TEXT:    [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;10 authentication failed: Detailed info for this error can be found with correlation ID 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'

• SAP Datasphere

A database user may be locked for several reasons, the default password policy defined in SAP Datasphere's HANA Cloud: indexserver.ini > password policy > maximum_invalid_connect_attempts = 6.
If the maximum invalid connection attempts configured are exceeded, the database user is locked.
In case your application connects via ODBC/JDBC with a database user generated in SAP Datasphere, this may happen if the password was changed but not updated in the application.

You can run the SQL statements below by using a Database Analysis User.
In the examples, the Space technical name is SPACE and DB User is MY_DB_USER, adjust according your scenario. 

• --check user password policy, including the maximum_invalid_connect_attempts
  SELECT * from "PUBLIC"."M_EFFECTIVE_PASSWORD_POLICY" where USER_NAME = 'SPACE#MY_DB_USER';
  
  
• --check the current status of the user
  select * from "SYS"."USERS" where USER_NAME = 'SPACE#MY_DB_USER';

• --check the user locked status and how many invalid attempts so far
  SELECT USERS.USER_NAME AS "username", USERS.INVALID_CONNECT_ATTEMPTS,
  CASE
      WHEN USERS.INVALID_CONNECT_ATTEMPTS >= TO_INT(POLICY.VALUE) THEN true
      WHEN TO_BOOLEAN(USERS.USER_DEACTIVATED) = true THEN true
      ELSE false
  END as "isLocked" FROM SYS.USERS AS USERS
  INNER JOIN SYS.M_EFFECTIVE_PASSWORD_POLICY AS POLICY ON POLICY.USER_NAME = USERS.USER_NAME AND POLICY.PROPERTY = 'maximum_invalid_connect_attempts'
  WHERE USERS.USER_NAME = 'SPACE#MY_DB_USER';

The root cause is likely due ODBC/JDBC clients using outdated Datasphere's HANA Cloud (HC) DB credentials, after resetting the password via Datasphere's UI.
After a password reset, the clients continuously tried to connect using the old/wrong password. When maximum_invalid_connect_attempts reaches 6 attempts, the user gets locked.

Note!
SAP is unable to control external attempts to connect to your instance.
To address this, we recommend reviewing all potential sources of incorrect login attempts, such as automated processes or third-party integrations, and ensuring that the correct credentials are being used:

1. Stop all ODBC/JDBC client connections using Datasphere's DB credentials;
2. Reset the password in Datasphere;
3. Update the password in all your clients;
4. Finally enable/start the connections again.

By using the template below, SAP Datasphere support can get you Client IP and Application name which attempts to authenticate, not more.
There is no way to get an exact job which is using wrong password.

"Hello Datasphere Support, 

According to KBA 3795928, provide me detailed information about failed authentication for the following context:
- Datasphere URL
- Database User (e.g. SPACE#DB_USER)
- Time window of interest

Regards,

In Datasphere's Security -> Activities you can see detailed information about when DB user was created (1), password reset (2), and privileges updated (3):

Some highlights:

• It is not supported to only unlock the user without changing the password.
• The SELECT privilege to "SYS"."AUTHENTICATION_ERROR_DETAILS" is intentionally not granted to Database Analysis User of Datasphere. 
• The SELECT privilege to AUDIT_LOG is granted, but audit policy 'DWC_SUPPORT_FAILED_CONNECTS_AUDIT' is by default not enabled.

• KBA 3479756 - How-To Unlock Database User within Datasphere Space

datasphere, database user lock, user locked, account locked, audit log, activity log, authentication failed, odbc error, hdbodbc, general error 10, cpi-ds, ci-ds, integration user, login attempts, correlation id, SELECT * FROM SYS.AUTHENTICATION_ERROR_DETAILS; , KBA , DS-SM , Space Management , How To