January 13, 2016 at 7:00 am
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.
January 13, 2016 at 9:42 am
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 notCreate 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
January 13, 2016 at 11:04 am
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]
January 13, 2016 at 11:18 am
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
January 13, 2016 at 11:25 am
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
January 13, 2016 at 2:10 pm
I see
January 13, 2016 at 2:19 pm
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.
January 14, 2016 at 7:14 am
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
March 7, 2017 at 7:23 am
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
August 7, 2020 at 6:37 pm
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
August 7, 2020 at 8:21 pm
This would be a great series of articles, if anyone wants to write a few pieces.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 7, 2020 at 8:47 pm
I am currently writing something up and will post it once its done
Jeff
August 7, 2020 at 9:15 pm
Please feel free to submit an article (or more than one) here: https://qa.sqlservercentral.com/contributions
You'll get paid and I'm sure others will be interested.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 8, 2020 at 12:08 am
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
August 10, 2020 at 1:51 pm
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply