-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathConfigureLoginAudit.sql
More file actions
201 lines (178 loc) · 8.25 KB
/
ConfigureLoginAudit.sql
File metadata and controls
201 lines (178 loc) · 8.25 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
/*
Script: ConfigureLoginAudit.sql
Description: Creates a SQL Server Audit and Server Audit Specification to
track successful logins, failed logins, and audit configuration
changes. Tears down any existing audit objects with the same name
before recreating them with sensible file-based settings
(100 MB max size, 10 rollover files). Finishes with a
verification query against the audit catalog views.
Prerequisites: SQL Server 2012+
Permissions: ALTER ANY SERVER AUDIT, CONTROL SERVER or sysadmin
The target audit file directory must already exist on disk
Database: master
Configuration: @FilePath - Directory for audit files (must exist)
@AuditName - Name of the server audit
@SpecificationName - Name of the server audit specification
@MaxSizeMB - Maximum size of each audit file in MB
@MaxRolloverFiles - Number of rollover files to retain
@ExcludedLogins - Comma-delimited list of logins to exclude
from auditing (e.g. service accounts)
Usage Notes: - If an audit and specification with the configured names already
exist they will be disabled and dropped before being recreated.
- The ON_FAILURE option is set to CONTINUE so that a full audit
log does not block logins. Change to SHUTDOWN if compliance
requirements demand guaranteed capture.
- To exclude service accounts from the audit, populate the
@ExcludedLogins variable with a comma-delimited list.
License: MIT License - https://opensource.org/licenses/MIT
Source: https://github.com/mbentham/sql-server-scripts
*/
USE master;
GO
----------------------------------------------------------------------
-- Configuration
----------------------------------------------------------------------
DECLARE @FilePath NVARCHAR(260) = N'C:\SQLAudit\'; -- Directory for audit files (must exist on the server)
DECLARE @AuditName NVARCHAR(128) = N'login_audit'; -- Name of the server audit
DECLARE @SpecificationName NVARCHAR(128) = N'track_all_logins'; -- Name of the server audit specification
DECLARE @MaxSizeMB INT = 100; -- Maximum size per audit file (MB)
DECLARE @MaxRolloverFiles INT = 10; -- Number of rollover files to retain
DECLARE @ExcludedLogins NVARCHAR(MAX) = N''; -- Comma-delimited logins to exclude (e.g. N'SvcAccount1,SvcAccount2')
----------------------------------------------------------------------
-- Teardown existing objects (if any)
----------------------------------------------------------------------
-- Disable and drop the existing audit specification
IF EXISTS (SELECT 1 FROM sys.server_audit_specifications WHERE name = @SpecificationName)
BEGIN
PRINT 'WARNING: Existing audit specification [' + @SpecificationName + '] will be dropped and recreated. There will be a brief gap in audit coverage.';
DECLARE @DisableSpec NVARCHAR(MAX) = N'ALTER SERVER AUDIT SPECIFICATION '
+ QUOTENAME(@SpecificationName) + N' WITH (STATE = OFF);';
EXEC sp_executesql @DisableSpec;
DECLARE @DropSpec NVARCHAR(MAX) = N'DROP SERVER AUDIT SPECIFICATION '
+ QUOTENAME(@SpecificationName) + N';';
EXEC sp_executesql @DropSpec;
END
-- Disable and drop the existing audit
IF EXISTS (SELECT 1 FROM sys.server_audits WHERE name = @AuditName)
BEGIN
PRINT 'WARNING: Existing audit [' + @AuditName + '] will be dropped and recreated.';
DECLARE @DisableAudit NVARCHAR(MAX) = N'ALTER SERVER AUDIT '
+ QUOTENAME(@AuditName) + N' WITH (STATE = OFF);';
EXEC sp_executesql @DisableAudit;
DECLARE @DropAudit NVARCHAR(MAX) = N'DROP SERVER AUDIT '
+ QUOTENAME(@AuditName) + N';';
EXEC sp_executesql @DropAudit;
END
----------------------------------------------------------------------
-- Create the server audit
----------------------------------------------------------------------
DECLARE @CreateAuditSQL NVARCHAR(MAX);
SET @CreateAuditSQL = N'CREATE SERVER AUDIT ' + QUOTENAME(@AuditName) + N'
TO FILE
(
FILEPATH = ' + QUOTENAME(@FilePath, '''') + N',
MAXSIZE = ' + CAST(@MaxSizeMB AS NVARCHAR(10)) + N' MB,
MAX_ROLLOVER_FILES = ' + CAST(@MaxRolloverFiles AS NVARCHAR(10)) + N'
)
WITH (ON_FAILURE = CONTINUE)';
-- Build optional WHERE clause to exclude service accounts
IF @ExcludedLogins IS NOT NULL AND LEN(LTRIM(RTRIM(@ExcludedLogins))) > 0
BEGIN
DECLARE @WhereClause NVARCHAR(MAX) = N'';
DECLARE @Login NVARCHAR(256);
DECLARE @Remainder NVARCHAR(MAX) = LTRIM(RTRIM(@ExcludedLogins));
DECLARE @CommaPos INT;
DECLARE @First BIT = 1;
WHILE LEN(@Remainder) > 0
BEGIN
SET @CommaPos = CHARINDEX(',', @Remainder);
IF @CommaPos = 0
BEGIN
SET @Login = LTRIM(RTRIM(@Remainder));
SET @Remainder = N'';
END
ELSE
BEGIN
SET @Login = LTRIM(RTRIM(LEFT(@Remainder, @CommaPos - 1)));
SET @Remainder = LTRIM(RTRIM(SUBSTRING(@Remainder, @CommaPos + 1, LEN(@Remainder))));
END
IF LEN(@Login) > 0
BEGIN
IF @First = 1
BEGIN
SET @WhereClause = N'
WHERE server_principal_name <> ' + QUOTENAME(@Login, '''');
SET @First = 0;
END
ELSE
BEGIN
SET @WhereClause = @WhereClause + N'
AND server_principal_name <> ' + QUOTENAME(@Login, '''');
END
END
END
SET @CreateAuditSQL = @CreateAuditSQL + @WhereClause;
END
SET @CreateAuditSQL = @CreateAuditSQL + N';';
BEGIN TRY
EXEC sp_executesql @CreateAuditSQL;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
RAISERROR('ERROR creating server audit: %s', 16, 1, @ErrorMessage);
RETURN;
END CATCH
----------------------------------------------------------------------
-- Create the server audit specification
----------------------------------------------------------------------
DECLARE @CreateSpecSQL NVARCHAR(MAX) = N'CREATE SERVER AUDIT SPECIFICATION '
+ QUOTENAME(@SpecificationName) + N'
FOR SERVER AUDIT ' + QUOTENAME(@AuditName) + N'
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);';
BEGIN TRY
EXEC sp_executesql @CreateSpecSQL;
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE();
RAISERROR('ERROR creating audit specification: %s', 16, 1, @ErrorMessage);
RETURN;
END CATCH
----------------------------------------------------------------------
-- Enable the server audit
----------------------------------------------------------------------
DECLARE @EnableAudit NVARCHAR(MAX) = N'ALTER SERVER AUDIT '
+ QUOTENAME(@AuditName) + N' WITH (STATE = ON);';
BEGIN TRY
EXEC sp_executesql @EnableAudit;
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE();
RAISERROR('ERROR enabling server audit: %s', 16, 1, @ErrorMessage);
RETURN;
END CATCH
----------------------------------------------------------------------
-- Verification: confirm the audit and specification are active
----------------------------------------------------------------------
SELECT
S.name AS AuditName,
CASE S.is_state_enabled
WHEN 1 THEN 'Y'
WHEN 0 THEN 'N'
END AS AuditEnabled,
S.type_desc AS WriteLocation,
SA.name AS AuditSpecificationName,
CASE SA.is_state_enabled
WHEN 1 THEN 'Y'
WHEN 0 THEN 'N'
END AS AuditSpecificationEnabled,
SAD.audit_action_name AS AuditActionName,
SAD.audited_result AS AuditedResult
FROM sys.server_audit_specification_details AS SAD
JOIN sys.server_audit_specifications AS SA
ON SAD.server_specification_id = SA.server_specification_id
JOIN sys.server_audits AS S
ON SA.audit_guid = S.audit_guid
WHERE SA.name = @SpecificationName;