StarQuest Technical Documents

SQDR Plus: Tier 2 UserID Considerations

Last Update: 27 November 2021
Product: SQDR & SQDR Plus
Version: 5.22 and later
Article ID: SQV00PL055

Abstract

The Quick Start Guide to Using SQDR Plus recommends using the UserID names db2admin and sqdr when configuring the SQDR Plus (tier 2) Platform and its agents.

On Windows, these UserID's can be either local or domain, and passwords must conform to the password complexity requirements of the Windows system. They are used for the following functions:

• db2admin - the userID that the Db2 LUW services run under. This is created by the Db2 installer if it does not exist. it may also be used for Db2 administration tasks.
• sqdr - used for communication from the SQDR client platform and is the owner of the control tables and stored procedures stored in the SQDR Plus staging database (e.g. SQDRP0). We recommend creating this user before installing Db2 (so that you are aware of password complexity requirements). You will prompted for this userID and its password on the second panel of the Create Agent Wizard.

If you require the use of names other than db2admin and sqdr, see the following considerations.

Db2 Service User

Running the Db2 services as a user named something other than db2admin in most cases has minimal impact. However, you may encounter privilege issues if you have a need to perform administrative tasks using a userID named other than db2admin e.g.

SQL0552N "DB2ADMINJ" does not have the privilege to perform operation "GRANT". SQLSTATE=42502

See Db2 Administrative Authorities below for methods of dealing with the lack of privileges.

SQDR User

To use a user named something other than sqdr, perform the following steps when creating a new agent:

• You must be using SQDR Plus 5.22 or later.
• Specify the userID and its password on the second panel of the Create Agent Wizard.
• After creating the agent, but before starting it, examine the agent configuration to identify the name of the newly created database (e.g. SQDRP0), connect to the database as a user with sufficient privileges (e.g. db2admin), and perform the following grant:

DB2 GRANT DBADM ON DATABASE TO NEWUSER

• Start the Agent.

This grant needs to be done only once per staging database, so you can create additional agents using the existing staging database without any special actions.

If you are using a user other than sqdr, you may see an authority error (SQLCODE 551/SQLSTATE 42501) related to updating a function or stored procedure (e.g. SQDR.GNV) in the Diangostics log when starting the agent for the first time after an update. If this occurs, do the following in the SQDRPn database. This example assumes that the error refers to schema SQDR; if the error refers to a different schema (e.g. SQDR0), adjust the SQL statements. Also adjust the WHERE clause to indicate whatever version of SQDR Plus is currently installed.

drop function SQDR.GNV
update sqdr.sq_properties set propertyvalue='05.24.20210826' where propertyvalue='05.24.20210827'

 

Db2 Administrative Authorities

The easiest way to perform the grant to the new SQDR user is to connect to the staging database as the user db2admin. This name is already present in the Db2 backup templates that are restored as part of creating an agent. Even if a different name is being used to run the Db2 services, we recommend creating a local or domain user by the name db2admin (even just temporarily) and performing any needed grants. You may also choose to grant SECADM & DBADM authorities to the actual Db2 service user or another ID if the use of db2admin is temporary.

 

Other methods of performing the grant to the new SQDR user are more complicated and involve the Db2 registry setting DB2_RESTORE_GRANT_ADMIN_AUTHORITIES.

Before creating the agent, do the following:

db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
db2stop
db2start

Setting DB2_RESTORE_GRANT_ADMIN_AUTHORITIES grants SECADM, DBADM, DATAACCESS, and ACCESSCTRL authorities to the user that performs the RESTORE DATABASE operation.

In the case of SQDR Plus, this is the user that the SQDR Plus Launch Agent service is running as. By default, this is Local System Account, so the user SYSTEM will have admin privileges in the newly restored database. You can verify that with:

db2 "select substr(grantee,1,30), securityadmauth from syscat.dbauth"

1 SECURITYADMAUTH
------------------------------ ---------------
SYSTEM Y
PUBLIC N
DB2ADMIN Y
SQDR N

However, it is not easy to connect to Db2 as user SYSTEM without writing an application program, since SYSTEM does not have an associated password. A workaround is to use the psexec command of Microsoft Sysinternals PsTools Suite. Start a db2cmd window using psexec64 -i -s db2cmd and issue DB2 GRANT DBADM TO NEWUSER.

Another workaround is to run the SQDR Plus Launch Agent service as a user other than Local System Account (even if only temporarily). For instance, you might use the Db2 service user. In this case, the RESTORE will be performed by the Db2 service user rather than SYSTEM, and the necessary admin privileges will be granted to the Db2 service user. The Db2 service user can then issue DB2 GRANT DBADM ON DATABASE TO NEWUSER.

If your goal is to create an Agent for a SQL Server source using Integrated Security, then the SQDR Plus Launch Agent service may already be running as a user other than Local System Account, in which case that user will be granted privileges in the newly created database and can perform the DB2 GRANT DBADM ON DATABASE TO NEWUSER.