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.