SQL University: SQL Agent Jobs That Run at SQL Agent Start

, 2011-10-27

SQL University logoWelcome to Security week at SQL University. I apologize for the late start. However, if you want to do some related reading from last week, take a look at the Kerberos series I started:

The reason I point out the Kerberos posts is we will continue that series tomorrow. But for today I want to talk to you about looking somewhere that most people never think to look for a potential security threat: SQL Server Agent. SQL Server Agent has sysadmin role membership in SQL Server, meaning it could do anything. Jobs owned by members of the sysadmin role will run with those privileges, meaning a job could do anything within SQL Server. So if I was an attacker and I wanted to try and ensure I could get back in, this is one way I would try to hedge my bets.

By the way, the inspiration for this was a short training video by Brent Ozar (blog | twitter) called Blitz! 60 Minute SQL Server Takeover Script. As I watching his presentation yesterday, I saw that he was keying in on stored procedures that run at SQL Server startup. We use these things for server side traces, but it's also another way an attacker can keep his or her grubby fingers in SQL Server. During a penetration test where I was temporarily able to escalate rights, I created a stored procedure and marked it to run as startup. The stored procedure simply created my login again if it was missing and re-added me to the sysadmin fixed server role, thereby re-granting me the rights I desired. Upon a test of a server reboot (to simulate monthly patching), I was able to demonstrate I was back in.

If you're not familiar with how to set a stored procedure to run at startup, check out sp_procoption. More benign uses for this would be to start a server side trace if the default trace isn't doing it for you, to send a message that SQL Server restarted (but use database mail if you do this), etc. But just as an attacker could do something sinister using a stored procedure marked in this way, said attacker could also use SQL Agent to do the same. So make sure you check your SQL Agent for jobs designed to run at start up. Here is a T-SQL script that will detect what SQL Agent jobs have a schedule which sets them to run when SQL Agent starts:

SELECT j.name AS 'Job'

FROM msdb.dbo.sysschedules sched

JOIN msdb.dbo.sysjobschedules jsched
ON sched.schedule_id = jsched.schedule_id

JOIN msdb.dbo.sysjobs j
ON jsched.job_id = j.job_id

WHERE sched.freq_type = 64;






Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads