Symptom
A technical database user is locked repeatedly (after changing the password) and thus an application cannot connect to the SAP HANA database.
When checking the traces or HANA Studio for further input, you find one of the following:
- The indexserver tracefile contains the following error:
e dbapiRequestProc RequestProcessor.cpp(00528) : SQL exception. Code: 416 Message: user is locked; try again later: lock time is 1440 minutes; user is locked until - Via HANA Studio -> Security -> Users, the locked user shows: Status: Deactivated; Reason: Too many invalid connect attempts
You want to trace who is locking the user and find out the client host or IP address (still using e.g. the previous logon-data) in case this happens again.
Environment
SAP HANA Platform
Cause
A database user may be locked for several reasons, one of them is very likely caused by the (default) password policy defined:
|
indexserver.ini > password policy > maximum_invalid_connect_attempts = 6 |
If the maximum invalid connection attempts configured are exceeded, a database user is locked. In case your application connects with a technical database (and no personalized) user, this may happen if the passwords was changed but not updated in the application.
Resolution
If a database user has been locked because of invalid connection attempts, you can trace this action using the auditing feature of SAP HANA.
In the Navigator view of HANA Studio, open the Security editor of the system to be audited (requires AUDIT_ADMIN system privilege).
Create the following audit policy:
|
Policy |
<enter a custom policy name> |
|
Policy Status |
ENABLED |
|
Audited Actions |
CONNECT |
|
Audited Action Status |
UNSUCCESSFUL |
|
Audit Level |
INFO |
As shown in the figure below (step 1):
In the System Settings for Auditing area, set the auditing status to enabled (step 2). Choose the desired audit trace target (SYSLOG or Database Table).
After clicking deploy (step 3), any action where a user is locked because of invalid connection attempts is written to the SYSLOG or database view AUDIT_LOG (note that you require the corrsponding AUDIT system privileges).
Result
In case a corresponding action where invalid username / password got entered is logged, the SYSLOG at location /var/log/messages or the AUDIT_LOG view (see the SAP HANA SQL and System Views Reference for reference, e.g.
select * from AUDIT_LOG where EVENT_STATUS = 'UNSUCCESSFUL' AND EVENT_ACTION = 'CONNECT';) shows an entry matching the pattern:
|
TIMESTAMP, HOST, PORT, SERVICE_NAME, CONNECTION_ID, CLIENT_HOST, CLIENT_IP, CLIENT_PID, CLIENT_PORT, USER_NAME, |
In particular the columns CLIENT_IP & APPLICATION_USER_NAME may help you to identify the origin of the invalid connection attempts.
Note: In case you change the SAP<SID> schema/user password of a Netweaver based system, also the relevant DEFAULT connection entries in the hdbuserstore or ABAP SSFS of the <SID>adm (Linux) or Domain\SAPService<SID> (Windows) operating system user need to be updated. Otherwise the AS ABAP workprocesses will continue to use the old password when establishing the connection.
