Depending upon some of the SQL Server Agent Jobs being run by a SQL Server Agent, it's possible for the SQL Server Agent to need elevated permissions to run some of the tasks it's trying to tackle. This can be the case in situations where replication is involved, or where log shipping is being used (and where the SQL Server agent will need to interact with file shares). It's more common, in my experience, found in situations where SQL Server Agent Jobs are interacting with other network components or machines—commonly via either .bat files being run in jobs or via PowerShell scripts being executed as jobs.
Related: Finding SQL Server Agent Jobs Running at a Particular Time
And, while it’s arguable that the SQL Server Agent might not be the best tool to tackle jobs like this, the reality is that SQL Server Agent's ability to send alerts when jobs fail (or for other reasons)—as well as an insanely flexible scheduler—make the SQL Server Agent a fairly compelling daemon/launcher for other tasks.
The Wrong Way to Escalate Permissions
Say, for example, we've got a complex bit of data crunching that goes on nightly—and that as part of this process—we want to turn off a service running on an application server so that it won't keep thumping our SQL Server database while we're crunching numbers. In a situation like this, not only is your SQL Server Agent going to need the ability to go off-box and communicate with other machines in your network, it's also going to need elevated enough permissions (on/against the target application server) to be able to run net stop and net start commands before and after the data crunching job kicks off.
Again, while there's definite merit in discussing whether using a SQL Server Agent Job is even the right way to tackle this particular issue, one thing is clear: the wrong way to tackle this problem would be to run your SQL Server Agent service with elevated (enough) credentials to let it reach across your network and run net stop/net start or other elevated operations against remote hosts. Taking this approach means that your SQL Server Agent is ALWAYS running with elevated permissions—making it a bigger target and bigger source of potential abuse.
More Secure Permissions Escalation: Credentials and Proxies
In situations where elevated permissions are needed to execute particular jobs, or more importantly, steps within giving jobs, a more secure way to tackle this issue is to use credentials and proxies.
So, to address the example above (of wanting to have Job Steps before and after a data crunching Job Step), a more secure way to address this problem would be to:
- Create a Least Privilege account on the domain that would allow a remote/network account to connect to the application server in question and either run net stop/net start or something even more specific/restrictive by only giving it the least amount of privilege necessary to accomplish this one task – and nothing more.
- In SQL Server Management Studio, against the server in question, expand the Security node and then create a new Credential – where you’ll create a ‘name’ or ‘handle’ for this credential and then specify its identity details (username and password) as shown below:
- Now, expand the SQL Server Agent Proxies node, and create a new Proxy—where you'll give it a name, specify the credentials it should use (i.e., from the previous step), and then specify which subsystems (or types of Job Steps/Types) it can execute against (again: think least privilege and try to be as restrictive as possible):
- Now, at this point, you can go into your SQL Server Agent Job and modify the Job Step where you need elevated permissions and you'll now be able to see the option to use your proxy instead:
The benefit to this approach, of course, is that while you are still using elevated permissions, you're doing so by taking advantage of the fact that the SQL Server Agent was designed by Microsoft to be able to impersonate other accounts and credentials—but you're only doing it for very brief times and under explicitly defined scenarios.
Of course, while this approach is more secure, it's not foolproof or immune to security problems. Malicious users could, in theory, take over your SQL Server Agent Job/Job Step and replace the commands you've explicitly defined with their own payload—provided, of course, that they have sufficient permissions to take control of Jobs/Job Steps. Of course, assuming they did take over your job step and now have carte blanche with the Proxy you've given them… the idea is that they shouldn't be able to do very much at ALL with this proxy—because you explicitly created the proxy against a least-privilege account which can't do much more than what you created it to do (i.e., I certainly hope no one does anything stupid with this tip and sets up an Admin proxy that lets them think they can just use a high-privilege proxy whenever and wherever they want—because that would be completely contrary to everything I've tried to outline with this post).
Related: DBAs and the SQL Server Agent
