Troubleshooting
Problem
When operating system configured to use third party authentication, no authority to connect to database even though proper authorities have been granted
Symptom
A specific Windows Active Directory user id does not have the authority to connect in Linux. Using other user ids of the same group succeed.
db2 "connect to sample user DB2TEST"
Enter current password for DB2TEST:
SQL30060N "DB2TEST" does not have the privilege to perform operation "CONNECT". SQLSTATE=08004
Querying system catalogs confirm DB2TEST was explicitly granted CONNECTAUTH and/or PUBLIC was granted this authority so the connection should have succeeded.
db2 "select grantee, connectauth from syscat.dbauth"
GRANTEE CONNECTAUTH
-------- -----------
DB2TEST Y
PUBLIC Y
2 record(s) selected.
db2diag.log will show:
2016-01-01-14.56.38.445502-240 I3524E510 LEVEL: Severe
PID : 29982 TID : 140736842426112 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-47 APPID: *LOCAL.db2inst1.160831185638
HOSTNAME: test.ibm.com
EDUID : 53 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, bsu security, sqlexLogPluginMessage, probe:20
DATA #1 : String with size, 42 bytes
secGetGroups failed with rc = -2146500503
2016-01-01-14.56.38.445878-240 I4035E505 LEVEL: Severe
PID : 29982 TID : 140736842426112 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-47 APPID: *LOCAL.db2inst1.160831185638
HOSTNAME: test.ibm.com
EDUID : 53 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, bsu security, sqlexLogPluginMessage, probe:20
DATA #1 : String with size, 37 bytes
osplugin_get_groups rc = -2146500503
2016-01-01-14.56.38.446171-240 E4541E604 LEVEL: Severe
PID : 29982 TID : 140736842426112 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-47 APPID: *LOCAL.db2inst1.160831185638
HOSTNAME: test.ibm.com
EDUID : 53 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, bsu security, getgroupsforuser, probe:150
MESSAGE : ADM13001E Plug-in "IBMOSgroups" received error code "-1" from the
DB2 security plug-in API "db2secGetGroupsForUser" with the error
message " ".
2016-01-01-14.56.38.446200-240 I10913688E785 LEVEL: Info
PID : 1517 TID : 140737260951328 PROC : db2bp
INSTANCE: db2inst1 NODE : 000
HOSTNAME: test.ibm.com
FUNCTION: DB2 UDB, DRDA Application Requester, sqljrReportServerErrReply, probe:20
MESSAGE : ZRC=0x8037012D=-2143878867=SQLJR_AUTERR "Authorization Error"
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -30082 sqlerrml: 2
sqlerrmc:
sqlerrp : sqljrerm
sqlerrd : (1) 0x8037012D (2) 0x0000012D (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
2016-01-01-14.56.38.446201-240 I10915816E710 LEVEL: Info
PID : 1517 TID : 140737260951328 PROC : db2bp
INSTANCE: db2inst1 NODE : 000
HOSTNAME: test.ibm.com
FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -30060 sqlerrml: 21
sqlerrmc: DB2TEST CONNECT
sqlerrp : SQLJRERM
sqlerrd : (1) 0x8037012D (2) 0x0000012D (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: 08004
Environment
DB2 is configured to use DB2AUTH=OSAUTHDB so third party non-IBM authentication configured in the operating system is used. No security plug-ins configured in DB2. For this specific example Linux is using Windows Active Directory for authentication and authorization.
Diagnosing The Problem
SQLO_BAD_GROUP
The command below can be used to translate DB2 return codes from db2diag.log. SQLO_BAD_GROUP indicates the group does not exist.
db2diag -rc -2146500503
(output truncated)
Identifer:
SQLO_BAD_GROUP
Identifer (without component):
SQLZ_RC_BADGRP
Description:
BAD GROUP
Resolving The Problem
Ensure all the operating system groups the user id DB2TEST belongs to exist, remove any non-existent groups. A DB2 trace can be collected on the DB2 server to determine which group is failing with SQLO_BAD_GROUP.
1) db2trc on -f trace.dmp
2) Recreate the SQL30060
3) db2trc off
4) db2trc flw trace.dmp trace.flw
5) db2trc fmt trace.dmp trace.fmt
6) db2trc fmt -c trace.dmp trace_drda.fmt
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21993344