SQL Backup Master

Introduction: Explicit Credentials vs. MSA

When configuring SQL Backup Master to connect to a SQL Server instance using Windows Authentication, you have the option of entering account credentials directly into the application. In such cases, SQL Backup Master stores those credentials (encrypted) and uses them to connect to SQL Server when performing backups or restores. This approach is simple and works well, but it has a drawback: you must manually update those credentials (in SQL Backup Master) if the account password changes.

A Managed Service Account (MSA), on the other hand, is a special type of account in Active Directory that eliminates the need to manually manage and rotate passwords. Windows handles password changes for the MSA automatically on a frequent schedule, and you never actually see or store the MSA password yourself. This yields several important benefits:

• Security: No need to store encrypted passwords to support SQL connections.
• Simplicity: Windows automatically updates the MSA account’s password.
• Reliability: The MSA is tied to your domain and can be restricted to specific servers/services.

SQL Backup Master supports either approach: specify the Windows account credentials in the application or run the service under an MSA that has the necessary permissions to back up your databases.

Preparing for an MSA

Using an MSA requires the following prerequisites:

• Your environment must be Active Directory-based (no local MSA support on standalone machines).
• You must have Windows Server 2008 R2 or later (for standard MSAs) or Windows Server 2012 or later (for Group MSAs).
• You need Domain Administrator privileges (or assistance) to create and configure the MSA in Active Directory.

Creating and Installing a Managed Service Account

Typically, a domain administrator handles creating the MSA. Specific steps will vary depending on your environment, but here's an overview of the general process:

• On a domain controller, open PowerShell and run a command such as:
  New-ADServiceAccount -Name SqlBackupMasterSvc -DNSHostName SqlBackupMasterSvc.domain.local
• Install the MSA on the target server where SQL Backup Master runs:
  Install-ADServiceAccount SqlBackupMasterSvc
• Validate that the MSA can run on that server:
  Test-ADServiceAccount SqlBackupMasterSvc

If it returns True, you’re all set to proceed.

For more information on creating and managing MSAs, see Microsoft’s documentation on Managed Service Accounts.

Granting the MSA Permissions on SQL Server

Because you’ll be using Windows Authentication (Integrated Security) with SQL Backup Master, SQL Server needs to recognize the MSA and assign it the correct permissions. Again, the specific steps will depend on your environment and the commands below are just examples.

• Add the MSA as a login in SQL Server:
  CREATE LOGIN [DOMAIN\SqlBackupMasterSvc$] FROM WINDOWS;
• Create a user mapped to that login in each database you plan to back up:
  USE [YourDatabaseName];
  CREATE USER [DOMAIN\SqlBackupMasterSvc$] FOR LOGIN [DOMAIN\SqlBackupMasterSvc$];
• Assign backup permissions, for example by adding the MSA to the db_backupoperator role:
  EXEC sp_addrolemember 'db_backupoperator', 'DOMAIN\SqlBackupMasterSvc$';

Configuring SQL Backup Master to Use an MSA

By default, SQL Backup Master installs its system service with the local system account. Here’s how to switch to your MSA:

• Open Windows Services (services.msc).
• Stop the "SQL Backup Master" service if it’s running.
• Right-click the "SQL Backup Master" service and select "Properties."
• On the "Log On" tab, choose "This account" and enter your MSA in the format DOMAIN\SqlBackupMasterSvc$ (leave the password fields blank).
• Click OK to save the service logon changes.
• Grant your MSA "Full Control" over the following folder (and all subfolders and files):
  C:\ProgramData\Key Metric Software\SQL Backup Master.
• Restart the "SQL Backup Master" service and then open SQL Backup Master.
• Edit a backup job and click the Choose SQL Server button. Select the Advanced tab and enable the Use Managed Service Account option. Save your changes.

Now, your backup job will run under the MSA identity. Because no credentials are stored, it automatically uses Windows Authentication for connecting to SQL Server under the MSA context.

Verification and Testing

Run each of your backup jobs to ensure they complete successfully, reviewing the job logs for any errors or warnings. If you see authentication or permission errors, verify:

• The MSA is properly installed on the server.
• The MSA has appropriate SQL Server permissions.
• You used the correct DOMAIN\AccountName$ format for the service Log On.

Important: Test all backup jobs thoroughly after making any changes to the service account.

Conclusion: Which Approach Should You Choose?

Specifying account credentials explicitly can work for smaller or non-domain environments but involves manual password management. A Managed Service Account is typically more secure and requires less upkeep because Windows rotates passwords automatically.

For most Active Directory environments, running SQL Backup Master under an MSA is the recommended best practice, providing added security, simplicity, and reliability.