SQL Server – Deploying SQL Server Backup Maintenance Plan in Multi-Tenant Environment

We have a multi-tenant environment in which I've been trying to deploy a standardised maintenance plan across all servers. I've been running into issues importing the maintenance plan properly, as well as issues setting up the correct minimal permissions to run the job.

Can anyone offer some guidance on how such a backup maintenance plan can be deployed across a heterogeneous, multi-tenant environment, such as ours. I've detailed my attempt below, and given the level of complexity is quickly rising, I'm wondering if I'm heading in a completely incorrect direction for my situation.

Is there a better way?

I'm looking for a solution that can be easily deployed via script/SQLCMD, so I can roll it out via Puppet. I'm also hoping to stick with Maintenance plans as SQL knowledge is low in our team, and having future edits to the backup plan require in depth T-SQL knowledge would be problematic (though it seems like a T-SQL script or to in order to run the backup may be the simplest way out).

The Environment

• Right now we run mostly Windows 2008 R2 with SQL Server 2008 R2 Standard
• However, a solution that supports Windows 2012 and SQL Server 2012/2014 would be the aim
• We support many SQL Servers for multiple customers, all of which are isolated.
• Some customers run on their own Active Directory forest, whereas others run stand-alone SQL Server with no AD. So there's no common factor in terms of AD authentication.
• All customers are isolated into their own network segment, but we can poke holes in the firewall for backup purposes.
• Right now we use Puppet for basic configuration management across our servers. Basically this translates into an easy ability to deploy new files, run scripts, SQL (via SQLCMD) and setup permissions/users on servers in a standard way.

What I've Tried

Right now, my main attempts have involved exporting my maintenance plan to a DTSX file via integration services, and the related T-SQL for the job Subplan. Then importing these into a target server, running under the same username/password.

Some rough steps:

1. Create maintenance plan on a server, as normal. Setup schedule.

2. Configure local server connection on the plan to connect as my standardised "backup" user.

3. Configure subplan1 job to be owned by "backup" user and configure Subplan_1 Step 1 to connect via the same backup SQL user.

4. Connect locally to Integration Services and export the maintenance plan stored in MSDB to a DTSX file, specify a custom encryption password. Also export the Job subplan_1 T-SQL to a new SQL file.

5. Grant backup user db_backupoperator on all databases.

6. Grant backup user SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole

7. Create a backup SQL user with the same permissions on the target server

8. Deploy the DTSX and SQL files to the target server,

9. Edit the DTSX file so that the local connection points to (local) instead of the source server's hostname.

10. Execute:

    dtutil.exe /FILE "C:\backups\Backup Plan.dtsx" /DestServer . 
               /Decrypt  MyEncryptionPassword 
               /COPY SQL;"\Maintenance Plans\Backup Plan"
    
    sqlcmd -E -S . -i "C:\backups\Backup Job.sql"
    

Problems with this approach

1. The "backup" user's password is present in plain-text in the SQL job file. I can work around this by instructing Puppet to delete the file immediately after execution, but it's a risk.

2. So far, I've been unable to have the job successfully run under my "backup" user context, unless I grant the sysadmin server role. I get the following:

   Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.

   This has been documented in various places, but I'm hoping to find a set of permissions that avoids having to create a windows user account just to run the job.

3. Even after I enable sysadmin temporarily, to work around the above, I still cannot right click and Modify or Execute the maintenance plan from the target server where I imported the DTSX (or should I say, when I execute, the job runs "successfully" but doesn't seem to actually do anything).

4. If I go down to the Job subplan_1 file, and click "Start Job at Step", it seems to actually try and run, but fails with a connection error:

   Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection.

This is where my troubleshooting is still ongoing.

But with the amount of research I've had to do, and the number of various complexities and issues I've run into, leaves me thinking there must be a smarter way to go about this in a multi-tenant environment?