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

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

Sample Run

Notes:

  • 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 

Windows

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

AIX

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

[{"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:
08 April 2021

UID

swg22014399