Troubleshooting Flow for DB2 Security authentication issues on Windows platforms

Resolving The Problem

• Have there been any recent changes to the instance or database configuration file?
  
  These changes constitute, changing the setting to the AUTHENTICATION and SYSADM_GROUP parameters in the database manager configuration.
  
• Check if the DB2 Extended Windows Security feature is enabled, To accomplish this use the following command:
  
  C:\>db2set -all | findstr DB2_EXTSECURITY
  
  If the output shows a value of Yes, then it is enabled.
  [g] DB2_EXTSECURITY=YES
  
  This feature is enabled by default for all DB2 database products on Windows operating systems except IBM Data Server Runtime Client and DB2 Drivers. IBM Data Server Runtime Client and DB2 Drivers do not support extended security on Windows platforms. When this feature is enabled, DB2 creates two local groups DB2ADMNS and DB2USERS. Members of these groups must acquire specific user privileges outlined in the Extended Windows security using DB2ADMNS and DB2USERS groups page in the DB2 V9.7 information center. Users missing these privileges will receive errors when executing DB2 commands.
  
  
• Have there been any recent changes to your operating system?
  
  Changes to the operating system, include new service packs, installation of a new security mechanism such as Kerberos or the Windows Active Directory environment. Evaluate all changes as possible conflicts with the authentication issue you are encountering.
  
  
• Are you using a customized security plug-in?
  
  Starting with DB2 V8.2, authentication is done using security plug-ins. Security plug-ins gives control to customize the security mechanisms to meet your individual needs, instead of relying on a standard facility like the operating system. With plug-ins you can change this behavior so that implicit connections are not allowed. For more information about DB2 Security plug-ins please read Understand DB2 security plug-ins.
  
  
• Are you using a local or a Domain user ID when the problem occurs?
  
  When authenticating a user id, DB2 uses LookupAccountName() Windows API to identify a user. The API uses the following search order to locate the user id:
  o Local machine
  o Domain Controller
  o Trusted Domain
  Verify that your user is in the correct location.
  
  
• Is the DB2 DB2_GRP_LOOKUP set?
  
  DB2 utilizes the registry variable only to enumerate groups the user id belongs to. If the user id is local, DB2 will not consult the registry variable. However if the account is a Domain ID, then DB2 will consult DB2_GRP_LOOKUP registry variable.
  If the registry variable is not specified with a value, DB2 will try to find the primary domain controller for the domain to communicate with to enumerate the groups.
  If DB2_GRP_LOOKUP=LOCAL, then DB2 will look on the local machine for groups in which the account is a member of. DB2 fully qualify the user with domain name. Hence its important to create local groups with fully qualified domain user ids.
  If DB2_GRP_LOOKUP=DOMAIN, then DB2 will utilize the NetGetAnyDCName() API to go to the nearest Domain controller and perform the search on that machine.
  
  
• Does the user account belong to the local Administrator and DB2ADMNS groups?
  
  In order to perform DBA tasks on the database and the database server, such as creating backups, adding or dropping table spaces, etc, the user account must be a member of the local Administrator as well as the DB2ADMNS group. If SYSADM_GROUP database manager configuration parameter is set to a specific group name, that user account must be a member of that group. If the parameter is not set, it defaults to the DB2ADMNS.
  
  
• Do you have a local group name called db2admin?
  
  The db2admin is a special user id and has a special meaning in DB2 configuration. Having a group name with the same name will cause confusion in DB2 resulting in SQL1092N error. Use the Windows command net localgroup to list the groups.
  
  
• What is the user ID the DB2 service is running under?
  
  Normally, the DB2 Service is based on the instance name. If you know the DB2 service name, then you can use the sc qc <DB2_service_name> command to display this information. In the example below, SERVICE_START_NAME is the service account/ID.
  
  C:\> sc qc db2
  
  [SC] GetServiceConfig SUCCESS