Join Log in
Score:1
youcef nafa avatar Server

Veeam SQL Server Database Publishing Fail with unexpected error code 3

jp flag
youcef nafa

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).

Screenshot of the error

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.

61
0 + 0
Score:1
youcef nafa avatar Server
jp flag
youcef nafa

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.

0 + 0
Score:0
Manu avatar Server
us flag
Manu

See Required Permissions at:

https://helpcenter.veeam.com/docs/backup/explorers/vesql_permissions.html?ver=110

Minimal permissions on mssql for Veeam Service Account to perform backups:

Engine Level: VIEW ANY DEFINITION, VIEW SERVER STATE

DB Level: master: db_backupoperator, db_datareader

msdb: db_backupoperator, db_datawriter, db_datareader

any DB you want to Backup trn-logs: db_backupoperator, db_denydatareader

Also your Veeam service account needs to be part of the local admin group.

To be able to perform restores directly into mssql, the dbcreator engine level role is required.

To set this properly up, you'll need to change your Veeam serivce account to a domain user.

Easy Mode: Domain Admin and Sysadmin on SQL (if you want to backup and restore AD objects too, you could go this way right away)

0 + 0
youcef nafa avatar
jp flag
youcef nafa
This was my first thought, that is why I reinstalled veeam as domain Administrator as stated in my question. I even tested "sa" for SQL connection and everything failed. The issue was the missing SQL Data directory missing from my backup file.
0
Reply
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.