StarQuest Technical Documents

SQDR Plus SQL Server User Authorities

Last Update:13 February 2020
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL039

Abstract

The Quick Start Guide to Using SQDR Plus v4 (see Tier 1 SQL Server host computer requirements) and its Worksheet (see Tier 1 Source Database (SQL Server) table) describe the SQL Server userID's that are used by SQDR Plus.

The default user authority requirements are designed for quick and trouble-free installation. However, in situations where data center security policies restrict the use of privileged user accounts, this document describes the roles and tasks performed by each of the user ID's and the grants necessary to enable non-privileged userID's to perform those tasks.

This document primarily covers the use of SQL Server Change Tracking (CT).To use SQL Server Change Data Capture (CDC), which is available only in certain editions of SQL Server (see SQDR Plus System Requirements), additional authority as described below is required.

For information about the (minimal) performance and storage impact of Change Tracking on SQL Server, see the SQDR Plus FAQ.

The examples below illustrate the use of SQL Server authentication, but similar considerations apply when using Integrated Security (Windows authentication). Refer to Integrated Security issues below for additional considerations.

Solution

• Installing User - not applicable
• Agent User (Schema owner) - suggested name SQDR
• Agent User access to source tables
• Publishing
• Replication User - suggested name SQDRUSER
• Integrated Security (Windows Authentication) Issues

User Accounts used by SQDR and SQDR Plus

Installing User - not applicable

Unlike some other supported DBMS platforms, SQDR Plus for SQL Server does not have a concept of the Installing User. The Create Agent Wizard for SQL Server requests the credentials of an existing SQL Server user (the Agent User, described in the next section), rather than the credentials of an administrative user with sufficient authorities to create the Agent User.

Agent User (Schema owner) - suggested name SQDR

During creation of an Agent, you will be prompted for the credentials of an existing SQL Server user.

For Change Tracking, the Quick Start Guide recommends supplying a user with sufficient authority to

• Create tables and stored procedures in a new schema (e.g. SQDR).
• Alter the database to use Change Tracking.
• Alter all tables to be replicated to use Change Tracking.
• SELECT on all tables to be replicated.

For Change Data Capture (CDC), the user must have authority to run the following procedures in the source database:

• sys.sp_cdc_enable_db: Requires membership in the sysadmin fixed server role.
• sys.sp_cdc_enable_table: Requires membership in the db_owner fixed database role.

To use a user without administrative privileges for Change Tracking, do the following as an administrative user:

1. Create the schema (e.g. SQDR)
   

use database mydb
CREATE SCHEMA SQDR

2. At the instance level, create the user (SQL Server with login) and uncheck Enforce password expiration and User must change password at next login.
3. At the database level, add the user, define its default schema, and select the schema under Owned Schemas.
4. Grant CREATE TABLE and CREATE PROCEDURE to the new user:

grant CREATE TABLE to SQDR
grant CREATE PROCEDURE to SQDR

5. Alter the database to enable Change Tracking:

In SQL Server Studio, right-click on the database and select:
Options - Allow Snapshot Isolation
Change Tracking - True; retention 2 days; auto cleanup

or use the following SQL:

ALTER DATABASE mydb SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
ALTER DATABASE mydb SET ALLOW_SNAPSHOT_ISOLATION ON

To view the current state of change tracking and Snapshot Isolation for the database:

SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('mydb')
select snapshot_isolation_state from sys.databases where database_id=DB_ID('mydb')

6. Create the Agent in SQDR Manager, supplying the credentials of the Agent user (SQDR). You will see the following in the history:

CREATE SCHEMA "SQDR" Completed
CHANGE_TRACKING ON Verified
ALLOW_SNAPSHOT_ISOLATION ON Verified

7. Start the Agent and verify that it starts without errors.

Agent User access to source tables (Change Tracking)

For all tables to be replicated or subscribed to, the Agent User needs SELECT and VIEW CHANGE TRACKING authority, and either ALTER TABLE (to enable Change Tracking on the table), or the table should already be altered for Change Tracking.

As the owner of the table or as an administrative user:

GRANT SELECT ON myschema.mytab to SQDR
ALTER TABLE myschema.mytab ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)
grant VIEW CHANGE TRACKING on myschema.mytab to SQDR

Note that the table must have a primary key defined in order to enable Change Tracking.

To create an SQL script to issue GRANT and ALTER TABLE on multiple tables in a schema, you can use statements like these:

select 'grant select on myschema.'+_name+ ' to SQDR ;' from sys.tables t where SCHEMA_NAME(t.schema_id)='myschema'

select 'ALTER TABLE myschema.'+name+ ' ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON) ;' from sys.tables t SCHEMA_NAME(t.schema_id)='myschema';

select 'grant VIEW CHANGE TRACKING on myschema.'+name+ ' to SQDR ;' from sys.tables t where SCHEMA_NAME(t.schema_id)='myschema'

Publishing

Publishing (described in the Publishing topic of the SQDR Control Center Help) allows you to specify which database tables to make available to SQDR clients for replication. It is also useful to obtain a "sharp" copy of the baseline when working with active large source tables where many changes may have occurred during the course of the baseline.

To publish tables in SQDR Manager, select the agent, select the Publications panel, and select the Plus sign from the top of the window. No additional authorities are required If the Agent user has been granted the authorities to the tables as specified above.

Replication User - suggested name SQDRUSER

In addition to the userID used by SQDR Plus, you will be asked to provide credentials when creating a source in Data Replicator Manager. This userID may already exist and does not need to be dedicated to use by SQDR. This userID is used to examine schemas and table structures when creating subscriptions, and is used for performing the initial baseline replication. It requires SELECT on the tables to replicated.

Integrated Security (Windows Authentication) Issues

When using Integrated security for the Agent User, additional steps are required to grant sufficient authority to the SQDR Plus services for the local DB2 LUW system. If you do not do this, creation of the Agent will fail with an error indicating insufficient privileges for creating a DB2 database.

You can either

• Add the machine account (e.g. mydomain\sqdrmachine$) to SQL Server and grant it appropriate authorites
• Or following the steps below to use a domain account:

1. Create a domain user that adheres to DB2 naming conventions. Note that IDs cannot begin with IBM, SQL or SYS, or be USERS, ADMINS, GUESTS, PUBLIC, LOCAL or any SQL reserved word.
2. Create a local Windows group DB2Admin (or a similar name) and add the domain user to it. Note that this is a different usage of Windows group by Db2 than Operating system security.
3. Issue the following statements from a DB2 command window:
   db2set DB2_GRP_LOOKUP=local
   db2 update dbm cfg using SYSADM_GROUP DB2Admin
   
4. Restart DB2 (db2stop or db2stop force, followed by db2start).
5. To verify that the user has sufficient authority to create a database, start a db2cmd window as the domain user by holding down the shift key when starting a command window and selecting Run as a different user, and enter
   DB2 CREATE DB TEMP
   DB2 DROP DB TEMP
   
6. Make sure the domain user has full permission on the \ProgramData\StarQuest\sqdrplus directory structure.
7. In the Services control panel, change the SQDR Plus Jetty and SQDR Plus Launch Agent services to run as the domain user.
8. In addition, the DB2 services should be run as a domain user e.g. mydomain\db2admin, so that DB2 is able to recognize the SQDR Plus domain user.