1

I have an AlwaysOn setup in which the jobs are running on domain service account which have been given access to a file share for backups. The jobs are not able to run.

This is the error message which I get

https://ola.hallengren.com Msg 50000, Level 16, State 1, Server xxxxx , Procedure DatabaseBackup, Line 457 The directory \xxxxxx\backups$ does not exist. Msg 50000, Level 16, State 1, Server HNATLD1SVRLSQ02, Procedure DatabaseBackup,

Should SQL agent account in the configuration manager run on the same account as the job owner or can it run on the NT\service account as shown in the screenshot attached, because the security context is still that of the job owner which has access to those file shares? What would be the best practice here? Please advice.

enter image description here

We have different service accounts running on the jobs

  • Is the owner a member of the sysadmin role? If so, I believe the job will run under the service account and not the context of the user. – Ryan Cooper Nov 11 '16 at 19:01
  • Yes the job owner has been given sysadmin permissions. So does it mean the SQL agent account can continue to run on the NT\service account? – user2923332 Nov 11 '16 at 19:15
  • Is the particular job step running through a proxy with appropriate credential? – Nic Nov 11 '16 at 19:20
  • So that is probably your issue. Since the owner is a sysadmin, the job is running as NT\service which isn't a domain user with access to that share. A few options: Like to change the service account to a domain user with filesystem perms and sql sysadmin or use a non-sysadmin account as the owner with filesystem perms & appropriate sql perms. Others as well. – Ryan Cooper Nov 11 '16 at 19:21
  • Yes the service account is a domain account with permission to fileshare and is sysadmin in the server role properties – user2923332 Nov 11 '16 at 19:28
1

Add a proxy account for Operating System (CmdExec) jobs and run the Ola jobs under that. This way the proxy account can have proper access to only the backup shares, etc. and you don't have to elevate permissions on the SQL Agent account (and inherently any other jobs running under the SQL Agent security context). Principle of Least Privilege for the win! :)

  • Should the service account also have connect and connect to endpoint permission? Because the backups are still not working – user2923332 Nov 14 '16 at 16:30
  • What's the error you're getting when running the job as the proxy account? – John Eisbrener Nov 14 '16 at 17:08
  • The same saying that the directory does not exist – user2923332 Nov 14 '16 at 17:36
  • The issue is that you're trying to write to an Admin Share (e.g. the $ at the end indicates this is a hidden share only local admins on the destination server can write to). Either setup the proxy account as a local admin on that server (NOT RECOMMENDED) or create a formal file share for the backups and write to that new path. – John Eisbrener Nov 14 '16 at 18:04
  • Oh, and no, the proxy account does not need elevated access within the database engine. The account that owns the job is what the job runs as when connecting to the database. The proxy account takes care of the external access. – John Eisbrener Nov 14 '16 at 18:05
0

This is the service account under which the db engine is running. It's a local service account and not a domain account which was used to run those jobs. Guess this was the problem all along

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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