MS SQL VM Image Job can truncate T-Logs, but the periodic T-Log jobs can't

[rleon]

Hi all,

Hope this is something simple. We have observed the following:
A VM image backup job with guest processing enabled, "Require successful processing" enabled, and on the SQL tab, the "Backup logs periodically" enabled.

The "Guest OS credentials" uses an account that is a member of the OS level administrators group.
For the MS SQL instance in the VM, the same account also has all the required minimal permissions granted as documented here:
https://helpcenter.veeam.com/docs/backu ... =110#vesql

The VM image job can complete successfully, its "Truncating transaction logs" step also succeeds.
The separately ran periodic transaction log backup job can also successfully backup and truncate the logs.
The results above were expected, given that we granted the account all the required permissions, at both the OS and SQL level.

Then, as a test, in SQL, we removed the "db_backupoperator" permission from one of the user DBs.
As expected, the separately ran periodic transaction log backup job started showing errors about not being able to backup or truncate logs for that specific DB where the "db_backupoperator" permission was removed for the user.
However, for some strange reasons the VM image job is not affected, with the "Truncating transaction logs" step still completing successfully.
We were expecting the VM image job would at least throw a warning that says something along the lines of "The VM backup was successful, but transaction logs for some DBs cannot be truncated."
The fact that the VM image job does throw any warnings or errors is worrying. We cannot be sure whether the log truncation for the DB actually happened or not.

Any ideas why this is the case? Could it be that, the account, as an OS level administrator, through the SQL VSS writer, just truncated the logs for all DBs anyway, ignoring that the same account not longer has the "db_backupoperator" permission for some DBs?

Thanks!

[Mildur]

Hi Rleon

Whats the version of the mssql server?

If the guest OS Credentials user do not have the permission to truncate the database, veeam will do a fallback the SYSTEM Account. On older MSSQL Servers (I think, until 2012), the SYSTEM Account had sysadmin permissions on the sql server after the installation of the sql instance.

Please check the permission of the NT AUTHORITY\SYSTEM user in the sql instance.

[rleon]

Hi Mildur,

It's Veeam 11a backing up a SQL Server 2019 VM.
I checked, NT AUTHORITY\SYSTEM only has the "public" permission, and doesn't map to any DB users.

[rleon]

In SQL, the default "NT SERVICE\SQLWriter" user does indeed have the sysadmin permission.
We suspect since VSS is triggered during a VM backup, the SQL VSS Writer, through the "NT SERVICE\SQLWriter" SQL sysadmin user, could truncate all DB logs.

To test this theory, we removed the default SQL "sysadmin" permission from "NT SERVICE\SQLWriter".
This time, the VM image job completed with a warning:
"SQL VSS Writer is missing: databases will be backed up in crash-consistent state and transaction log processing will be skipped"

Mystery solved. It really was VSS bypassing the job settings "Guest OS credentials" account during a VM image level backup to truncate the transaction logs.
I wish this was documented better!

[Mildur]

Hi rleon

Thanks for your tests.
For the documentation part, I only know that the SYSTEM account is mentioned in the following kb article, but not the SQLWriter account.

KB2027 - Backup Job reports warning "Failed to truncate Microsoft SQL Server transaction logs."

If SQL Truncation is unable to be performed with the specified account, the software will failover and attempt to use the NT AUTHORITY\SYSTEM account.