Auditing In SQL Server. SQL Saturday #486 - RVA Presented By Brad McKuhen

(1)

Auditing In SQL Server

SQL Saturday #486 - RVA

(2)

(3)

(4)

About Me



SQL Server DBA/Developer – 13 years



Lead DBA at Clutch Group



Contact Me:



Info at lakesidedba.com



@bradmckuhen

(5)

"All it takes is one bad day to reduce the sanest man alive

to lunacy...Just one bad day."

(6)

From the top . . .

Built In Logs

C2

Common Criteria Compliance

Default Trace

Server Settings

ExEv

Server Audit

Database Audit

CDC

TempDB

Ransack

WinMerge

(7)

Rotate Your Logs

Image Credit: http://d2rormqr1qwzpz.cloudfront.net/photos/2013/02/01/44394-owl_head.jpg

USE

msdb

;

GO

-- agent log cycling must be run from MSDB

EXEC

dbo

.

sp_cycle_agent_errorlog

;

GO

-- both log cycling SP's are SYSADMIN role only

EXEC

sp_cycle_errorlog

;

(8)

(9)

C2 - Setup

From MSDN:

Selecting this option will configure the server to record

both failed and successful attempts to access statements

and objects.

All of them.

Be very careful about using this.

-- turn it on

sp_configure 'show advanced options', 1 ;

GO

RECONFIGURE WITH OVERRIDE ;

GO

sp_configure 'c2 audit mode', 1 ;

GO

RECONFIGURE WITH OVERRIDE ;

GO

-- turn it off

sp_configure 'c2 audit mode', 0 ;

GO

RECONFIGURE WITH OVERRIDE ;

GO

sp_configure 'show advanced options', 0 ;

GO

RECONFIGURE WITH OVERRIDE;

GO

(10)

(11)

C3 – Common Criteria Compliance

(12)

C3 - Setup

Criteria Description Residual

Information Protection (RIP)

RIP requires a memory allocation to be overwritten with a known pattern of bits before memory is reallocated . . .

The ability to view login statistics

Each time a user successfully logs in to SQL Server, information about the last

successful login time, the last unsuccessful login time, and the number of attempts . . . is made available . . . query the

sys.dm_exec_sessions

DMV

That column GRANT should not override table DENY

After the common criteria compliance enabled option is enabled, a table-level DENY takes precedence over a column-level GRANT. When the option is not enabled, a column-level GRANT takes precedence over a table-level DENY.

Important

In addition to enabling the common criteria compliance enabled option, you also must download and run a script that finishes configuring SQL Server to comply with Common Criteria Evaluation Assurance Level 4+ (EAL4+). You can download this script from theMicrosoft SQL Server Common CriteriaWeb site.

(13)

(14)

C3 – Common Criteria Compliance, Results

Tons More

Information In

(15)

Default Trace - Setup

SELECT

*

FROM

sys.configurations

(16)

(17)

Built In Logs

C2

Common Criteria Compliance

Default Trace

Server Settings

ExEv

Server Audit

Database Audit

CDC

TempDB

Ransack

WinMerge

(18)

(19)

Extended Events - Setup

CREATE EVENT SESSION [Get All Statements] ON SERVER ADD EVENT sqlserver.rpc_completed

(ACTION(sqlos.task_time, sqlserver.database_id, sqlserver.database_name, sqlserver.is_system,

sqlserver.nt_username, sqlserver.session_id, sqlserver.sql_text, sqlserver.username) WHERE

([sqlserver].[is_system] = (0))), ADD EVENT sqlserver.sql_batch_completed (ACTION(sqlos.task_time,

sqlserver.database_id, sqlserver.database_name, sqlserver.is_system, sqlserver.nt_username,

sqlserver.session_id, sqlserver.sql_text, sqlserver.username) WHERE ([sqlserver].[is_system] = (0))), ADD EVENT sqlserver.sql_statement_completed (ACTION(sqlos.task_time, sqlserver.database_id,

sqlserver.database_name, sqlserver.is_system, sqlserver.nt_username, sqlserver.session_id,

sqlserver.sql_text, sqlserver.username) WHERE ([sqlserver].[is_system] = (0))) ADD TARGET package0.event_file (SET filename = N'C:\Program Files\Microsoft SQL

Server\MSSQL12.MSSQLSERVER\MSSQL\Log\Get All Statements.xel')

WITH (STARTUP_STATE = ON)

GO

ALTER EVENT SESSION [Get All Statements] ON SERVER STATE = START;

(20)

Extended Events - Results

SELECT

CAST(

event_data

AS XML

)

event_data

, *

FROM

sys

.

fn_xe_file_target_read_file

(

'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\Get

All Statements*.xel'

, NULL, NULL, NULL)

Courtesy of

https://www.brentozar.c

om/archive/2014/03/ex

tended-events-doesnt-hard/

One way, quite

manual:

(21)

(22)

(23)

Server Audit - Setup

CREATE SERVER AUDIT

[AuditForDemo]

TO FILE

(

FILEPATH

=

'C:\TEMP'

,

MAXSIZE

=

1 GB

,

MAX_ROLLOVER_FILES

=

2

,

RESERVE_DISK_SPACE

=

OFF

)

WITH

(

QUEUE_DELAY

=

1000

,

ON_FAILURE

=

CONTINUE

);

CREATE SERVER AUDIT

SPECIFICATION [ServerAuditSpecificationForDemo]

FOR SERVER AUDIT

[AuditForDemo] ADD

(

APPLICATION_ROLE_CHANGE_PASSWORD_GROUP

),

ADD

(

AUDIT_CHANGE_GROUP

),

ADD

(

BACKUP_RESTORE_GROUP

),

ADD

(

BROKER_LOGIN_GROUP

);

-- START THEM

ALTER SERVER AUDIT

[AuditForDemo] WITH

(

STATE

=

ON

);

GO

ALTER SERVER AUDIT

SPECIFICATION [ServerAuditSpecificationForDemo]

WITH

(

STATE

=

ON

);

(24)

DDL Tracked – The Table

CREATE DATABASE AuditSampleDB;

GO

--EXEC AuditSampleDB.dbo.sp_changedbowner 'contoso\ironman';

EXEC AuditSampleDB.dbo.sp_changedbowner 'sa';

GO

USE AuditSampleDB;

GO

CREATE TABLE dbo.DDLEvents (EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, EventType NVARCHAR(64) , EventDDL NVARCHAR(MAX), EventXML XML, DatabaseName NVARCHAR(255), SchemaName NVARCHAR(255), ObjectName NVARCHAR(255), [ObjectID] INT, HostName VARCHAR(64), IPAddress VARCHAR(32), ProgramName NVARCHAR(255),

(25)

DDL Tracked – The Trigger

CREATE TRIGGER ddl_trig_alter_db ON ALL SERVER

FOR ALTER_DATABASE, CREATE_DATABASE, DROP_DATABASE AS BEGIN DECLARE @WhatHappened XML;

SELECT @WhatHappened = EVENTDATA();

DECLARE @ip VARCHAR(32) = (SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id =

@@SPID);

INSERT AuditSampleDB.dbo.DDLEvents (EventType, EventDDL, EventXML, DatabaseName, SchemaName, ObjectName,

HostName, IPAddress, ProgramName, LoginName)

SELECT @WhatHappened.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),

@WhatHappened.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),

@WhatHappened, DB_NAME(), @WhatHappened.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),

@WhatHappened.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'), HOST_NAME(), @ip,

PROGRAM_NAME(), SUSER_SNAME();

END GO

(26)

(27)

(28)

(29)

Database Audit - Setup

CREATE DATABASE SampleDB;

GO

USE [SampleDB] GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecificationForDemo]

FOR SERVER AUDIT [AuditForDemo] ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP), ADD

(AUDIT_CHANGE_GROUP), ADD (BACKUP_RESTORE_GROUP), ADD (DATABASE_CHANGE_GROUP), ADD

(DATABASE_LOGOUT_GROUP), ADD (DATABASE_OBJECT_ACCESS_GROUP), ADD (DATABASE_OBJECT_CHANGE_GROUP);

GO

ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecificationForDemo] WITH (STATE = ON);

(30)

Database Audit – Get Results

SELECT

CAST(

additional_information

AS XML

)

AS

ADDLINFOXML

, *

FROM

sys

.

fn_get_audit_file

(

'C:\TEMP\AuditForDemo_44582E17-A397-4882-B30F-946F27B0B262_0_131024426446730000.sqlaudit'

,

DEFAULT

,

DEFAULT

)

WHERE

database_name

=

'AdventureWorks2014'

ORDER BY

EVENT_TIME DESC

;

(31)

(32)

(33)

(34)

(35)

(36)

(37)

(38)

(39)

(40)

(41)

(42)

Questions?

Contact Me:



Info at lakesidedba.com



@bradmckuhen

Microsoft SQL Server Common Criteria