IBM Support

Authentication and permission problems when using DB2 UDB and Active Directory Service

Question & Answer


Question

In certain environments where DB2® UDB is installed on Windows®, you can encounter the following errors: SQL1060N: The specified authorization ID does not have the CONNECT privilege; SQLSTATE=08004 or SQL0551N: The specified authorization ID does not have the privilege to perform operation SELECT on the particular object; SQLSTATE=42501 or SQL1092N "" does not have the authority to perform the requested command or operation.

Cause

This problems can occur if you have installed DB2 Universal Database™ (DB2 UDB) on Windows 2003 or Windows XP and the following conditions are true:

  • the machine is a member of a Windows 2003 domain
  • the instance runs as a local user
  • you are using Global groups.

When DB2 UDB tries to obtain a list of the groups that a user belongs to, it makes a Windows API call to NetUserGetLocalGroups. In the scenario described above, this call fails with a return code indicating permission denied.

The Microsoft® MSDN library shows that only authenticated users can issue the NetUserGetLocalGroups call. If domain global groups are involved, authenticated users are users that are authenticated to the domain. Since localsystem and local users are not authenticated to the domain, the API call will fail with an access denied message.

Answer

The problem can be resolved using one of the following two methods.

Method 1: Make this a domain installation and use a domain user to run the instance. This involves changing the instance's service to run as a domain user rather than a local user. Make sure that the newly assigned domain user has the necessary rights needed for the instance service (see the link under Related Information for details).


Method 2: Switch the Microsoft Active Directory Service to Pre-Windows 2000 compatibility mode. This involves adding "Everyone" to the built-in "Pre-Windows 2000 compatibility access" group.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Security \/ Plug-Ins - IBM Suplied\/Default","Platform":[{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;9.1;8","Edition":"Enterprise;Personal;Workgroup;DB2 UDB Express","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21222934