Alerts for SQL Server security events by serverku

Security is the main thing for servers and SQL servers and we need to trace it for security management. Have to need track who is using it, what they are doing and when they performed. For SQL Server 2005 and later versions we have some DDL events which can help us to achieve this. So let us catch it up. But before going ahead, please visit DDL Trigger and DDL Auditing in SQL Server 2005 and Logon trigger in SQL Server for more information about DDL trigger.
I am presenting here for DDL triggers fire for security events. We can also log all the event data in the table, but I want to describe this post to get al alert for this security event's occurrence. To implement I am creating a DDL trigger in the master database and evaluate for some scenario like login creation, Add server roles and drop logins. You can find the trigger created for the same as following,

CREATE TRIGGER [Trg_TrackLoginManagement]
ON ALL SERVER
FOR 
 DDL_SERVER_SECURITY_EVENTS
AS
BEGIN
SET NOCOUNT ON

DECLARE @data xml,
              @EventType varchar(100),
              @EventTime datetime,
              @ServerName varchar(100),
              @AffectedLoginName varchar(100),
              @WhoDidIt varchar(100),
              @EmailSubject varchar(500),
              @EmailBody varchar(800),
              @EmailRecipients varchar(300),
              @TSQL varchar(4000)
 
SET @EmailRecipients = 'prajapatipareshm@gmail.com'
 
SET @data = EVENTDATA()
SET @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
SET @EventTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
SET @ServerName = @data.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(100)')
SET @AffectedLoginName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)')
SET @WhoDidIt = @data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(100)')
SET @TSQL = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(4000)')

SET @EmailSubject =  @EventType + ' occured by ' + @WhoDidIt + ' on ' +
      @ServerName + ' occured at: ' + convert(Varchar, @EventTime) 
SET @EmailBody = @TSQL 

EXEC msdb.dbo.sp_send_dbmail  
    @recipients = @EmailRecipients
  , @subject = @EmailSubject  
  , @body = @EmailBody  
  , @profile_name = '<ProfileName>' -- Put profile name here
  , @body_format = 'HTML' ;  

END

We are capturing some data which is full information of login name , the events occurred and the date on which it occurred and who did. I collected some snaps after performing and testing some scenario for creating a login, assigning server roles and finally deleting created login after testing, Let me share here.

Above are the alert emails which I received for the events happened to create login, server role assignment and after all deleting the login. If we do not want to continue receiving the alerts for change, then it DDL trigger on the server can be disabled with following statement.

DISABLE TRIGGER [Trg_TrackLoginManagement] ON ALL SERVER
GO

Did you configure any alerts for such DDL events? Share your thoughts here.

Logon trigger in SQL Server by serverku

As a secure part, recently I worked with login details and the auditing for the same. I needed to capture each event for logging statistics like login name, the time when logon, the program through established connection, session and host or client IP. This event is raised when user sessions make connections with SQL Server instances where we configured LOGON trigger. This will helpful for us to make auditing the logon details for each connections. For the small demo let us create required objects and implement it.

USE DemoDB
GO
-- Creating audit table
CREATE TABLE LogonAuditing
(
 SessionId int,
 LogonTime datetime,
 HostName varchar(50),
 ProgramName varchar(500),
 LoginName varchar(50),
 ClientHost varchar(50)
)
GO

USE Master
GO
-- Creating DDL trigger for logon
CREATE TRIGGER LogonAuditTrigger
 ON ALL SERVER 
FOR LOGON
AS
BEGIN
       DECLARE @LogonTriggerData xml,
                     @EventTime datetime,
                     @LoginName varchar(50),
                     @ClientHost varchar(50),
                     @LoginType varchar(50),
                     @HostName varchar(50),
                     @AppName varchar(500)
                     
       SET @LogonTriggerData = eventdata()
  
    SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
       SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
       SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
       SET @HostName = HOST_NAME()
       SET @AppName = APP_NAME()--,program_name()
 
 INSERT INTO DemoDB.dbo.LogonAuditing
  (
  SessionId,
  LogonTime,
  HostName,
  ProgramName,
  LoginName,
  ClientHost
 )
 SELECT 
  @@spid,
  @EventTime,
  @HostName,
  @AppName,
  @LoginName,
  @ClientHost

END
GO

Audit table and the trigger to fill that table is created. Now it is time to evaluate, test it and review the audit table.

We have all details of the logging events from the table. Also one more interesting thing is as we can prevent unwanted user logins and connections to SQL Server. This prohibition can be for the login, program or host, for that we just need to add some code in logon trigger for conditions to make rollback at that time like following,

-- Preventing 'sa' login
IF @LoginName = 'sa'
BEGIN
ROLLBACK;
END
  
-- Preventing the connections from SSMS
IF @AppName = 'Microsoft SQL Server Management Studio'
BEGIN
ROLLBACK;
END

For first criteria to prevent login ‘SA’, if connection made for the same and the message fired at the time of event logging,

You can make your criteria as per requirement as I implemented for login and program connections. Are you using a Logon Trigger?