Different ways to audit SQL Server security

Login Auditing

SQL Server login auditing is a legacy form of auditing that can be used to record failed and successful login attempts on the server. Login auditing writes to the error log.

Recording connection attempts is useful to be able to get answers to questions about who is attempting to connect to the database, whether an attack is currently taking place or whether an attempted attack was successful.

The login auditing can be configured as follows:

• Right-click SQL Server in Object Explorer; choose Properties.
• Click Security.
• Set the login auditing property. Choose from: none, failed logins only, successful logins only or both failed and successful logins.
• Click OK and restart SQL Server.

C2 auditing and Common Compliance Criteria

You can enable C2 Audit Mode to log failed and successful attempts to access statements and objects. C2 Audit mode saves a lot of data, so the files tend to get very large. When a file reaches 200 MB, SQL Server opens a new file. If the data directory in which logs are being saved runs out of space, SQL Server will shut itself down. To enable C2 Audit Mode:

• Right-click the SQL Server in Object Explorer and choose Properties.
• Click Security and then check Enable C2 audit tracing.
• Click OK.

Alternatively you can use sp_configure to enable C2 audit tracing.

Please note that while the C2 security standard is still available in SQL Server 2012, it is being replaced by Common Compliance Criteria and will probably be removed from future versions of SQL Server.

Common Compliance Criteria is an auditing standard that also uses SQL Trace to capture data. After the common compliance criteria option is enabled, login auditing is also enabled. To enable Common Compliance Criteria:

• Right-click the SQL Server in Object Explorer and choose Properties.
• Click Security and then check Enable C2 audit tracing.
• Click OK.

SQL Profiler Trace security audit events can be used to track any object access and login configuration and security events. Results are stored in trace files or database table. For more information about how to create SQL Trace, visit Create a Trace (SQL Server Profiler).

Data Definition Language (DDL) triggers

DDL triggers can be used to log information about DDL and SQL Server security-related events to a table. Logging DDL events provides you with an audit trail and potential warning of an elevation of privilege attack, a user who's been assigned too many permissions or a user who's misusing their permissions.

You can specify the scope as Server or Database when you specify the trigger. Server scope triggers fire (execute) in response to server object events (objects such as logins). Database scope triggers fire in response to database object events, such as schemas, tables and views. The EventData function provides detailed information about the DDL or security-related event that caused the DDL trigger to fire. The EventData function returns a value of type xml. The schema differs depending on the type of event.

The following code creates a DDL trigger on the server that will audit and store all connections and security-related events that occur on SQL Server to a SecurityLog table inside master database:

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 create a DDL trigger (ddl_trig_capture_security_events) to 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 the trigger has been created, you can test to see if it is working:

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

Click on xml to view the full event log:

The following figure shows the output when you choose to view the log table:

Event Notification

Event Notifications were introduced in SQL Server 2005 and offer the ability to collect a very specific subset of SQL Trace, DDL and security-related events through a Service Broker and queue. Event Notification allows automated processing of the events asynchronously (outside the scope of a transaction) through Service Broker. Event notifications can offer a programming alternative to DDL triggers and SQL Trace.

For more information, see Understanding Event Notifications vs. Triggers.

In the following example, we will audit and store all connections and security-related events that will occur on SQL Server using 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]
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
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

--Creating the service program that will process the event messages that is

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

--Once service program is created successfully, execute the following script to

--activate our service broker queue and reference this Service Program stored procedure:

ALTER QUEUE [dbo].[SecurityEventsQueue]        
        WITH STATUS = ON       
        ,ACTIVATION (PROCEDURE_NAME = [sProcessSecurityEvents]       
        ,STATUS = ON       
        ,MAX_QUEUE_READERS = 1       
         ,EXECUTE AS OWNER)
GO

Now test the Event Notification setup and then examine the output of the SecurityLog table.

SQL Server Audit

We can also use SQL Server audit to track the security-related event. SQL Server audit is a SQL Server Enterprise Edition feature, which has been available since SQL Server 2008. SQL Server audit uses extended events to help perform an audit.

For the example, we will create audit specifications to audit all security-related events. To do that, we will first create a file-based server audit, which we will use for our server audit specification:

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

We will now create an audit specification to audit server and database security-related events such as 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

To test your audit specification, generate some events and then view the audit file to ensure that the events have been audited (see below):