Question & Answer
Standalone programs to determine if there is problem outside of DB2 when with authentication/authorization. Programs will also print group membership.
Note: Group privileges, with the exception of PUBLIC, are not checked for queries that are contained in static SQL statements or DDL statements.
The root cause is usually due to an incorrect operating system configuration outside of DB2's control. SQL30082N reason code 24 is often reported after implementing LDAP or some other form of non-default (non local) operating system authentication.
This may also result in authorization errors like SQL1092N or SQL0551N because there are problems finding the group membership for a user. Note that group membership will fail for static SQL, this is working as designed.
The programs also print out timestamps to determine if there are delays calling operating system APIs.
Perform an explicit connect to verify SQL30082 reason code 24 is returned:
db2 "connect to sample user db2inst1"
(User is prompted for password)
Implicit connects does not test authentication calls, so the following statement should not be used as a test:
db2 "connect to sample"
Errors returned from these stand-alone programs indicate there is problem outside of DB2's control. Work with your system administrator to resolve.
The Linux and AIX test programs must be run as root. Specify user which is receiving SQL30082. In examples below it is user "db2inst1" with password xxxxx
Linux (default local authentication):
|Intel/AMD64||linuxGetUserPw_Linux-x86_64.tar||1.10||October 30, 2020|
|PowerPC (Little Endian)||linuxGetUserPw_Linux-ppc64le.tar||1.10||October 30, 2020|
|zLinux||linuxGetUserPw_Linux-s390x.tar||1.10||October 30, 2020|
Since all linux systems use shadowed passwords, the "pwd=" and "Crypt" outputs will not match but the output should say "The shadow password is correct".
Sample Run from working system
$ ./linuxGetUserPw newton xxx linuxGetUserPw Version: 1.10 The passwd password is incorrect /* Ignore since this is for non-shadowed password */ Passwd: len=1, pwd=y Crypt: yyy The shadow password is correct Shadow: len=60, pwd=zzzz Crypt: zzzz
Examples of Errors
(A) Linux API crypt() is NULL. On working system it should be non-null.
It will show a non-null value like: Crypt: **98234.\4a in /etc/passwd or the shadow password section. The example below is from a system using non-default configuration because db2inst1 is not defined in shadow file.
# .linuxGetUserPw db2inst1 xxxxxxxx The passwd password is incorrect Passwd: len=1, pwd=* Crypt is NULL gid: 7312 User db2inst1 is not defined in the shadow file
(B) User account is locked or cannot be logged in
pwd=!! indicates account is locked or cannot login.
The shadow password is incorrect Shadow: len=2, pwd=!! Crypt is NULL
Linux (Transparent LDAP or DB2AUTH=OSAUTHDB):
This test program should be used when the user account is not local to Linux such as a remote LDAP server or when DB2AUTH=OSAUTHDB is used in combination with third party authentication.
Must be run as root! Specify user which is receiving SQL30082N. In example below, it is user "db2inst1" with password xxxxx
|Intel/AMD64||linuxTransLdap_Linux-x86_64.tar||1.13||March 5, 2021|
|PowerPC (Little Endian)||linuxTransLdap_Linux-ppc64le.tar||1.13||March 5, 2021|
|zLinux||linuxTransLdap_Linux-s390x.tar||1.13||March 5, 2021|
- Add -t option to display timestamps
- Optionally specify the PAM file like: # ./linuxTransLdap -u db2inst1 -p xxxxx -c db2
$ ./linuxTransLdap -u newton -p xxx linuxTransLdap Version: 1.10 linuxTransLdap Version: 9 main() start timestamp : Fri Feb 12 10:39:57 2021 Using data from getspnam_r() we got the following account status: Account has NOT expired. Password has NOT expired. Initializing PAM using PAM config file db2. pam_start successful. Attempting to authenticate user newton. PAM Conversation:: msg style: PAM_PROMPT_ECHO_OFF, i: 0, msg: "Password: " PAM Conversation:: exit rc = 0 pam_authenticate successful Starting getting groups for user newton. getgrouplist returned 2 groups. 200, 7777 Listing the groups with names: gid = 200, group name = build gid = 7777, group name = pdxdb2 main() end timestamp : Fri Feb 12 10:39:57 2021 Total Elapse time : 0.125010
Examples of errors
(A)This may be caused by incorrectly configured /etc/pam.d/db2 file. Work with the Linux system administrator to resolve.
Attempting to authenticate user db2inst1. pam_authenticate failed with the following reason: auth error Finish.
(B) errno 2 in /usr/include/*/errno.h maps to NOENT (no such file or directory). This means the user id db2inst1 was not found.
Investigate with system administrator to ensure the account exists on the remote system.
getspnam_r( db2inst1 ) failed. Result is NULL. rc = 2. errno = 2
This error also indicates the user db2inst1 was not found even though errno=0.
getspnam_r( db2inst1 ) failed. Result is NULL. rc = 0. errno = 0
(C) db2inst1 can login, but "su - db2inst1" fails.
... Attempting to authenticate user db2inst1. PAM Conversation:: msg style: PAM_PROMPT_ECHO_OFF, i: 0, msg: "Password:" PAM Conversation:: exit rc = 0 pam_authenticate failed with the following reason: pam_strerror(7) --> Authentication failure The user was not authenticated
|db2auth_new.zip||April 8, 2021|
Sample Authenticating local non-domain id db2inst1 on local machine MYCOMPUTER
C:\>db2auth db2inst1 xxxxx -l [ NetServerGetInfo 0 ms ] This machine is NOT a domain controller [ LookupAccountNameA 0 ms ] LookupAccountNameA found user db2inst1 in domain MYCOMPUTER. User SID = S-1-5-21-1948279686-2654247516-1166183285-1000 [ AuthzInitializeContextFromSid 15 ms ] AuthzInitializeContextFromSid completed. [LogonUserA 0 ms ] Authentication was successful Calling NetUserGetLocalGroups with: DCName = NULL uni_fullUserName = db2inst1\db2inst1
Sample Authenticating MYDOMAIN\db2inst1
C:\>db2auth db2inst1 xxxxx -d MYDOMAIN [ NetServerGetInfo 0 ms ] This machine is NOT a domain controller [ LookupAccountNameA 0 ms ] LookupAccountNameA found user MYDOMAIN\db2inst1 in domain MYDOMAIN. User SID = S-1-5-21-581112117-2996542980-2825243346-36578 [ AuthzInitializeContextFromSid 78 ms ] AuthzInitializeContextFromSid completed. [ LogonUserA 16 ms ] Authentication was successful [ DsGetDcNameW 0 ms ] Domain Controller name is \\MYDOMAINDC2 Calling NetUserGetLocalGroups with: DCName = \\MYDOMAINDC2 uni_fullUserName = db2inst1 [ NetUserGetLocalGroups 31 ms ] The user db2inst1 belongs to the following local groups: Users Calling NetUserGetGroups with: DCName = \\MYDOMAINDC2 uni_userid = db2inst1 [ NetUserGetGroups 0 ms ] The user db2inst1 belongs to the following global groups: Domain Users
|aixAuthTest_AIX-powerpc-1.12.tar||1.12||November 11, 2020|
Sample Run (Default Local Authentication)
$ ./aixAuthTest -FILE newton xxx aixAuthTest Version: 1.12 5821763 timestamp : Fri Feb 12 10:28:28 2021 Test will include the following: Authentication with userid=newton, password=xxx Use /etc/security/passwd and compare password hash to authenticate. Group lookup with userid=newton Group lookup using /etc/groups and the getgrent API. User newton has REGISTRY value set to "files". Start time : Fri Feb 12 10:28:28 2021 Authenticating.... No errors from loginrestrictionsx. Call getpwnam to get password hash of user: newton The password is correct Passwd: xxx Crypt: xxx No error from passwdexpiredx. loginsuccess succeeded loginsuccess Message: Last unsuccessful login: Thu Sep 3 14:13:13 EDT 2020 on Last login: Fri Feb 12 10:23:45 EST 2021 on Done authenticating. Doing group lookup...... Group lookup of user newton using getgrent. Primary group: build (200) Secondary groups: build (200) pdxdb2 (7777) Done group lookup...... End time : Fri Feb 12 10:28:28 2021 Finish.
Sample Run (Transparent LDAP or DB2AUTH=OSAUTHDB)
$ ./aixAuthTest -OSAUTH newton xxx aixAuthTest Version: 1.12 5821763 timestamp : Fri Feb 12 10:29:52 2021 Test will include the following: Authentication with userid=newton, password=xxx Call authenticatex() without getauthdb and setauthdb. Group lookup with userid=newton Call getgrset to do group lookup. User newton has REGISTRY value set to "files". Start time : Fri Feb 12 10:29:52 2021 Authenticating.... No errors from loginrestrictionsx. User authenticated. The authenticatex API returned successfully No error from passwdexpiredx. loginsuccess succeeded loginsuccess Message: Last unsuccessful login: Thu Sep 3 14:13:13 EDT 2020 on Last login: Fri Feb 12 10:28:28 EST 2021 on Done authenticating. Doing group lookup...... Group lookup of user newton using getgrset. getgrset returned 200,7777. build (200) *** getgrgid_r(7777) failed with ERANGE at a buffer size of 1024. Trying again with buffer size of 2048 *** pdxdb2 (7777) Done group lookup...... End time : Fri Feb 12 10:29:52 2021 Finish.
08 April 2021