Different ways to audit SQL Server security
Strong SQL Server security has become a more important part of a robust defense. But what are your options for security auditing?
With strengthened security management guidance from the industry, Microsoft is building more security and auditing...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
features into SQL Server. These SQL Server auditing features not only help DBAs generate audit trails of schema changes and security-related events, but also allow companies to meet various regulatory compliance requirements such as the Gramm-Leach-Bliley Act (GLBA), the European Union Data Protection Directive (EUDPD), the Health Insurance Portability and Accountability Act (HIPAA), the Payment Card Industry Data Security Standard (PCI DSS), and the Sarbanes-Oxley Act (SOX). In addition to helping organizations meet regulatory compliance requirements, these auditing features also enhance their data and database infrastructure 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.
For more on SQL Server security:
SQL Server security test checklist
SQL Server security vulnerabilities you may be overlooking
SQL Server security in the physical world vs virtual world
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):