Welcome 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:

• Understanding Kerberos, Part I
• Understanding Kerberos, Part II

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;