March 30, 2010 at 2:11 pm
This questions has always perplexed me hence I decided to ask it. I setup a SQL Server 2000 backup job which backups a database, and publishes it to a network share folder. Initially the SQLServer Agent was running as Local Admin Account but I changed it to use a domain account. In addition, I provided full control permission to the SQL Server agent domain account for the network share folder.
When doing a test run of the job, it failed. Although the job is running using the SQL Server Agent domain account but it is still failing. Which tells me that it is probably using the SQL Server service account, which is running using Local Admin account.
This brings me to my question. When running a SQL Server Agent job, does is also use the SQL Server Service account as well? Or does it depend on the type of job/task??
Based on my research it was suggesting running SQL Server Service using a domain account which will fix my backup job failing issue. I know its a good practise to use a domain account instead of Local Admin account.
Any insight will greatly help me.
Thanks,
March 30, 2010 at 10:17 pm
The SQL Server Agent service is essentially a scheduler (that's a gross simplification, but in the context of the question it's easiest to think of it that way.
All interactions with a database are done under the auspices of the SQL Server service account, regardless of whether they're started by an internal or a user connection. In the case of an SQL Agent job, in essence it's just another user connection.
The easiest way to illustrate this is using the command:
exec xp_cmdshell 'whoami'
This command will return the name of the user at the OS level who runs the command.
1. If you run that command through Query Analyser, SSMS or any other interactive means you will get the name of the account under which the SQL Server service was started.
2. If you set up an SQL Agent job and run the same command you will get exactly the same result as for test 1, regardless of what account the SQL Server Agent service is running under.
Therefore in your case it's the SQL Server service account that needs to have rights to the share, not the SQLAgent service account. As the share is on a different machine you'll need a domain account to access it (and you're right: if you were following best practice that's what you would have already).
March 31, 2010 at 8:19 am
Mr 500 thanks a lot for your response. Great insight and definitely learned something new. When I looked in the log for the job which failed, its says "Executed as user: domain\username" when i see this it tells me the job ran under the reported username reported in the log. But according to you it is using SQL Service account. Basically the agent is just another user and any transactioin with the SQL server Db is running under the service account. Although I am repeating the same point twice, but I think i have understood it now.
Another question, what significance does the "Owner" have, in the job properties. Can I put any account there? Can I use the agent account there too? Does this owner need to be granted permission so it can be executed by the agent as a job??
March 31, 2010 at 3:35 pm
There's two different levels of rights involved, one within SQL Server and the other at the Windows level.
The Owner of a job is the account under which the job is to be run in SQL Server. Therefore that Owner must have the necessary rights within the database(s) and system to perform whatever actions the job requires. This is exactly the same as if the user and kicked off the job actions interactively rather than using SQLAgent.
Regardless of who the job owner is, as far as the OS (Windows) is concerned the job will be run under the context of the SQL Server service account.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply