1

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?

  • 3
    My gut goes with your DBA and that Amazon is just being lazy in figuring out what they actually need. I'd guess they only need the ability to read data, schema, and security (i.e. users and roles). Why not set it up on a test server with the above and see if it works? – Ben Thul Jan 29 '17 at 1:16
3

Is there good reason not to move forward, or is this just the cost of doing business?

I completely understand the DBA's reluctance and giving out SA is not a good practice. This becomes especially rough if the server falls under any industry regulations such as PCI/SOX where logins with SA are required to be monitored and have justifications.

However, there is a business need for this and you're already using AWS which makes the concern sort of moot. I'd definitely as Amazon for a list of permissions and not just SA, but I would also (in parallel) continue on with testing and not let it be a blocker.

how dangerous is creating such an account (assuming other best practices are followed ie: securely encrypted passwords, restricted access by ip address, etc.)?

Giving out SA should be no light matter, as whomever has access to it can do anything they want without leaving a trace. Assuming this server isn't directly accessible from the internet and the login is restricted by IP, etc., that would limit the surface area the best. After that it's up to trusting Amazon, which it doesn't seem as though you have a reason to not trust them.

Overall, I'd ask for a list of least permissions required but would continue on with using the service and setting up the account as needed. Personally, I'd put extra auditing around that login and watch it like a hawk in my test environment to see what it "really" does... but it's something that's required if you want to use the service - price of doing business with that service owner.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.