Microsoft SQL DMS Source CDC migration to S3 Target unsuccessful

I have a Windows_Server-2019-English-Full-SQL_2022_Standard-2023.03.15 running on an EC2 instance as a source for DMS to be migrated to S3 target. I am using RDP to connect to the instance and SSMS to configure database.

The migration succeeds when a full-load-only migration task type is used, but fails with full-load and ongoing replication and CDC-only migration task types. The database has a user that has the sysadmin, and db_owner roles. My user has SELECT, EXECUTE, VIEW SERVER, VIEW DATABASE permissions. Full DB backup was done. CDC enabled on both table and DB. A Distributor & publisher is set-up. My table has a primary key, and no unique index. I am using a DMS Instance engine_version = "3.4.7" type "dms.t2.micro".

User Database permissions:

I used this link to set-up MSSQL Database:

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html

CloudWatch Logs from Full-load and CDC:

• 2023-03-24T10:24:20 [SOURCE_CAPTURE ]E: SqlStat: Can't retrieve exception Information. [1020102] (sqlserver_log_processor.c:4218)
• 2023-03-24T10:24:20 [SOURCE_CAPTURE ]E: Unknown '0' native error detected while SQL_ERROR is flagged / SQLSTATE is not empty. [1020102] (sqlserver_log_processor.c:4219)
• 2023-03-24T10:24:20 [SOURCE_CAPTURE ]E: Encountered an unexpeceted error. [1020102] (sqlserver_endpoint_capture.c:961)
• 2023-03-24T10:24:20 [TASK_MANAGER ]D: Stream component failed at subtask 0, component st_0_FJEVKYXUXT4EL37EIZLPUSAOLI5SQIWJICONNJQ [1020102] (subtask.c:1414)
• 2023-03-24T10:24:20 [SOURCE_CAPTURE ]E: Error executing source loop [1020102] (streamcomponent.c:1873)
• 2023-03-24T10:24:20 [TASK_MANAGER ]D: Error executing source loop; Stream component failed at subtask 0, component st_0_FJEVKYXUXT4EL37EIZLPUSAOLI5SQIWJICONNJQ; Stream component 'st_0_FJEVKYXUXT4EL37EIZLPUSAOLI5SQIWJICONNJQ' terminated [1020102] (replicationtask.c:2891)

CloudWatch Logs from CDC only:

• Set up MS-CDC Replication for tables Setting/ECA 'setUpMsCdcForTables' is Disabled
• Positioning token value upon start is: 'timestamp:2023-03-27T09:05:37'
• Querying for 1st LSN matching timestamp '2023-03-27T09:05:37' on physical device '(null)', at position 0. Please wait as this may take some time.
• 2023-03-27T13:41:32 [SOURCE_CAPTURE ]E: Failed (retcode -1) to execute statement [1022502] (ar_odbc_stmt.c:2732)
• 2023-03-27T13:41:32 [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42000 NativeError: 9005 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid parameter passed to OpenRowset(DBLog, ...). Line: 1 Column: -1 [1022502] (ar_odbc_stmt.c:2738)
• 2023-03-27T13:41:32 [SOURCE_CAPTURE ]E: Failure in resolving stream position by TIMESTAMP [1020101] (sqlserver_log_utils.c:2647)
• 2023-03-27T13:41:32 [SOURCE_CAPTURE ]E: Failure in starting (with TXN consistency...) [1020101] (sqlserver_endpoint_capture.c:495)
• 2023-03-27T13:41:32 [TASK_MANAGER ]D: Stream component failed at subtask 0, component st_0_FJEVKYXUXT4EL37EIZLPUSAOLI5SQIWJICONNJQ [1020101] (subtask.c:1414)
• 2023-03-27T13:41:32 [SOURCE_CAPTURE ]E: Error executing source loop [1020101] (streamcomponent.c:1873)
• Last Error Stream Component Fatal error. Task error notification received from subtask 0, thread 0 [reptask/replicationtask.c:2822] [1020101] Error executing source loop; Stream component failed at subtask 0, component st_0_FJEVKYXUXT4EL37EIZLPUSAOLI5SQIWJICONNJQ ; Stream component st_0_FJEVKYXUXT4EL37EIZLPUSAOLI5SQIWJICONNJQ' terminated [reptask/replicationtask.c:2829] [1020101] Stop Reason FATAL_ERROR Error Level FATAL

What I have tried:

I have specified a LSN number for CDC start point. This was unsuccessful and provided the same logs seen above (cdc only).

I have tried to set the "setUpMsCdcForTables" setting to "true" in my task_settings.json file but this is not possible through Terraform apply or manually available on the console. I enabled this manually on the db/table.

According to documentation

*"AWS DMS version 3.4.7 and greater can set up MS CDC for your database and all of your tables automatically if you aren't using a read-only replica. To use this feature, set the SetUpMsCdcForTables ECA to true. For information about ECAs, see Endpoint settings." * This does not prove true in my case.

Followed Microsoft documentation:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-db-transact-sql?view=sql-server-ver16

Followed AWS workshop for Microsoft SQL Server to Amazon S3:

https://catalog.us-east-1.prod.workshops.aws/workshops/77bdff4f-2d9e-4d68-99ba-248ea95b3aca/en-US/sqlserver-s3

What I do not understand

Querying for 1st LSN matching timestamp '2023-03-27T09:05:37' on physical device '(null)', at position 0. Please wait as this may take some time.

The physical device = null, if this means that the transaction logs for the MSSQL database cannot be accessed, and why this is the case.

2023-03-27T13:41:32 [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42000 NativeError: 9005 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid parameter passed to OpenRowset(DBLog, ...). Line: 1 Column: -1 [1022502] (ar_odbc_stmt.c:2738)

Invalid parameter, I am guessing, is due to the error shown before (physical device).

Why this is not succeeding?

Please advise