T-SQL Tuesday #53-Matt's Making Me Do This!
- by Most Valuable Yak (Rob Volk)
Hello everyone! It's that time again, time for T-SQL Tuesday, the wonderful blog series started by Adam Machanic (b|t).
This month we are hosted by Matt Velic (b|t) who asks the question, "Why So Serious?", in celebration of April Fool's Day. He asks the contributors for their dirty tricks. And for some reason that escapes me, he and Jeff Verheul (b|t) seem to think I might be able to write about those. Shocked, I am!
Nah, not really. They're absolutely right, this one is gonna be fun!
I took some inspiration from Matt's suggestions, namely Resource Governor and Login Triggers. I've done some interesting login trigger stuff for a presentation, but nothing yet with Resource Governor. Best way to learn it!
One of my oldest pet peeves is abuse of the sa login. Don't get me wrong, I use it too, but typically only as SQL Agent job owner. It's been a while since I've been stuck with it, but back when I started using SQL Server, EVERY application needed sa to function. It was hard-coded and couldn't be changed. (welllllll, that is if you didn't use a hex editor on the EXE file, but who would do such a thing?)
My standard warning applies: don't run anything on this page in production. In fact, back up whatever server you're testing this on, including the master database. Snapshotting a VM is a good idea. Also make sure you have other sysadmin level logins on that server.
So here's a standard template for a logon trigger to address those pesky sa users:
CREATE TRIGGER SA_LOGIN_PRIORITY ON ALL SERVER
WITH ENCRYPTION, EXECUTE AS N'sa'
AFTER LOGON AS
IF ORIGINAL_LOGIN()<>N'sa' OR APP_NAME() LIKE N'SQL Agent%' RETURN;
-- interesting stuff goes here
GO
What can you do for "interesting stuff"? Books Online limits itself to merely rolling back the logon, which will throw an error (and alert the person that the logon trigger fired). That's a good use for logon triggers, but really not tricky enough for this blog. Some of my suggestions are below:
WAITFOR DELAY '23:59:59';
Or:
EXEC sp_MSforeach_db 'EXEC sp_detach_db ''?'';'
Or:
EXEC msdb.dbo.sp_add_job @job_name=N'`', @enabled=1, @start_step_id=1, @notify_level_eventlog=0, @delete_level=3;
EXEC msdb.dbo.sp_add_jobserver @job_name=N'`', @server_name=@@SERVERNAME;
EXEC msdb.dbo.sp_add_jobstep @job_name=N'`', @step_id=1, @step_name=N'`', @command=N'SHUTDOWN;';
EXEC msdb.dbo.sp_start_job @job_name=N'`';
Really, I don't want to spoil your own exploration, try it yourself! The thing I really like about these is it lets me promote the idea that "sa is SLOW, sa is BUGGY, don't use sa!". Before we get into Resource Governor, make sure to drop or disable that logon trigger. They don't work well in combination. (Had to redo all the following code when SSMS locked up)
Resource Governor is a feature that lets you control how many resources a single session can consume. The main goal is to limit the damage from a runaway query. But we're not here to read about its main goal or normal usage! I'm trying to make people stop using sa BECAUSE IT'S SLOW! Here's how RG can do that:
USE master;
GO
CREATE FUNCTION dbo.SA_LOGIN_PRIORITY()
RETURNS sysname
WITH SCHEMABINDING, ENCRYPTION AS
BEGIN
RETURN CASE
WHEN ORIGINAL_LOGIN()=N'sa' AND APP_NAME() NOT LIKE N'SQL Agent%'
THEN N'SA_LOGIN_PRIORITY'
ELSE N'default' END
END
GO
CREATE RESOURCE POOL SA_LOGIN_PRIORITY
WITH (
MIN_CPU_PERCENT = 0 ,MAX_CPU_PERCENT = 1
,CAP_CPU_PERCENT = 1 ,AFFINITY SCHEDULER = (0)
,MIN_MEMORY_PERCENT = 0 ,MAX_MEMORY_PERCENT = 1
-- ,MIN_IOPS_PER_VOLUME = 1 ,MAX_IOPS_PER_VOLUME = 1 -- uncomment for SQL Server 2014
);
CREATE WORKLOAD GROUP SA_LOGIN_PRIORITY
WITH
( IMPORTANCE = LOW
,REQUEST_MAX_MEMORY_GRANT_PERCENT = 1
,REQUEST_MAX_CPU_TIME_SEC = 1
,REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 1
,MAX_DOP = 1
,GROUP_MAX_REQUESTS = 1 )
USING SA_LOGIN_PRIORITY;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.SA_LOGIN_PRIORITY);
ALTER RESOURCE GOVERNOR RECONFIGURE;
From top to bottom:
Create a classifier function to determine which pool the session should go to. More info on classifier functions.
Create the pool and provide a generous helping of resources for the sa login.
Create the workload group and further prioritize those resources for the sa login.
Apply the classifier function and reconfigure RG to use it.
I have to say this one is a bit sneakier than the logon trigger, least of all you don't get any error messages. I heartily recommend testing it in Management Studio, and click around the UI a lot, there's some fun behavior there. And DEFINITELY try it on SQL 2014 with the IO settings included! You'll notice I made allowances for SQL Agent jobs owned by sa, they'll go into the default workload group. You can add your own overrides to the classifier function if needed.
Some interesting ideas I didn't have time for but expect you to get to before me:
Set up different pools/workgroups with different settings and randomize which one the classifier chooses
Do the same but base it on time of day (Books Online example covers this)...
Or, which workstation it connects from. This can be modified for certain special people in your office who either don't listen, or are attracted (and attractive) to you.
And if things go wrong you can always use the following from another sysadmin or Dedicated Admin connection:
ALTER RESOURCE GOVERNOR DISABLE;
That will let you go in and either fix (or drop) the pools, workgroups and classifier function.
So now that you know these types of things are possible, and if you are tired of your team using sa when they shouldn't, I expect you'll enjoy playing with these quite a bit!
Unfortunately, the aforementioned Dedicated Admin Connection kinda poops on the party here. Books Online for both topics will tell you that the DAC will not fire either feature. So if you have a crafty user who does their research, they can still sneak in with sa and do their bidding without being hampered.
Of course, you can still detect their login via various methods, like a server trace, SQL Server Audit, extended events, and enabling "Audit Successful Logins" on the server. These all have their downsides: traces take resources, extended events and SQL Audit can't fire off actions, and enabling successful logins will bloat your error log very quickly. SQL Audit is also limited unless you have Enterprise Edition, and Resource Governor is Enterprise-only. And WORST OF ALL, these features are all available and visible through the SSMS UI, so even a doofus developer or manager could find them.
Fortunately there are Event Notifications!
Event notifications are becoming one of my favorite features of SQL Server (keep an eye out for more blogs from me about them). They are practically unknown and heinously underutilized. They are also a great gateway drug to using Service Broker, another great but underutilized feature. Hopefully this will get you to start using them, or at least your enemies in the office will once they read this, and then you'll have to learn them in order to fix things.
So here's the setup:
USE msdb;
GO
CREATE PROCEDURE dbo.SA_LOGIN_PRIORITY_act
WITH ENCRYPTION AS
DECLARE @x XML, @message nvarchar(max);
RECEIVE @x=CAST(message_body AS XML) FROM SA_LOGIN_PRIORITY_q;
IF @x.value('(//LoginName)[1]','sysname')=N'sa' AND @x.value('(//ApplicationName)[1]','sysname') NOT LIKE N'SQL Agent%'
BEGIN
-- interesting activation procedure stuff goes here
END
GO
CREATE QUEUE SA_LOGIN_PRIORITY_q
WITH STATUS=ON, RETENTION=OFF,
ACTIVATION (PROCEDURE_NAME=dbo.SA_LOGIN_PRIORITY_act, MAX_QUEUE_READERS=1, EXECUTE AS OWNER);
CREATE SERVICE SA_LOGIN_PRIORITY_s ON QUEUE SA_LOGIN_PRIORITY_q([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
CREATE EVENT NOTIFICATION SA_LOGIN_PRIORITY_en ON SERVER WITH FAN_IN
FOR AUDIT_LOGIN
TO SERVICE N'SA_LOGIN_PRIORITY_s', N'current database'
GO
From top to bottom:
Create activation procedure for event notification queue.
Create queue to accept messages from event notification, and activate the procedure to process those messages when received.
Create service to send messages to that queue.
Create event notification on AUDIT_LOGIN events that fire the service.
I placed this in msdb as it is an available system database and already has Service Broker enabled by default. You should change this to another database if you can guarantee it won't get dropped.
So what to put in place for "interesting activation procedure code"? Hmmm, so far I haven't addressed Matt's suggestion of writing a lengthy script to send an annoying message:
SET @[email protected]('(//HostName)[1]','sysname') +
N' tried to log in to server ' + @x.value('(//ServerName)[1]','sysname') +
N' as SA at ' + @x.value('(//StartTime)[1]','sysname') +
N' using the ' + @x.value('(//ApplicationName)[1]','sysname') +
N' program. That''s why you''re getting this message and the attached pornography which' +
N' is bloating your inbox and violating company policy, among other things. If you know' +
N' this person you can go to their desk and hit them, or use the following SQL to end their session:
KILL ' + @x.value('(//SPID)[1]','sysname') +
N';
Hopefully they''re in the middle of a huge query that they need to finish right away.'
EXEC msdb.dbo.sp_send_dbmail @recipients=N'[email protected]', @subject=N'SA Login Alert', @query_result_width=32767,
@body=@message, @query=N'EXEC sp_readerrorlog;', @attach_query_result_as_file=1, @query_attachment_filename=N'UtterlyGrossPorn_SeriouslyDontOpenIt.jpg'
I'm not sure I'd call that a lengthy script, but the attachment should get pretty big, and I'm sure the email admins will love storing multiple copies of it. The nice thing is that this also fires on Dedicated Admin connections! You can even identify DAC connections from the event data returned, I leave that as an exercise for you. You can use that info to change the action taken by the activation procedure, and since it's a stored procedure, it can pretty much do anything!
Except KILL the SPID, or SHUTDOWN the server directly. I'm still working on those.
