IBM Support

Authentication/authorization return SQL30082N rc=24, SQL1092N, SQL0551N

Question & Answer


Question

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.

Cause

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.

Answer


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):

Platform Download Version Release Date
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

Platform Download Version Release Date
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

Sample Run

Notes:

  • 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 

Windows

db2auth.zip


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

AIX

Download Version Release Date
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.

Related Information

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"Security \/ Plug-Ins - IBM Suplied\/Default","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;10.1;10.5;11.1;11.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
11 November 2020

UID

swg22014399