File permissions for SQL Server 2014 virtual account

Question

I have two SQL Server 2014 instances installed on the same server - one for production, and one for development. I have set up a SQL Agent job that identifies the most recent backup from the production instance and restores it on the development instance. When I run the job, it gives me an error like this (names altered to protect the innocent):

Executed as user: NT SERVICE\SQLAgent$MyAppDEV.
Cannot open backup device 'E:...MyApp_backup_2016_02_24_092912_0136837.bak'.
Operating system error 5(Access is denied.).
[SQLSTATE 42000] (Error 3201)
RESTORE DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013).
The step failed.

I read a little about the virtual accounts that SQL Agent runs under. If I open the folder in question and give read permissions to NT Service\SQLAgent$MyAppDEV, I still get the same error. But if I give read access to the Authenticated Users built-in account, everything works.

What gives? The error message purports to tell me what security context the job is using, but that doesn't seem to match up with reality.

Answer 1

Dan,

I'm assuming the account running the agent job is a sysadmin. In this case, the DEV instance will need to have its' service account or service sid for the DATABASE ENGINE given access to the folder.

SQL Server agent doesn't do the restore, it connects to SQL Server and impersonates the account if non sysadmin and then continues. In this case you're connecting to the database engine and issuing a RESTORE DATABASE command which will be processed by the engine. SQL Agent isn't touching the backups, the engine is.

Edit: Explaining the posted agent output

Executed as user: NT SERVICE\SQLAgent$MyAppDEV.

This is letting you know that the Agent job was executed as the SQLAgent$MyAppDEV local account. This is most likely due to the job owner being sysadmin.

Cannot open backup device 'E:...MyApp_backup_2016_02_24_092912_0136837.bak'.

Here is the actual error, we can't open the file. We don't know why yet.

Operating system error 5(Access is denied.).

This is explaining why we can't open the file, the OS returned error 5. This can be validated by checking the OS return code by executing net helpmsg 5 on a command prompt.

[SQLSTATE 42000] (Error 3201)
RESTORE DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013).
The step failed.

Some extra notes about what is happening, but this isn't relevant to the actual error or issue.