'Unable to RESUME AWS DMS ongoing replication task with SQL server as source
I am trying to run an AWS DMS ongoing replication task with Microsoft SQL server as source. I have followed the below official documentation to configure SQL server as a source for DMS:
Ongoing replication has been configured without sysadmin role and all the below permissions have been granted:
USE master;
GRANT VIEW SERVER STATE TO dms_user;
GRANT SELECT ON FN_DBLOG TO dmstest;
GRANT SELECT ON FN_DUMP_DBLOG TO dmstest;
GRANT VIEW SERVER STATE TO dmstest;
use msdb;
GRANT EXECUTE ON MSDB.DBO.SP_STOP_JOB TO dmstest;
GRANT EXECUTE ON MSDB.DBO.SP_START_JOB TO dmstest;
GRANT SELECT ON MSDB.DBO.BACKUPSET TO dmstest;
GRANT SELECT ON MSDB.DBO.BACKUPMEDIAFAMILY TO dmstest;
GRANT SELECT ON MSDB.DBO.BACKUPFILE TO dmstest;
I have also scheduled backup jobs for both full and tlog backups.
I am getting the following errors for multiple tasks when trying to resume them:
Last Error AlwaysOn BACKUP-ed data is not available Task error notification received from subtask 0, thread 0 .Error executing source loop; Stream component failed at subtask 0, component st_0_dms-task; Stream component dms-task' terminated [reptask/replicationtask]Stop Reason FATAL_ERROR Error Level FATAL
[SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42000 NativeError: 9011 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]User does not have permission to query backup files with the virtual table DBLog. Only members of the sysadmin fixed server role has this permission Line: 1 Column: -1
[SOURCE_CAPTURE ]E: Failed to access LSN 'lsn_number' in the backup log sets since BACKUP/LOG-s are not available. (sqlserver_endpoint_capture)
Can someone help me to fix this? Is there any other permission DMS user requires to access the backup/logs? I am trying to do this using non-sysadmin privilege for dms user.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
