Recently I spent months of my lift working on STIG and CIS compliance at my job and one of those tasks was setting up SQL Audit for STIG. Now, that might seem like a trivial task after all don’t you just have to create an audit and audit specification and let it run. If only it were that easy, some of the specifications can have a significant performance impact on your system depending on the type of activity happening and if you happened to lucky enough to have a monitoring software setup your will be logging even more data that doesn’t make sense to log. In addition, on my system we are using SQL replication and that activity due to volume doesn’t make sense to log. So, let’s walk through my setup and how I got there, the how I got there being the most important part so you can figure out how to use filters to setup a SQL audit that does that kill your performance.
So, for those not familiar with STIG, you can go here to view all the lengthy requirements for the instance and database. STIG requirements are created for dealing with businesses that have the US Department of Defense as customers. There is a script included with the audit that contains all the items they request be audited which contains an extensive list. Below is what your default specification would look like with all the specifications defined, all the finings they apply to are in comments out to the side for reference, so save this in source control.
CREATE SERVER AUDIT SPECIFICATION [STIGAuditSpec]
FOR SERVER AUDIT [Audit-STIG]
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP), -- V-79239,V-79291,V-79293,V-79295
ADD (AUDIT_CHANGE_GROUP), -- V-79239,V-79291,V-79293,V-79295
ADD (BACKUP_RESTORE_GROUP), -- V-79239,V-79291,V-79293,V-79295
ADD (DATABASE_CHANGE_GROUP), -- V-79239,V-79291,V-79293,V-79295
ADD (DATABASE_OBJECT_CHANGE_GROUP), -- V-79239,V-79291,V-79293,V-79295
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP), -- V-79239,V-79259,V-79261,V-79263,V-79265,V-79275,V-79277,V-79291,V-79293,V-79295
ADD (DATABASE_OBJECT_ACCESS_GROUP), -- V-79239
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP), -- V-79239,V-79259,V-79261,V-79263,V-79265,V-79275,V-79277,V-79291,V-79293,V-79295
ADD (DATABASE_OPERATION_GROUP), -- V-79239,V-79291,V-79293,V-79295
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP), -- V-79239,V-79259,V-79261,V-79263,V-79265,V-79275,V-79277,V-79291,V-79293,V-79295
ADD (DATABASE_PERMISSION_CHANGE_GROUP), -- V-79239,V-79259,V-79261,V-79263,V-79265,V-79275,V-79277,V-79291,V-79293,V-79295
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), -- V-79239,V-79291,V-79293,V-79295
ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP), -- V-79239,V-79291,V-79293,V-79295
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), -- V-79239,V-79259,V-79261,V-79263,V-79265,V-79275,V-79277,V-79291,V-79293,V-79295
ADD (DBCC_GROUP), -- V-79239,V-79291,V-79293,V-79295
ADD (FAILED_LOGIN_GROUP), -- V-79289
ADD (LOGIN_CHANGE_PASSWORD_GROUP), -- V-79239,V-79291,V-79293,V-79295
ADD (LOGOUT_GROUP), -- V-79295
ADD (SCHEMA_OBJECT_ACCESS_GROUP), -- V-79137,V-79139,V-79251,V-79253,V-79255,V-79257,V-79271,V-79273,V-79283,V-79285,V-79299,V-79301
ADD (SCHEMA_OBJECT_CHANGE_GROUP), -- V-79239,V-79267,V-79269,V-79279,V-79281,V-79291,V-79293,V-79295
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP), -- V-79239,V-79259,V-79261,V-79263,V-79265,V-79275,V-79277,V-79291,V-79293,V-79295
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP), -- V-79239,V-79259,V-79261,V-79263,V-79265,V-79275,V-79277,V-79291,V-79293,V-79295
ADD (SERVER_OBJECT_CHANGE_GROUP), -- V-79239,V-79291,V-79293,V-79295
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP), -- V-79239,V-79259,V-79261,V-79263,V-79265,V-79275,V-79277,V-79291,V-79293,V-79295
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP), -- V-79239,V-79259,V-79261,V-79263,V-79265,V-79275,V-79277,V-79291,V-79293,V-79295
ADD (SERVER_OPERATION_GROUP), -- V-79239,V-79291,V-79293,V-79295
ADD (SERVER_PERMISSION_CHANGE_GROUP), -- V-79239,V-79259,V-79261,V-79263,V-79265,V-79275,V-79277,V-79291,V-79293,V-79295
ADD (SERVER_PRINCIPAL_CHANGE_GROUP), -- V-79291,V-79293,V-79295
ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP), -- V-79239,V-79291,V-79293,V-79295
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), -- V-58071,V-58073
ADD (SERVER_STATE_CHANGE_GROUP), -- V-79239,V-79259,V-79261,V-79263,V-79265,V-79275,V-79277,V-79291,V-79293,V-79295
ADD (SUCCESSFUL_LOGIN_GROUP), -- V-79287,V-79297
ADD (TRACE_CHANGE_GROUP), -- V-79239,V-79291,V-79293,V-79295
ADD (USER_CHANGE_PASSWORD_GROUP)
WITH (STATE = ON);If you want to know more about what each of these specifications logs you can look at Microsoft’s page on auditing.
Now, where did I run into problems at. Specifically, our monitoring software, replication, and logging SELECT/INSERT/DELETE/UPDATE/EXECUTE statements. So, let’s talk about what each of these are doing and how we can filter these out. Note we turned on the above specification and filled up 5 GBs of logs in under 10 minutes and managed to make our test system unusable. So, I highly recommend performance testing under load your application to see that you are capturing things that make sense to capture and talking your auditors about way it doesn’t make sense to log your monitoring software or replication and anything else you might find that I hope you will tell me about.
Monitoring software issues a lot of commands that fall under the VIEW SERVER STATE and VIEW DATABASE STATE commands. Now, we may want to see when others are doing this, but monitoring software is constantly going to be doing this. So, this is where we start to talk about filtering the audit itself which we have not setup yet. Below is the script to set it up. One thing to note is the ON_FAILURE option, the STIG requirements state to set to this SHUTDOWN the instance but since I’d prefer to have them running and not randomly shutting down, I’ve set mine to CONITNUE if there is failure.
CREATE SERVER AUDIT [Audit-STIG]
TO FILE
(FILEPATH = N'C:TempAudit' --<Your local error log directory I've did this for testing so I could query and delete the files easily, so don't do this
,MAXSIZE = 100 MB
,MAX_ROLLOVER_FILES = 50
,RESERVE_DISK_SPACE = ON
)
WITH
(QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '55a7f185-afbf-4c1a-8135-96b1db32a370'
)So, once you get this setup you are ready to see what data you are collecting and performance test but just maybe on one server because as I know it may take it down hard. You can query the SQL audit files by using the sys.fn_get_audit_file function. But getting all the data back in one stream isn’t very useful. But grouping by action is because then you can see if there are particular things you can filter out that are occurring over and over that aren’t necessary to log.
SELECT * FROM sys.fn_get_audit_file ('c:tempaudit*.sqlaudit',default,default);So, let’s take a look on my local laptop of what that would look like. If we take a look at what as most of the records, we can see are VSST. From here we will want to take and drill down until any of the one that are the largest numbers and see the details and add filters.
SELECT action_id,
count(*)
FROM sys.fn_get_audit_file ('c:tempaudit*.sqlaudit',default,default)
group by action_id;
So, first take a look at the link to determine the action_ids are and save the link you will need it later for your filters and send Solomon a thank and maybe a follow for compiling the list. He is in my local user group and wicked smart.
SELECT *
FROM sys.fn_get_audit_file ('c:tempaudit*.sqlaudit',default,default)
WHERE action_id = 'VSST';And one look at my local laptop no wonders we turn haven’t off Telemetry it’s so chatty, so go turn that service off if you haven’t so you don’t have to add a filter. But on to other things.
So, with these two pieces we can look at Solomon’s page and determine that VSST is VIEW SERVER STATE and that might be me having the Activity Monitor open or replication monitoring software activity. So, our first part of our filter will be for our service account for monitoring software is below. So, I had to setup two filters on the service account. One for the whole entire account and one to filter out individual actions. So, when you query the audit files you find the text for the action_id and use link to find the number for the action_id to use in your filters, yes, I know intuitive right {sarcasm}. Well here goes a handy link to find those and an index to numbers we will need for filter. Thanks Solomon! BTW…in case I didn’t say it before Solomon is in my local user group and wicked smart.
ALTER SERVER AUDIT [Audit-STIG]
WHERE session_server_principal_name <> ('domainmonitoring_software')
--Monitoring software still logging these even with account filter
AND (session_server_principal_name <> 'domainmonitoring_software' AND action_id <> 1128481348) --DBCC
Next, my servers rely on replication to replicate data and this isn’t audit worthy as it is a system process so let’s filter out all of that and get these processes even more streamlined.
WHERE session_server_principal_name NOT LIKE '*logreader*'
AND session_server_principal_name <> 'repladmin'
AND session_server_principal_name <> 'distributor_admin'
AND session_server_principal_name NOT LIKE '*svc_distribution*'
AND session_server_principal_name NOT LIKE '*svc_snapshot*'
AND (session_server_principal_name <> 'repladmin' AND action_id <> 1128481348) --DBCC
AND (session_server_principal_name NOT LIKE '*logreader*' AND action_id <> 1128481348) --DBCC
AND (session_server_principal_name NOT LIKE '*svc_distribution*' AND action_id <> 1128481348) ---DBCC
AND (session_server_principal_name NOT LIKE '*svc_snapshot*' AND action_id <> 1128481348) --DBCC
AND (session_server_principal_name <> 'repladmin' AND action_id <> 542133577) --DBCC
AND (session_server_principal_name NOT LIKE '*logreader*' AND action_id <> 542133577) --DBCC
AND (session_server_principal_name NOT LIKE '*svc_distribution*' AND action_id <> 542133577) ---DBCC
AND (session_server_principal_name NOT LIKE '*svc_snapshot*' AND action_id <> 542133577) --DBCCFinally, you just keep altering the ALTER SERVER AUDIT and stop and start and keep adding the filters you need. I had so have some other filters that were specific to my environment but the basics are you would just keep running the action_id with count query to determine what is recording the most data, then using the query below to review the top action(s) and see if there is a filter you can insert to trim out the data and document for the auditors why this data doesn’t need to be audited.
Any questions or comments drop me a line and I’ll be happy to assist.
The post Setting Up SQL Audit for STIG Compliance first appeared on Tracy Boggiano's Blog.