While trying to perform replication from source to target using DMS, you may encounter the following error:

Failed to get the capture list from the endpoint. Stop Reason FATAL_ERROR Error Level FATAL

Below are the steps to troubleshoot this issue. Let me know if you face any challenges. I'm happy to help.

In my scenario, my source is SQL Server and the target is S3.

Pre-Checks

Check if both the source and target are in the same VPC. If not, create a VPC Peering.

Ensure proper IAM roles are set for the target S3 bucket.

The above options are usually set properly. If you are still facing the error, please check the solution below that worked for me.

Solution

DMS prefers to use dms_user to connect to the database. If you are using any other user ID (albeit the endpoint is successful), please create a new one with the dms_user credentials.

Below are the steps for creating the user:

`USE db_name;

CREATE USER dms_user FOR LOGIN dms_user; 
ALTER ROLE [db_datareader] ADD MEMBER dms_user; 
GRANT VIEW DATABASE STATE TO dms_user;

USE master;

GRANT VIEW SERVER STATE TO dms_user;`

This solution should work! Good luck.

Reference Docs :

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

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