topic Re: non-sysadmin role for sql server source endpoint - needs sysadmin roles for logins still? in Qlik Replicate

non-sysadmin role for sql server source endpoint - needs sysadmin roles for logins still?

Rak — Thu, 13 Jun 2024 22:55:11 GMT

https://help.qlik.com/en-US/replicate/May2022/Content/Global_Common/Content/SharedReplicateHDD/SQLServer-Source/nonsys_admin.htm

Upon reviewing the steps outlined in the document, Steps #9 and #14 still require for the sysadmin server roles which is a concern for our infosec team. Is there a workaround that does not involve referencing or granting sysadmin server roles while setting up non-sysadmin role for qlik to replicate data?

Add the login to the sysadmin server role as follows:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_dump_dblog_login];

Add the login to the sysadmin server role as follows:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_position_1st_timestamp_login];

Re: non-sysadmin role for sql server source endpoint - needs sysadmin roles for logins still?

sureshkumar — Fri, 14 Jun 2024 03:07:19 GMT

Hello @Rak
As i checked internally "In order to replicate the data, we have to run a few features that SQL Server requires the use of the sysadmin role."

Regards,
Suresh

Re: non-sysadmin role for sql server source endpoint - needs sysadmin roles for logins still?

SushilKumar — Fri, 14 Jun 2024 11:35:04 GMT

Hello @Rak

AS mentioned in the Qlik help document as SYSADMIN role have all the privileges to work with Qlik replicate, if you Choose a non-sysadmin user then you must privileges require reading transaction logs, backups logs and MSDB for backup information and Metadata collection.

request you to look in section 16 for additional privileges required for non-SYSADMIN USER.

Regards,

Sushil Kumar

Re: non-sysadmin role for sql server source endpoint - needs sysadmin roles for logins still?

Heinvandenheuvel — Sun, 16 Jun 2024 06:11:47 GMT

@SushilKumar >> if you Choose a non-sysadmin user then you must privileges require reading transaction logs,

I do not think that is correct information. Replicate may need to use fn_dump_dblog. That function can be used to make sql-server read any file on the server regardless of the protection. Thus sql server protects the system by requiring sys_admin role whether you have the privilege to read the transaction log itself or not.

The error message if you try without is extremely clear about that:

User does not have permission to query the virtual table, DBLog. 
Only members of the sysadmin fixed server role and the db_owner fixed database role have this permission

See for example: https://dba.stackexchange.com/questions/217584/grant-select-on-function-fn-dump-dblog-in-sql-server-without-granting-sysadmin

Hein

Re: non-sysadmin role for sql server source endpoint - needs sysadmin roles for logins still?

Rak — Tue, 18 Jun 2024 15:26:27 GMT

@SushilKumar can you point exactly regarding the section 16?