Overview
In my previous article, we have discussed about Temporal Tables in SQL Server 2016, and understand much more about it. Now in this article we will discuss about triggers and will know how to create an audit trigger in SQL Server.
In many applications’ database, it is a very difficult task to find who did something and at what time was the activity performed on that application. However, in SQL Server it is easy to find someone’s activity at any time by using some special type of SQL objects like triggers.
What Are Triggers in SQL Server?
In SQL Server, a trigger is a special kind of stored procedure or database object that automatically executes and captures events or actions that a user performs on a SQL Server database. SQL Server comprises of two types of triggers:
- DML Triggers (Data Manipulation Language)
- DDL Triggers (Data definition Language)
DML Triggers: DML triggers applies when a user performs operations like INSERT, UPDATE, or DELETE in a specified table or view.
DDL Triggers: DDL triggers applies when a user performs operations such as CREATE, ALTER, and DROP. This kind of trigger is used for administrative tasks like; auditing database operations.
Now, after a small description of triggers in SQL Server we will focus on our main topic that is how to create an audit trigger in SQL Server.
Why Do We Need To Create An Audit Trigger?
The basic need to create an audit trigger in SQL Server is for security purpose and these things become evidence at the time of tampering:
- Client software that was used for accessing data
- Date and time, at which the change was occurring
- Data value, prior and after change
It means that all the above info works as evidence when a trigger is fired.
Now we will create an SQL Server Database audit trigger that will audit and store all the security related events of SQL Server.
USE [master]
GO
DROP TABLE [dbo].[SecurityLog]
GO
CREATE TABLE [dbo].[SecurityLog](
[EventType][nvarchar](128) NULL,
[EventTime][datetime] NULL,
[EventLog][xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Now we will create DDL trigger that will capture and store all connections and security-related events:
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'ddl_trig_capture_security_events')
DROP TRIGGER ddl_trig_capture_security_events
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_capture_security_events
ON ALL SERVER;
FOR LOGON, DDL_SERVER_SECURITY_EVENTS,
DDL_DATABASE_SECURITY_EVENTS
AS
INSERT INTO [master]..[SecurityLog] (EventType, EventTime, EventLog)
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(128)')
,EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
,EVENTDATA()
GO
Once you create an audit trigger, you can test it using the following command:
USE [master] GO CREATE LOGIN [TestDDL] WITH PASSWORD=N'TestDDL' GO USE [AdventureWorks2012] GO CREATE USER [TestDDL] FOR LOGIN [TestDDL] GO ALTER ROLE [db_datareader] ADD MEMBER [TestDDL] GO GRANT EXECUTE ON [dbo].[uspGetBillOfMaterials] TO [TestDDL] GO
SQL Server database audit trigger for event notification
USE [msdb] GO --Creating queue CREATE QUEUE [SecurityEventsQueue] GO --Creating service for the queue CREATE SERVICE [//AdventureWorks.com/SecurityEventsService] AUTHORIZATION [dbo] ON QUEUE [dbo].[SecurityEventsQueue] GO
Creating route for the service
CREATE ROUTE SecurityEventsRoute WITH SERVICE_NAME = '//AdventureWorks.com/SecurityEventsService', ADDRESS = 'LOCAL'; GO
Creating Event Notification to capture connection and secrity-related events
USE [msdb]
GO
CREATE EVENT NOTIFICATION NotifySecurityEvents
ON SERVER
FOR AUDIT_LOGIN,
AUDIT_LOGOUT,
AUDIT_LOGIN_FAILED,
DDL_SERVER_SECURITY_EVENTS,
DDL_DATABASE_SECURITY_EVENTS
TO SERVICE '//AdventureWorks.com/SecurityEventsService' ,
'9D584F73-1796-4494-ADC2-04BDD729FBCE';
GO
We will create a service program that will process the event messages that are generated via Event Notification objects:
IF EXISTS (SELECT * FROM [sys].[objects] WHERE [name] = 'sProcessSecurityEvents') DROP PROCEDURE [dbo].[sProcessSecurityEvents] GO
CREATE PROC [dbo].[sProcessSecurityEvents]
AS BEGIN
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
BEGIN TRY
DECLARE @message_body [xml]
,@EventTime [datetime]
,@EventType [varchar](128)
,@message_type_name [nvarchar](256) ,@dialog [uniqueidentifier]
Endless loop
WHILE (1 = 1) BEGIN BEGIN TRANSACTION ;
Receive the next available message
WAITFOR (RECEIVE TOP(1)
@message_type_name = [message_type_name],
@message_body = [message_body],
@dialog = [conversation_handle]
FROM [dbo].[SecurityEventsQueue]), TIMEOUT 2000
Rollback and exit if no messages were found
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END;
End conversation of end dialog message
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + CAST(@dialog as [nvarchar](40));
END CONVERSATION @dialog;
END;
ELSE
BEGIN
SET @EventTime = CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS [nvarchar](max)) AS [datetime])
SET @EventType = CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS [nvarchar](128))
INSERT INTO [master]..[SecurityLog] ([EventType], [EventTime], [EventLog])
VALUES (@EventType, @EventTime, @message_body)
END
COMMIT TRANSACTION
END --End of loop
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()
,ERROR_SEVERITY()
,ERROR_STATE()
,ERROR_PROCEDURE()
,ERROR_LINE()
,ERROR_MESSAGE()
END CATCH
END
GO
After successfully created service program, execute the following script to activate service broker queue:
ALTER QUEUE [dbo].[SecurityEventsQueue]
WITH STATUS = ON
,ACTIVATION (PROCEDURE_NAME = [sProcessSecurityEvents]
,STATUS = ON
,MAX_QUEUE_READERS = 1
,EXECUTE AS OWNER)
GO
Now test and examine the output of the SecurityLog table.
To track the security-related event we can also use SQL Server audit.
USE [master]
GO
CREATE SERVER AUDIT [Audit-SecurityEvents]
TO FILE
( FILEPATH = N'D:\Demo_SQLAudit'
,MAXSIZE = 200 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF )
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE )
GO
After this now, we will create an audit specification to audit our SQL server and database security-related events like Login Password change, Database user or object permission change, failed login attempts and server principle change:
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification] FOR SERVER AUDIT [Audit-SecurityEvents] ADD (LOGIN_CHANGE_PASSWORD_GROUP), ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP), ADD (FAILED_LOGIN_GROUP), ADD (SERVER_PRINCIPAL_CHANGE_GROUP) GO
Conclusion
In this section, we learnt about how SQL Server uses DML and DDL triggers for security purposes. These DML and DDL triggers capture user events that they performed on SQL Server. Although some SQL features such as SQL Audit are not yet fully developed, auditing of SQL server database using DDL triggers have several disadvantages. For example, a user can easily disable the triggers if he has sufficient permissions. Another disadvantage of using trigger in SQL Server is that triggers perform a schema change and then delete the captured information from auditing repository. In addition, the procedure to create an audit trigger in SQL Server is too long and time consuming. Therefore, it is better to use SQL log viewer tool, which saves a significant amount of valuable time and provides you a convenient result.
