So we have been experimenting with Veeam Backup & Replication for a while now. We setup a dedicated VM for Veeam Backup Server which performs daily backups of all our infrastructure components. We have not aquired a licence yet, so this is the Community edition that we're testing (version 11a build 11.0.1.1261 P20220302)
We have a few VMs with SQL Server instances containing databases. We use Veeam's application processing option to backup the databases and include them in the backup file.
However, we face a problem when we try to perform a restore of the databases from the backup file, be it on the Veeam Backup Server machine, or another local machine. We perform application item restore as described in Veeam B&R documentation then go through the steps for data publishing to the local (staging) SQL Server using Veeam SQL Explorer. We have tried publishing the database, restoring the .BAK file or even saving the MDF and LDF files directly. All operations fail for lack of permissions (see Veeam SQL Explorer logs below).
11/04/2022 00:17:32 21 (8876) Connecting to SQL Server localhost\SQL2019 using Windows authentication (username: WIN-KB0LJQ6QU6L\Administrator)...
11/04/2022 00:17:32 21 (8876) Connection completed successfully.
11/04/2022 00:17:32 21 (8876) Checking database version compatibility (server: Microsoft SQL Server 2014, database version: 782)...
11/04/2022 00:17:32 21 (8876) Target server (localhost\SQL2019) is identified as Microsoft SQL Server 2019 (version: 904).
11/04/2022 00:17:32 21 (8876) Connecting to SQL Server localhost\SQL2019 using Windows authentication (username: WIN-KB0LJQ6QU6L\Administrator)...
11/04/2022 00:17:32 21 (8876) Connecting to SQL Server localhost\SQL2019 using Windows authentication (username: WIN-KB0LJQ6QU6L\Administrator)...
11/04/2022 00:17:32 21 (8876) Validating account permissions for server 'localhost'...
11/04/2022 00:17:32 21 (8876) Validation completed successfully.
11/04/2022 00:17:33 16 (9136) Publishing database...
11/04/2022 00:17:33 16 (9136) Restore point ID: 3147eb18-d76a-47f1-ab4c-ec5a67dd81f1
11/04/2022 00:17:33 16 (9136) SQL server: localhost\SQL2019
11/04/2022 00:17:33 16 (9136) Database name: bigsoft_33o_vide
11/04/2022 00:17:33 16 (9136) Connecting to SQL Server localhost\SQL2019 using Windows authentication (username: WIN-KB0LJQ6QU6L\Administrator)...
11/04/2022 00:17:34 17 (11180) Getting Instant Recovery sessions...
11/04/2022 00:17:34 17 (11180) New USN value: 5113
11/04/2022 00:17:34 17 (11180) Loaded 0 Instant Recovery sessions
11/04/2022 00:17:34 17 (11180) Loading databases completed
11/04/2022 00:17:37 19 (9280) Getting Instant Recovery sessions...
11/04/2022 00:17:37 19 (9280) New USN value: 5114
...
11/04/2022 00:18:05 16 (9136) Database publish failed
11/04/2022 00:18:05 16 (9136) Error: Method failed with unexpected error code 3.
11/04/2022 00:18:05 16 (9136) Type: System.InvalidOperationException
11/04/2022 00:18:05 16 (9136) Stack:
11/04/2022 00:18:05 16 (9136) at System.Security.AccessControl.NativeObjectSecurity.CreateInternal(ResourceType resourceType, Boolean isContainer, String name, SafeHandle handle, AccessControlSections includeSections, Boolean createByName, ExceptionFromErrorCode exceptionFromErrorCode, Object exceptionContext)
at System.Security.AccessControl.FileSystemSecurity..ctor(Boolean isContainer, String name, AccessControlSections includeSections, Boolean isDirectory)
at System.Security.AccessControl.FileSecurity..ctor(String fileName, AccessControlSections includeSections)
at System.IO.FileInfo.GetAccessControl(AccessControlSections includeSections)
at Veeam.Engine.Security.FileAccess.GetAccessControl(String path, AccessControlSections sections)
at Veeam.Engine.Security.FileSystemSecurity.HasFileAccess(String accountName, String path)
at Veeam.Engine.FileSystem.LocalAccessChecker.GrantFileAccess(String filePath)
at Veeam.SQL.Core.Extensions.AccessCheckerExtension.CheckDatabaseFilesAccess(IAccessChecker accessChecker, IDatabaseFiles databaseFiles, String permissionSourceFolder)
at Veeam.SQL.Restore.Publish.RestorePointDatabasePublisher.Publish(ISqlBroker broker, ISqlConnectProvider sqlConnectProvider, IPublishConfig config, Boolean isClustered, ISqlActionsObserver observer, CancellationToken ct)
at Veeam.SQL.Restore.Publish.DatabasePublisher.Publish(ISqlBroker sqlBroker, ISqlConnectProvider sqlConnectProvider, IPublishConfig config, ISqlActionsObserver observer, CancellationToken ct)
at Veeam.SQL.Restore.Publish.DatabasePublisher.Publish(ISqlConnectProvider sqlConnectProvider, IPublishConfig config, ISqlActionsObserver observer, CancellationToken ct)
at Veeam.SQL.Restore.Publish.DatabasePublisher.Publish(IPublishConfig config, ISqlActionsObserver observer, CancellationToken ct)
at Veeam.SQL.Restore.Publish.LoggedDatabasePublisher.Publish(IPublishConfig config, ISqlActionsObserver observer, CancellationToken ct)
11/04/2022 00:18:06 1 (6504) Database publish failed
11/04/2022 00:18:06 1 (6504) Error: Method failed with unexpected error code 3.
11/04/2022 00:18:06 1 (6504) Type: System.InvalidOperationException
11/04/2022 00:18:06 1 (6504) Stack:
11/04/2022 00:18:06 1 (6504) at System.Security.AccessControl.NativeObjectSecurity.CreateInternal(ResourceType resourceType, Boolean isContainer, String name, SafeHandle handle, AccessControlSections includeSections, Boolean createByName, ExceptionFromErrorCode exceptionFromErrorCode, Object exceptionContext)
at System.Security.AccessControl.FileSystemSecurity..ctor(Boolean isContainer, String name, AccessControlSections includeSections, Boolean isDirectory)
at System.Security.AccessControl.FileSecurity..ctor(String fileName, AccessControlSections includeSections)
at System.IO.FileInfo.GetAccessControl(AccessControlSections includeSections)
at Veeam.Engine.Security.FileAccess.GetAccessControl(String path, AccessControlSections sections)
at Veeam.Engine.Security.FileSystemSecurity.HasFileAccess(String accountName, String path)
at Veeam.Engine.FileSystem.LocalAccessChecker.GrantFileAccess(String filePath)
at Veeam.SQL.Core.Extensions.AccessCheckerExtension.CheckDatabaseFilesAccess(IAccessChecker accessChecker, IDatabaseFiles databaseFiles, String permissionSourceFolder)
at Veeam.SQL.Restore.Publish.RestorePointDatabasePublisher.Publish(ISqlBroker broker, ISqlConnectProvider sqlConnectProvider, IPublishConfig config, Boolean isClustered, ISqlActionsObserver observer, CancellationToken ct)
at Veeam.SQL.Restore.Publish.DatabasePublisher.Publish(ISqlBroker sqlBroker, ISqlConnectProvider sqlConnectProvider, IPublishConfig config, ISqlActionsObserver observer, CancellationToken ct)
at Veeam.SQL.Restore.Publish.DatabasePublisher.Publish(ISqlConnectProvider sqlConnectProvider, IPublishConfig config, ISqlActionsObserver observer, CancellationToken ct)
at Veeam.SQL.Restore.Publish.DatabasePublisher.Publish(IPublishConfig config, ISqlActionsObserver observer, CancellationToken ct)
at Veeam.SQL.Restore.Publish.LoggedDatabasePublisher.Publish(IPublishConfig config, ISqlActionsObserver observer, CancellationToken ct)
at Veeam.SQL.Restore.Publish.AuditedDatabasePublisher.Publish(IPublishConfig config, ISqlActionsObserver observer, CancellationToken ct)
at Veeam.SQL.Restore.Publish.StoringDatabasePublisher.Publish(IPublishConfig config, ISqlActionsObserver observer, CancellationToken ct)
at Veeam.SQL.Restore.Publish.PublishService.Publish(IPublishConfig config, ISqlActionsObserver observer, CancellationToken ct)
at Veeam.SQL.Explorer.Async.Publish.AsyncPublishDatabaseTask.Run(IProcessObserver observer, CancellationToken ct)
at Veeam.Presentation.Async.VisualAsyncTask.Execute(IProcessObserver observer)
Note that Veeam was installed as a Local System account on the Veeam Backup Server and the user logged in using Windows Authentication is in Administrator's group. Moreover, in our local machine we imported the backup and have tested running everything as the user "Administrator" on Windows Server 2019 (Veeam services, Veeam user account and sql explorer service), yet the permissions problem remains.
This question is to anybody that is specifically familiar with Veeam or anyone who has an idea about the generic error message and how to bypass it by providing full permissions in Windows.
Best Answer
After contacting support, it turns out, the issue was that I was not including the SQL Server DATA directory in my backup. I thought Veeam would do that for me since it allowed me to activate application processing. I do not remember seeing any detail about this in the docs, or I could have missed it.
This could have also been mitigated by selecting the entire computer for backup or doing a volume backup of the C drive. Apparently it is recommended to save the full machines anyway for instant recovery.
All the ambiguous errors went away by simply including the DATA directory.