I ran into an issue with Windows directory access permissions yesterday that I thought was worth a post. Here at my company, we use LiteSpeed for our backups. For those not familiar with LiteSpeed operation, it basically creates some extended stored procedures that link to DLLs that perform database backups, compression, and a host of other things. So instead of using T-SQL’s BACKUP command, you call xp_backup_database and / or some other procedures, which then call the LiteSpeed DLLs to do the work. We’ve got this set up to run via a scheduled job.
For some other reasons, we had one server where we needed to change the account SQL Server and SQLServer Agent used temporarily. We changed them from the normal account to Local System. While running under this account, our backup jobs for that server started failing. A little digging showed the failure was due to that account not having access to the backup directory:
We all know that when jobs are executed, the commands are run using the account permissions for the Windows account SQL Server Agent is running under. So this seemed like a straightforward issue to me. The other work that required the service account changes had been complete and I was free to move the services back to running under their original accounts. Since this was a production system, I was not able to immediately change the SQL Service account, but I was able to change the SQL Agent account. So I did this, restarted the service and re-ran the backup job, thinking it should now complete successfully.
It still failed with the permission denied error. Hmm.. I tried running the command manually via SSMS and got the same error. This got me thinking about what was going on behind the scenes. Because the command was calling an extended stored procedure, which in turn calls a DLL, I theorized that even though SQL Agent was the account executing the job, the call to the DLL was being done by the SQL Server engine and thus, was made under the SQL Server service account. I did not find any documentation of this anywhere. Truthfully, I didn’t look too hard because I knew I was going to be able to change the SQL Server service account the following morning. When I did that and restarted the service, the job completed without error.
As I said, I did not research this very much, so I will not say this is 100% certain. But my experiment certainly does provide some pretty significant evidence: extended procedures are executed using the SQL Server service account, not the SQL Agent service account, even when executed from a scheduled job.
(I will also note this was done using SQL 2005. Other versions may behave differently.)