2

I'm trying to automate backups using Ola Hallengren's backup scripts.

I'm testing this out on a SQL express instance 2008 R2 (I know it's EOL today). I've setup the jobs and I created a scheduled task to run a batch script with SQLCMD. The scheduled task is running as a gMSA

Everything seems to be groovy except for some darn reason I can't reach the UNC path I'm trying to backup to. The error I'm getting is as follows:

Msg 50000, Level 16, State 1, Server servername\instancename, Procedure DatabaseBackup, Line 878
The directory \\servername\sharename does not exist.

This has to have something to do with the gMSA not having proper permission to the share or something but I've explicitly granted this account full access at the share and NTFS levels (out of desperation) and still no dice. Obviously the share does exist, and I can connect to it just fine using other accounts.

If anyone has any experience running SQL server with gMSA's who could shed some light on this I would greatly appreciate it.

Thanks,

UPDATE - Just for kicks I gave the service account sysadmin priviledges on the SQL server and now it works. I have no idea why that would fix problems with access to a file share so the error is definitley misleading. What are the minimum rights required to run a backup for databases? Initially I gave the service account the "public" server role and "db_backupoperator" on each database. Looks like I'll need more than that? The target here is least priviledge.

Thanks

Improve this question
7
  • 2
    Support for MSA can in SQL Server 2012, and gMSA in 2014 (IIRC, possibly 2016). When you say "running as a gMSA", what exactly do you mean? Is the service account for Agent a gMSA (not supported)? Or something else?
    – Tibor Karaszi
    Commented Jul 9, 2019 at 14:53
  • Specifically the scheduled task is running as the gMSA. There's no agent because it's express so I'm using a scheduled task as outlined in Ola's FAQ I think to your point though this may have more to do with gMSA's and scheduled tasks and less to do with SQL server... so I may be in the wrong place.
    – PJRap986
    Commented Jul 9, 2019 at 15:02
  • Note that the gMSA is irrelevent for the backup command. It is the database engine, the SQL Server service account that need permissions on that share.
    – Tibor Karaszi
    Commented Jul 9, 2019 at 16:25
  • If I run the batch script either manually or by running the scheduled task on demand, as a regular user (which has sufficient permissions to do a backup) it works just fine. The account the SQL services is running under is NETWORKSERVICE, which should be fine since the permissions on the share at the moment is "everyone". It just seems like when I change the user context the task runs under, that's when it fails.
    – PJRap986
    Commented Jul 9, 2019 at 18:51
  • Network service doesn't exist outside the machine. So it is not included in "everyone". That would have explained why it failed. But it doesn't explain why it works when you do it interactively. Perhaps the error isn't rooted in the BACKUP command? Ola uses structured error handling, so the error you see comes from Ola's code. Try to dig deeper in his error table, output file and if there is some option for his proc for verbose output...
    – Tibor Karaszi
    Commented Jul 10, 2019 at 6:34

1 Answer 1

Reset to default
0

If you check the documentation for Ola Hallengren's solution, you'll see under "Which permissions are needed for the SQL Server Maintenance Solution to work?" the following:

  1. DatabaseBackup: sysadmin
  2. DatabaseIntegrityCheck: EXECUTE on dbo.DatabaseIntegrityCheck, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases
  3. IndexOptimize: EXECUTE on dbo.IndexOptimize, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

I believe the reason sysadmin is required is because the solution uses xp_fileexist to check the existence of a directory, and this extended stored procedure requires sysadmin rights.

Normally, the jobs are executed by a SQL Agent which has sysadmin rights by default granted to the per-service sid NT SERVICE\SQLServerAgent (or NT SERVICE\SQLAgent$instance_name for a named instance). If you use a proxy to run the jobs in SQL Agent, that proxy also needs sysadmin, as does the user account running the procedure from any other scheduler, as is your described scenario.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.