I'm trying to setup Amazon DMS to replicate data from our production SQL Server instances (currently running in dedicated EC2 instances) into Redshift for data warehousing purposes. The DMS documentation explicitly states that:
The AWS DMS user account must have the sysAdmin fixed server role on the Microsoft SQL Server database you are connecting to.
Windows Authentication is not supported.
This is concerning to our DBA. They are wary of creating an account (sql or AD really) with sysadmin rights and then giving that access to a 3rd party service. While I understand the concern, I'm inclined to trust Amazon, create the service account, and get on with my day.
They've suggested setting up a job to grant the account the necessary sysAdmin role just prior to a nightly synchronization and then reverting the permissions after the job completes. That feels unnecessarily paranoid to me, and doesn't work at all if we move to continuous replication.
So my question is: how dangerous is creating such an account (assuming other best practices are followed ie: securely encrypted passwords, restricted access by ip address, etc.)? Is there good reason not to move forward, or is this just the cost of doing business?