Mitigating STIGs

Question

Mitigating STIGs

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

michael.k.choffy Say Hey Kid Points: 675 More actions · Answer 1

Thanks Jeff, I will give this a try. I tried to implement Lowell's audit solution, however I found out that you need the Enterprise Edition to create user audits and we only have Standard Edition.

Thanks Wayne, I will look over your suggestion as well.

michael.k.choffy Say Hey Kid Points: 675 More actions · Answer 2

jayoub (1/12/2016)
Below is how I resolved these STIG Checks. I go by STIG ID number so please make sure these are the ones you are asking about. Let me know if they are not
Create an SQL Agent job to execute daily called STIG-014900-015100-015200 and use the
Following description and query.
Description:
The job will mitigate Database STIG ID SQL2-00-014900, SQL2-00-015100 AND SQL2-00-15200. The output will be a count of all SP, Functions and triggers as well as the last object to be modified and the date it was modified. Review the history and compare.
Place the below query as step one
Select top 1 name, modify_date
from sys.all_objects
Where type
in('AF','FN','FS','FT','IF','TF','P','PC','X','TA',' TR')
ORDER BY MODIFY_DATE desc
Select count(*)
From sys.all_objects
Where type
in('AF','FN','FS','FT','IF','TF','P','PC','X','TA',' TR')
In the advanced part of the step - check the box "Include output in history"
Schedule the job to execute daily and check as often as you can
Additional Information\Steps:
If the DB-Mail is configure you can get an email alert with the output or write the output of the job to a table and check it on a weekly basis. I just check the history weekly and see if there are any changes.

Hi Jeff,

I created this job and got it to kind of work. The job's output is only a single object "fso_audit". I know there should be Stored Procedures and Functions reported. Also under Server Objects/Triggers I created a trigger for connection limits and this doesn't show up either.

Have any ideas on what is going on?

Thanks Michael

Wayne West SSC-Insane Points: 22586 More actions · Answer 3

The code he's using is filtering the object type, there's 26 different objects. BOL for SQL 2014 shows the types as:

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

Modify the 'where type in' lines and you should be able to capture what you need. Personally I'd filter to exclude IT and S rather than to capture just specific types. I don't know if it's possible to create IT and S, I'm not really familiar with techniques for attacking SQL Server aside from injection.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

jayoub SSCertifiable Points: 6991 More actions · Answer 4

Cool deal.

The way I figure is that the STIG is asking us to determine if there have been any changes to or additions to stored procedures, functions and triggers so if you only see the fso_audit stored procedure every time you check then you are good. If one day you check and find a different stored procedure or function - than you know that something has been created or altered. And that is what the STIG is asking us to figure out.

Also you can alter the query for top 10 or more. At this point you are only asking for top 1

Let me know I like helping out.

Jeff

Eric M Russell SSC Guru Points: 125560 More actions · Answer 5

Regarding the issue of how to monitor for unauthorized changes to the database schema, accounts, permissions, etc., one technique is to periodically perform an automated schema comparison between a pre-production environment and production. In other words, if you have an instance (perhaps just Express Edition) where you deploy prior to the final production deployment, then the two instances should be in sync, and any difference would indicate a lapse or breach in the production control process.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

michael.k.choffy Say Hey Kid Points: 675 More actions · Answer 6

michael.k.choffy

Say Hey Kid

Points: 675

January 13, 2016 at 2:10 pm

#1851737

I see

michael.k.choffy Say Hey Kid Points: 675 More actions · Answer 7

I see said the "blind man"! So only the objects with latest modify date show up. Now I have set it to show the last 10.

Thanks Jeff for your help on this, I was pulling my hair out. Look forward to reading your STIG doc when you post it.

Eric, thank you for your suggestion however I believe implementing the Job is the way I'll go.

Eric M Russell SSC Guru Points: 125560 More actions · Answer 8

michael.k.choffy (1/13/2016)
I see said the "blind man"! So only the objects with latest modify date show up. Now I have set it to show the last 10.
Thanks Jeff for your help on this, I was pulling my hair out. Look forward to reading your STIG doc when you post it.
Eric, thank you for your suggestion however I believe implementing the Job is the way I'll go.

But consider that a simply report of what objects have recently changed on production won't exactly tell you whether it was a legitimate change or not. However, identifying differences in schema between pre-production and production is a better clue that a modification occurred outside production control process.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

vadba SSChampion Points: 11133 More actions · Answer 9

For SQL Server 2012, I mitigate these STIG findings with an extended event. I scripted the xevent and have included it below. Please change the folder used for the xevent files as needed.

CREATE EVENT SESSION [AlterCodeObjects_PFT] ON SERVER
ADD EVENT sqlserver.object_altered(SET collect_database_name=(0)
ACTION(package0.collect_system_time,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
WHERE (([package0].[equal_uint64]([object_type],(8272)) OR [package0].[equal_uint64]([object_type],(20038)) OR [package0].[equal_uint64]([object_type],(21076))) AND [package0].[equal_uint64]([ddl_phase],(1)))),
ADD EVENT sqlserver.object_created(
ACTION(package0.collect_current_thread_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.sql_text)
WHERE (([package0].[equal_uint64]([object_type],(8272)) OR [package0].[equal_uint64]([object_type],(20038)) OR [package0].[equal_uint64]([object_type],(21076))) AND [package0].[equal_uint64]([ddl_phase],(1)))),
ADD EVENT sqlserver.object_deleted(
ACTION(package0.collect_current_thread_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.sql_text)
WHERE (([package0].[equal_uint64]([object_type],(8272)) OR [package0].[equal_uint64]([object_type],(20038)) OR [package0].[equal_uint64]([object_type],(21076))) AND [package0].[equal_uint64]([ddl_phase],(1))))
ADD TARGET package0.event_file(SET filename=N'H:\XEvents\Object_CREATE_UPDATE_DELETE.xel',max_file_size=(50),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Tony Newbie Points: 6 More actions · Answer 10

I've passed many SQL audits now, and I developed a tool to help perform STIG checks, if you are interested.

https://borellisecuritysoftware.com/products/asset-automated-sql-security-evaluation-tool

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 11

This would be a great series of articles, if anyone wants to write a few pieces.

jayoub SSCertifiable Points: 6991 More actions · Answer 12

I am currently writing something up and will post it once its done

Jeff

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 13

Please feel free to submit an article (or more than one) here: https://www.sqlservercentral.com/contributions

You'll get paid and I'm sure others will be interested.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 14

Anything that you write for code can be changed by anyone with SysAdmin privs. No pun intended, but if you want the STIG to "stick", you're going to have to turn on auditing and have it write to a disk that no one on the server has access to.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 15

For good auditing, the service account should have a file location that only allows write access for this account. read should be from another account, usually the auditors/security staff.