February 5, 2008 at 8:45 am
We have some network/hardware group users that keep connecting to database servers using domain admin accounts instead of their personal windows accounts to do administrative tasks.
This is causing a nightmare when trying to audit these servers. I've developed a trigger on the restorehistory system table to catch people in the act and figure out where they are doing it from. I've tested it in a development environment and it works, but I wonder what opinions you all will have about applying a trigger on a system table.
Besides the obvious reasons like any upgrades/changes to Sql Server will likely blow away this trigger, can you think of anything it will hurt?
(Why are the network guys doing these tasks in the first place? Good question. Very long story...)
Any opinions would be appriciated.
--------------------------------------------------------
USE MSDB
GO
CREATE TRIGGER tr_Restore_Monitor
ON dbo.restorehistory
AFTER INSERT
AS
DECLARE @SEND_TXT VARCHAR(512)
DECLARE @MSG_TXT VARCHAR(255)
SET @MSG_TXT = SUSER_SNAME() + ' ' + HOST_NAME()
IF SUSER_SNAME() IN ('DOMAIN\ROOTUSER', 'DOMAIN\SRVADMIN')
BEGIN
-- CLIENT THAT DID THE RESTORE
SET @SEND_TXT =
'NET SEND ' + HOST_NAME() + ' RESTORE OF A DATABASE DETECTED USING '
+ SUSER_SNAME() + ' FROM: ' + HOST_NAME() +
' THIS IS A VIOLATION OF POLICY. THIS ACTION HAS BEEN LOGGED. ONLY USE
YOUR WINDOWS ACCOUNT TO CONNECT TO DATABASE SERVERS!'
EXECUTE master.dbo.xp_cmdshell @SEND_TXT
-- DBA'S COMPUTER
SET @SEND_TXT =
'NET SEND RESTORE OF A DATABASE DETECTED USING '
+ SUSER_SNAME() + ' FROM: ' + HOST_NAME() +
' THIS IS A VIOLATION OF POLICY. THIS ACTION HAS BEEN LOGGED. ONLY USE
YOUR WINDOWS ACCOUNT TO CONNECT TO DATABASE SERVERS!'
EXECUTE master.dbo.xp_cmdshell @SEND_TXT
-- Create Log
-- (CODE OMITTED TO SAVE SPACE)
END -- END IF
GO
The Redneck DBA
February 5, 2008 at 9:26 am
Your better answer is to use profiler and create a trace on those users. Create a standard trace and filter on NTUserName (can't remember the exact column name) . This way you will be able to tell exactly what commands they are running .. and best of all what machine they are running the commands from.
Of course the other option (and potentialy more fun) is to remove access to the domain/admin accounts and wait and see who screams :). Then when they come to you complaining you can ask them why on earth they are using those accounts in the first place.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 5, 2008 at 9:38 am
Agreed. We have Idera's compliance manager that catches everything for us. It gives us everthing this script gives us and then some...This little net send popup is just intended to scare people more than anything.
And I can't disable the account's RESTORE DATABASE rights because it is what the sql server agent is running as, and there are some legitimate jobs that restore databases that it runs. Perhaps I should just change the password and see who notices 🙂
The Redneck DBA
February 5, 2008 at 9:47 am
One very important thing you should remember (that I just thought of). Its a very bad idea for the DBA to tick off the Network/Hardware guys :).
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 5, 2008 at 9:51 am
🙂 Except for in my case we both report to the same person, who usually sides with the DBAs.
The Redneck DBA
February 5, 2008 at 11:02 am
Or use a DDL trigger
February 5, 2008 at 11:43 pm
If you're on SP2, use a login trigger. I've got one on my main server to prevent exactly this - users logging in using accounts they're not supposed to.
Short sample from my trigger
CREATE TRIGGER TheLoginTrigger
ON ALL SERVER
FOR LOGON AS
BEGIN
IF APP_NAME() like 'Microsoft SQL Server Management Studio%'
IF Original_Login() = 'The restricted account'
......
Couple things to beware of with login triggers. Try not to access any databases other than master. If you do and the database gets dropped, goes suspect, is detached, ... then the trigger will throw an error. If the trigger throws an error of severity 16 or higher, logins will be rolled back. This may mean that no one can get into the server.
Connections via the DAC still trigger logon triggers. Only thing that doesn't is a connection made when SQL Server is started single user mode with minimal config.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2008 at 7:24 am
Honestly sounds really cool, and really scary at the same time :).
I can see that as being really useful to enforce that a backup login (for instance) is only used by your backup utility.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 6, 2008 at 10:26 pm
Kenneth Fisher (2/6/2008)
Honestly sounds really cool, and really scary at the same time :).
It's both. We've had one incident when one of my colleagues detached the database that the trigger was logging to. Instant DoS. fortunatly he still had a connection open and was able to disable the trigger. Still resulted in a phone call 8pm one saturday night.
Since then I've modified it so that it calls a proc in master to do the logging, and checks DB existance before that. No issues now.
I can see that as being really useful to enforce that a backup login (for instance) is only used by your backup utility.
Which is what I'm using it for. To ensure that logins that belong to other apps are coming from those apps and not some bright-spark developer that thinks he's found a way round the security.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply