Why does SQL Clone Agent need local Administrators privilege?

John_Zabroski_wam

I recently asked this question on ServerFault and got an answer indicating Backup Operator should be sufficient: https://serverfault.com/a/948106/238609

However, SQL Clone Agent service will not start unless it's service account is a local administrator.

John_Zabroski_wam

I should add: IT Audits are extremely expensive, costing anywhere between $20,000 to $100,000 per audit for one of my clients.  So every time we use a tool that requires Administrator privilege to function, it raises an audit flag and lowers the IT department score and increases the frequency of costly audits.

ChrisHurley

While Backup Operator is sufficient to open a connection to VDS (although I should note that since this is deprecated, SQL Clone now uses the Windows Storage Management API on supported platforms), that isn't enough to do operations that SQL Clone requires, such as attaching virtual disks. That requires the privilege "Perform volume maintenance tasks", which is only available to local Administrators by default (which Microsoft recommend as best practice). There's a lot of other permissions that would be required on top of Backup Operator to perform SQL Clone's basic functionality - I attempted a build which didn't enforce local admin and had to change permissions on certificate private keys and SQL Server data files, and grant "perform volume maintenance tasks" just to get part-way through the process, but that still wasn't sufficient to perform the required storage management API calls.

Moreover, SQL Clone agents auto-update themselves by virtue of the fact that they are running as local admin, so even if we were able to find a lengthy set of permissions that could permit perform imaging/cloning, auto-updating would be broken without permission to install software.

Of course, we would always want to keep the requirements for our software as low as possible, but haven't managed to find a reasonable way to run SQL Clone Agents with lower than local admin privileges given the kinds of operations it does. What we would generally recommend is to not install SQL Clone Agent on production systems where there would understandably be much greater scrutiny, and instead use backups to import data into the system.

John_Zabroski_wam

Thanks, Chris!  This is not a production system, but auditors do not seem to care.  Ability to have group MSA is the only saving grace here - this probably lowers it from a red flag to a yellow flag.

Do you know what permissions the Windows Storage Management API requires?  I tried to find documentation prior to writing this post and the one on ServerFault, but the documentation is sparse.

T_Steve_G

I created an account to give feedback on this topic. I don't know what, if anything that can be done regarding the requirement of local administrator for the service: But this is a serious barrier for many large customers, my enterprise included.

As an enterprise DBA I was exploring SQL Provision to make a case to explore it at my workplace. But unfortunately when I learned it needed local administrator this stopped my effort in it's tracks. Any DBA who also has responsibilities for the security of the sql environments (which should be most of us right?) is in the process of or has already eliminated every such application requirement as possible, and will be averse to adding a new service that requires local administrator permissions.

I can see a lot of use for a product like SQL Clone (it does some fantastic and helpful things) and will continue to watch to see if this requirement can be might be able to be removed in the future.

John_Zabroski_wam

@T_Steve_G

I think you just need to communicate to RedGate how much $ your company would be prepared to spend on such a solution.  If you're a large enterprise, SQL Clone will likely be cheaper than Dephix or WinDocks, and probably better supported.

John_Zabroski_wam

The other drawback to SQL Clone and most tools like it is there is no RDS offering.  You can somewhat work around this by using regular SQL Server IAAS instances for dev.