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 db2inst1 xxxxx The passwd password is incorrect /* Ignore since this is for non-shadowed password */ Passwd: len=1, pwd=x Crypt: xxxxx gid: 23581 Group membership : Group ID (gid) : 23581 Group Name : db2inst1 Group ID (gid) : 157 Group Name : stapsys Group ID (gid) : 158 Group Name : stapdev Group ID (gid) : 101 Group Name : dasadm1 Group ID (gid) : 23581 Group Name : db2inst1 The shadow password is correct Shadow: len=98, pwd=xxxxxxxxxxxxxxxxxxxx Crypt: yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
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.10||October 30, 2020|
|PowerPC (Little Endian)||linuxTransLdap_Linux-ppc64le.tar||1.10||October 30, 2020|
|zLinux||linuxTransLdap_Linux-s390x.tar||1.10||October 30, 2020|
- Add -t option to display timestamps
- Optionally specify the PAM file like: # ./linuxTransLdap -u db2inst1 -p xxxxx -c db2
./linuxTransLdap -u db2inst1 -p <password> linuxTransLdap Version: 5 main() start timestamp : Wed Oct 30 11:14:25 2019 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 db2inst1. 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 db2inst1. getgrouplist returned 4 groups. 23581, 157, 158, 101 Listing the groups with names: gid = 23581, group name = db2inst1 gid = 157, group name = stapsys gid = 158, group name = stapdev gid = 101, group name = dasadm1 main() end timestamp : Wed Oct 30 11:14:25 2019 Total Elapse time : 0.023312
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
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 (Transparent LDAP or DB2AUTH=OSAUTHDB)
./aixAuthTest -OSAUTH newton xxx aixAuthTest Version: 1.9 dc00345 timestamp : Fri Oct 23 20:31:28 2020 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 Oct 23 20:31:28 2020 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 Oct 23 20:31:03 EDT 2020 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 Oct 23 20:31:28 2020 Finish.
11 November 2020