August 13, 2015 at 1:38 pm
I am trying to STIG my Instance and databases and wonder if you can help. I need help figuring out how to do the following:
At the DB level:
STIG ID: SQL2-00-014900
SQL Server must be monitored to discover unauthorized changes to functions.
STIG ID: SQL2-00-015100
SQL Server must be monitored to discover unauthorized changes to triggers.
STIG ID: SQL2-00-015200
SQL Server must be monitored to discover unauthorized changes to stored procedures.
At the Instance level:
STIG ID: SQL2-00-023900
SQL Server must verify there have not been unauthorized changes to SQL Server software and information.
Any help is appreciated
Thanks
Jeff
August 17, 2015 at 8:35 am
jayoub (8/13/2015)
I am trying to STIG my Instance and databases and wonder if you can help. I need help figuring out how to do the following:At the DB level:
STIG ID: SQL2-00-014900
SQL Server must be monitored to discover unauthorized changes to functions.
STIG ID: SQL2-00-015100
SQL Server must be monitored to discover unauthorized changes to triggers.
STIG ID: SQL2-00-015200
SQL Server must be monitored to discover unauthorized changes to stored procedures.
At the Instance level:
STIG ID: SQL2-00-023900
SQL Server must verify there have not been unauthorized changes to SQL Server software and information.
Any help is appreciated
Thanks
Given that there's been no response to your post in 4 days, it seems likely that no one knows what you're talking about. I'm thinking you're talking about some kind of monitoring software from which you're retrieving logged information that it keeps track of. However, as I'm not familiar with ANY such product, I'm not going to be of any help with this either. I also can't even determine what it is you are actually asking for help with, as you haven't really been the least bit specific about what the data you listed represents.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 17, 2015 at 2:59 pm
Thank you very much for the reply. I may have resolve the problem by doing the following:
Step one: Create a job to execute daily called DB-STIG 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 MOFIFY_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
If the DB-Mail is configure you can get an email alert with the output.
Let me know what you think. We do not have any monitoring software, nor do we have dbmail yet.
Your help is appreciated.
Jeff
August 17, 2015 at 4:15 pm
Okay, but I still don't know what a "STIG" is, as you haven't said word one. My gut, after your last post, suggests it's some kind of internal audit requirement, but who knows... Sound like you've solved your own problem.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 17, 2015 at 7:04 pm
Great questions.
the STIG is the Security Technical Implementation Guide and its created by a DoD Agency called DISA (Defense Information Systems Agency). The guide is a checklist of things you must do to lock down your applications. They have it for everything including SQL and IIS and anybody can use it. I was told that the DISA STIG is considered the most stringent DB lock down in the business.
The web site is http://iase.disa.mil/stigs/Pages/index.aspx
Anyway for SQL they have a STIG checklist for the instance and a checklist for the database. As a DBA I must ensure that all the servers and all production and system database are compliant based on the checks.
Some checks are easy to mitigate and some are very hard. In each check they have a Discussion, check and fix among other items.
The discussion gives you some facts. The check illustrates what and how to check for the issue and the fix tells you how to fix the issue.
The problem is that most of them are very easy to understand, but many are not. Also the fix in many of them does not have much information.
We have a application call STIG VIEWER that you use to work with the checklists
The STIG changes about 2 times per year, so as DBA's we often have to recheck our servers. System Administrators and Developer have to deal with this also
My main job now is to STIG SQL instances and database and I want to ensure that I get it perfect. As you mentioned we do get audited by DISA and if they are not happy they can shut your system down.
My goal is to create Document and method to this madness. The document will have all the steps to mitigate each check. I am almost done but have a few left to figure out. The ones you helped me with are for the Database STIG.
Once i get this document and method down on paper I would like to post it so that other do not have to work so hard to figure this stuff out. Most DBA's have their way to this this but so far I have not seen anybody get through an audit w/o findings.
I have passed an audit once, but it was with an older version of the STIG and it had more detail in the fix section of the check and was easier to understand.
Anyway, let me know if there is a place on SQLServerCentral I can post my stuff, so other can use this resource and add information. I know there is more then one way to do things.
I do have access to the people who created the STIG's and can get information from them.
Your help is appreciated.
Jeff
August 17, 2015 at 8:04 pm
Perhaps the largest challenge among the items listed are the ones requiring that you monitor for "unauthorized" changes. How do you plan to define the word "authorized"? You might think that's simple, but it's truly not. What about a hot fix to production to resolve an issue created by some other kind of update? Do you have a formal "Change Control" process, with regular meetings to review "approved" changes and such? And then, what do you put in place to detect changes? DDL triggers? And if you detect a change via trigger, then how do you plan to report that change, and how do you store a list of "authorized" changes? Don't answer me. This is all stuff you'll need to work out. I don't know if there's a better forum within SQL Server Central for these kinds of tasks or not, but let's hope someone who is actually a DBA can chime in with the kinds of things they do...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 11, 2016 at 9:54 am
jayoub (8/17/2015)
Great questions.the STIG is the Security Technical Implementation Guide and its created by a DoD Agency called DISA (Defense Information Systems Agency). The guide is a checklist of things you must do to lock down your applications. They have it for everything including SQL and IIS and anybody can use it. I was told that the DISA STIG is considered the most stringent DB lock down in the business.
The web site is http://iase.disa.mil/stigs/Pages/index.aspx
Anyway for SQL they have a STIG checklist for the instance and a checklist for the database. As a DBA I must ensure that all the servers and all production and system database are compliant based on the checks.
Some checks are easy to mitigate and some are very hard. In each check they have a Discussion, check and fix among other items.
The discussion gives you some facts. The check illustrates what and how to check for the issue and the fix tells you how to fix the issue.
The problem is that most of them are very easy to understand, but many are not. Also the fix in many of them does not have much information.
We have a application call STIG VIEWER that you use to work with the checklists
The STIG changes about 2 times per year, so as DBA's we often have to recheck our servers. System Administrators and Developer have to deal with this also
My main job now is to STIG SQL instances and database and I want to ensure that I get it perfect. As you mentioned we do get audited by DISA and if they are not happy they can shut your system down.
My goal is to create Document and method to this madness. The document will have all the steps to mitigate each check. I am almost done but have a few left to figure out. The ones you helped me with are for the Database STIG.
Once i get this document and method down on paper I would like to post it so that other do not have to work so hard to figure this stuff out. Most DBA's have their way to this this but so far I have not seen anybody get through an audit w/o findings.
I have passed an audit once, but it was with an older version of the STIG and it had more detail in the fix section of the check and was easier to understand.
Anyway, let me know if there is a place on SQLServerCentral I can post my stuff, so other can use this resource and add information. I know there is more then one way to do things.
I do have access to the people who created the STIG's and can get information from them.
Your help is appreciated.
Hi jayoub,
I am working through a STIG of a 2008R2 database and ran into the same issues where some of the requirements are easy to resolve while others are much less clear.
Curious, did you ever get the chance to post your "STIG stuff"? I believe that this would help a lot of people working with these STIGs.
-Michael
January 11, 2016 at 11:47 am
I have a document ready, but need to clean it up before posting it. Is there a particular STIG you are having trouble with that i may be able to help with quick.
Let me know.
Jeff
January 11, 2016 at 1:02 pm
Hi Jeff,
Thanks for the quick response! There are three that I have been struggling with, V-41403, V-41404, V-41406. They all have to do with "SQL Server must be monitored to discover unauthorized changes to functions, triggers, and Stored Procedures". Do you have any ideas on how to mitigate them?
Thanks, Michael
January 11, 2016 at 1:20 pm
michael.k.choffy (1/11/2016)
Hi Jeff,Thanks for the quick response! There are three that I have been struggling with, V-41403, V-41404, V-41406. They all have to do with "SQL Server must be monitored to discover unauthorized changes to functions, triggers, and Stored Procedures". Do you have any ideas on how to mitigate them?
Thanks, Michael
i would think that is asking for the creation of a SQL Server and/or Database Audit which checks for DDL changes.
that is different than a database or serverwide DDL trigger, since that could be disabled and enabled, right?
https://technet.microsoft.com/en-us/library/dd392015(v=sql.100).aspx
Lowell
January 11, 2016 at 1:35 pm
Hi Lowell,
Thank you for your input! Yes your suggestion does sound correct, however I will have to investigate the info at the link you provided. Unfortunately I inherited the SQL server and am not a DB, just the unlucky Microsoft guy.
Thanks again, Michael
January 11, 2016 at 2:01 pm
hope that helps;
in the article a mentioned, search for DATABASE_OBJECT_CHANGE_GROUP , as i think that is the audit spec you are looking for;
I think that section, complete with screenshots, can help you build up what you are after.
Lowell
January 12, 2016 at 7:31 am
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.
Jeff
January 12, 2016 at 7:35 am
Sorry I posted twice
Jeff
January 12, 2016 at 4:08 pm
I don't know if you might find this useful, but a long time ago I wrote some code for SQL Server 2000 for a similar purpose. It's probably overkill, but something in it might prove useful for you, so submitted for your consideration....
Obviously the system tables referenced in the view should be updated for modern systems.
First, a repository table.
CREATE TABLE [dbo].[ObjectChecksums](
[BackupDate] [smalldatetime] NULL,
[DatabaseName] [nvarchar](128) NULL,
[ObjectName] [nvarchar](261) NULL,
[RecCount] [int] NULL,
[BChecksum] [int] NULL,
[ID] [int] NOT NULL,
[TYPE] [char](2) NULL,
[UID] [smallint] NULL,
[parent_obj] [int] NOT NULL,
[What] [varchar](9) NULL
) ON [PRIMARY];
Next, a view that gives me labels as to what is being extracted from system metadata.
CREATE VIEW vwObjectChecksums AS
SELECT 'Columns' AS What, id AS ID,
COUNT(*) AS RecCount,
CHECKSUM_AGG(binary_checksum(*)) AS BChecksum
FROM syscolumns
GROUP BY id
UNION
SELECT 'Text', id, COUNT(*), CHECKSUM_AGG(binary_checksum(*))
FROM syscomments
GROUP BY id
UNION
SELECT 'Idexes', id, indid, CHECKSUM_AGG(binary_checksum(*))
FROM sysindexes
GROUP BY id, indid
UNION
SELECT 'IndexKeys', id, indid, CHECKSUM_AGG(binary_checksum(*))
FROM sysindexkeys
GROUP BY id, indid;
Finally, a select statement that takes the view and gives checksums of the user-created system objects:
SELECT CAST(GETDATE() AS SMALLDATETIME) AS BackupDate, DB_NAME() AS DatabaseName,
'[' + su.name + '].[' + o.name + ']' AS ObjectName, RecCount, BChecksum,
o.ID, TYPE, o.UID, parent_obj, what
--INTO ObjectChecksums
FROM sysobjects o
LEFT JOIN vwObjectChecksums cs
ON o.id = cs.id
LEFT JOIN sysusers su
ON o.uid = su.uid
WHERE TYPE NOT IN ('S','SQ','IT')
ORDER BY TYPE, ObjectName;
Note the commented out INTO clause on the final code block, that's what I used to initially build the repository table. I had a database called zDBInfo that held this table, and the nightly backup script would run this Select as an Insert dbo.ObjectChecksums From ..., thus collecting checksums of non-system objects. I would think it wouldn't be too difficult to write a windowing function to trip when a checksum changes from something previously seen, but I wrote this before windowing functions came around. I came up with this when I learned of and became fascinated by checksums and aggregate checksums, and I remember very little about the code, so don't ask me too deep of questions of it as it's over 8 years old. 😀
-----
[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]
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply