Analyzing data for the Db2 security model on Windows platforms

Resolving The Problem

Trouble Shooting Guide

• 1. Learning More
• 2. Troubleshooting
• 3. Collecting Data
• 4. Analyzing Data- selected tab,

 
Refer to the Collecting Data document for DB2 security model on Windows platforms, which can be used to gather necessary diagnostic data. The following information will help you analyze the information collected.

SQL1092N error message

One of the most common causes of SQL1092N error is having a local group name db2admin created on the system. The db2admin name has a special meaning in the DB2 configuration and can cause DB2 can cause problems. Listed below, is the db2diag.log message logged when someone tried to list table spaces from the Control Center:

2009-12-16-14.14.52.167000-300 E138050031H599     LEVEL: Warning
PID     : 7708                 TID  : 6880        PROC : db2fmp.exe
INSTANCE: DB2                  NODE : 000
EDUID   : 6880
FUNCTION: DB2 UDB, routine_infrastructure, sqlerFencedTruncateTokensUserSqlState, probe:100
DATA #1 : signed integer, 4 bytes
-443
DATA #2 : String with size, 5 bytes
38553
DATA #3 : String with size, 23 bytes
SYSPROC.GET_DBSIZE_INFO
DATA #4 : String with size, 15 bytes
GET_DBSIZE_INFO
DATA #5 : String with size, 69 bytes
SQL1092  Reason code or token: DB2ADMIN 


Use the Windows net localgroup command to list the different groups defined locally on your system. You will be able to recognize that the db2admin local group must be changed.
In this case renaming the db2admin local group will resolve the problem.
For example:

C:\> net localgroup

Aliases for \\SERVERHostName
-----------------------------------------------------------------------
*Administrators
*Backup Operators
*DB2 ADMNS
*db2admin
*DB2ADMNS
*DB2USERS
*Distributed COM Users
*Guests
*HelpServicesGroup
*Network Configuration Operators
*Performance Log Users
*Performance Monitor Users
*Power Users
*Print Operators
*Remote Desktop Users
*Replicator
*TelnetClients
*Users
The command completed successfully.

DB2 Authentication issue

Another common issue with DB2 authentication running on Windows platforms is the use of local user id vs a Domain id. If you are using an a local account id to administrate DB2 database server, you need to make sure the user id is a member of the local Administrators group. If the DB2 Windows Extended Security feature is enabled (default setting during DB2 installation), the user id must be a member of DB2ADMNS group. If the registry variable DB2_EXTSECURITY=YES, the DB2 Windows Extended Security feature is enabled.
Also check if the database manager configuration parameter, SYSADM_GROUP is set to a specific group name and the user id is a member of that group. To view the parameter, use the db2 get dbm cfg command as in the example below.
In the following example, members of the MYDB2ADMNS local group can only administrate the DB2 database server.

C:\> db2 get dbm cfg | findstr /I sysadm

SYSADM group name        (SYSADM_GROUP) = MYDB2ADMNS

DB2 Service issue

It is important to know the account which started the DB2 service as viewed from the Windows services panel. Using the sc qc <db2_servicename> command, you can determine the account the service is running under. In the example below, the SERVICE_START_NAME field contains the service account ID.

C:\>sc qc db2inst1
[SC] GetServiceConfig SUCCESS

SERVICE_NAME: db2inst1
       TYPE               : 10  WIN32_OWN_PROCESS
       START_TYPE         : 3   DEMAND_START
       ERROR_CONTROL      : 1   NORMAL
       BINARY_PATH_NAME   : C:\PROGRA~1\IBM\SQLLIB\bin\db2syscs.exe
       LOAD_ORDER_GROUP   :
       TAG                : 0
       DISPLAY_NAME       : DB2 - DB2INST1
       DEPENDENCIES       : LanmanServer
                          : +NetBIOSGroup
       SERVICE_START_NAME : .\rodeh

In this case, the defined user id "rodeh" must acquire administrative rights on the Windows server in order to start the service.

If the user id is a Domain account, and will be used to administrate DB2, the Domain id must be a member of the local Windows Administrators group, or can be a member of a Domain group that is a member of the local Windows Administrators group.

DB2 Traces

In dealing with DB2 issues, DB2 traces are helpful in finding the root cause of issues encountered on the DB2 database server. DB2 security issues are no different. Since DB2 depends on operating system APIs to validate the user ID and password, there are specific functions or components within DB2 that utilizes those kernel subroutines such as LookupAccountName() Windows API. One of these components is referred to as the Operating System Service (OSS). This component is captured by the DB2 trace utility. In cause of DB2 security, DB2 uses a function called sqlo_validate_pwd to validate a user ID and password. Below is an example from a DB2 formatted trace file :


136459    entry DB2 UDB oper system services sqlo_validate_pwd cei (1.3.15.651.2)
    pid 4536 tid 2728 cpid 2368 node 0
    eduid 2728 eduname db2agent
    bytes 16

    Data1     (PD_TYPE_STRING,8) String:
    cifowner

136460    entry DB2 UDB oper system services sqloTalkToSecurityServer fnc (1.3.15.1197.0)
    pid 4536 tid 2728 cpid 2368 node 0
    eduid 2728 eduname db2agent

136461    exit DB2 UDB oper system services sqloTalkToSecurityServer fnc (2.3.15.1197.0)
    pid 4536 tid 2728 cpid 2368 node 0
    rc = 0x0000052E = 1326

136462    error DB2 UDB oper system services sqlo_validate_pwd cei (4.3.15.651.2.20)
    pid 4536 tid 2728 cpid 2368 node 0 probe 20
    Error ZRC = 0x00000000 = 0 = PSM_OK
    bytes 12

    Data1     (PD_TYPE_DEFAULT,4) Hexdump:
    2E05 0000                                  ....


136463    exit DB2 UDB oper system services sqlo_validate_pwd cei (2.3.15.651.2)
    pid 4536 tid 2728 cpid 2368 node 0
    rc = 0x800F0065 = -2146500507 = SQLO_BAD_PSW


From trace point 136459, the DB2 function is trying to validate the password for user ID cifowner DB2 dispatched thread id 2728 to handle the task by calling sqloTalkToSecurityServer function.The function in received error 1326 from the OSS component. This is a Windows error which means :

C:\> net helpmsg 1326

Logon failure: unknown user name or bad password.

As a result DB2 returned reason code rc = 0x800F0065 = SQLO_BAD_PSW. You can get a description of the error using the db2diag -rc command.


C:\Program Files\IBM\SQLLIB\BIN>db2diag -rc 0x800F0065

Input ZRC string '0x800F0065' parsed as 0x800F0065 (-2146500507).

ZRC value to map: 0x800F0065 (-2146500507)
        V7 Equivalent ZRC value: 0xFFFF8665 (-31131)

ZRC class :
        SQL Error, User Error,... (Class Index: 0)
Component:
        SQLO ; oper system services (Component Index: 15)
Reason Code:
        101 (0x0065)

Identifer:
        SQLO_BAD_PSW
Identifer (without component):
        SQLZ_RC_BADPSW

Description:
        Password does not belong with specified user id

Associated information:
        Sqlcode -30082
SQL30082N  Attempt to establish connection failed with security reason "" ("").

        Number of sqlca tokens : 2
        Diaglog message number: 8111