SQL30082N Security processing failed with reason code "24" when selecting from an administrative views using IBM LDAP security plug-ins, IBMLDAPgroups and IBMLDAPauthserver.
When using IBM LDAP security plug-ins, IBMLDAPgroups and IBMLDAPauthserver, you will receive SQL30082N with reason code "24" when executing queries on administrative views even though the connection to the database was successful. For example, consider the following configuration
Client Userid-Password Plugin (CLNT_PW_PLUGIN) =
Client Kerberos Plugin (CLNT_KRB_PLUGIN) =
Group Plugin (GROUP_PLUGIN) = IBMLDAPgroup
GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED
Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =
Server Userid-Password Plugin (SRVCON_PW_PLUGIN) = IBMLDAPauthserver
Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
Database manager authentication (AUTHENTICATION) = CLIENT
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Using a user id, adm_alvleung, to connect to the sample database, will be successful.
$> db2 connect to sample user adm_alvleung using XXXXXXXX
Database Connection Information
Database server = DB2/LINUXX8664 9.1.3
SQL authorization ID = ADM_ALVL...
Local database alias = SAMPLE
However, when executing the select statement against a system view, you will receive SQL30082N error.
$> db2 "select * from sysibmadm.snaplock"
SNAPSHOT_TIMESTAMP AGENT_ID TAB_FILE_ID LOCK_OBJECT_TYPE
-------------------- ----------------- --------------
SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR
PASSWORD INVALID"). SQLSTATE=08001
The above error message will generate the following entry in the db2diag.log
2008-08-01-08.42.36.963443+120 I36734E357 LEVEL: Warningi
PID : 22314 TID : 46912637033008
FUNCTION: DB2 Common, Security, Users and Groups, secLogMessage, probe:20
DATA #1 : String, 137 bytes
db2ldapGetUserDN: searching (retried=0 scope=2) with base=dc=int,dc=msci,dc=com
Since the SELECT statement is running against a system view, sysibmadm.snaplock, the error is caused by the query triggering a call to an internal stored procedure. In turn, the stored procedure will issue an ATTACH to the DB2 instance. Since this ATTACH is handled by the db2fmp process, it is considered a "NULL" ATTACH, which means it does not require a password and therefore uses CLIENT authentication. Given the configuration above, you can see the database manager configuration parameter, CLNT_PW_PLUGIN, is not set. This means client authentication will use the default OS plug-in (IBMOSauthclient) and not LDAP plug-in. As a result, the OS plugin validates the userid to be no longer than 8 characters which leads to SQL30082N.
Keep in mind, the database CONNECT command actually goes through the LDAP authentication plug-in which has a userid limit of 255 characters.
This behavior is not exhibited if the query executed was against a regular database table.
Resolving the problem
In this case, there are two ways to resolve the error :
- set the CLNT_PW_PLUGIN parameter in the database manager configuration file to IBMLDAPauthclient by issuing the following command. Note: The database server must be restarted for the new value to take effect.
db2 update dbm cfg using CLNT_PW_PLUGIN IBMLDAPauthclient
- If you do not wish to use the IBMLDAPauthclient plug-in, the userid must be limited to eight (8) characters long.