July 7, 2011 at 12:08 pm
Howdy,
I am working with complex conditions for policy based management in SQL Server 2008. I haven’t been using policy based management very log, so it was only recently that I discovered that we can use scripts instead of the simple facets as the basis for conditions.
Using the advanced editor for a policy based management condition I wan to use the script
ExecuteSql('Numeric', 'SELECT count(*) FROM sys.all_objects o, sys.database_principals u, sys.database_permissions p WHERE o.object_id = p.major_id AND p.grantee_principal_id = u.principal_id AND p.state IN (''G'', ''W'') AND u.type IN (''S'', ''U'')'
(Those of you who are working with the STIGS may recognize that query!) I have been able to use simpler scripts in policy based management conditions but this one requires strings inside the over all script. The condition fails to parse over the string values for p.state and u.type.
Does anyone know how to work around this?
Policy based management appears to have so much potential for helping with the STIGS. If only I could get around the problem of parsing the STIG check scripts.
Thanks!
July 7, 2011 at 1:47 pm
I haven't found a good way to work around those check script issues. I have setup SQL 2008 in a mandatory STIG compliant environment and it was a PITA.. There were no 2008 explicit STIGs and the guidance was use the 2005. However, I agree that PBM has a fair amount of potential for continued compliance, I don't believe it can handle every item. I have been working on a set of STIG based policies for as many items as possible for the better part of 6 months, progress is slow since I am not in that environment anymore but I do find many of the items useful. Also, keep in mind that many CAT II items are not mandatory and most CAT III items are effectively ignored if you don't have an easy way to accomplish them.. Only CAT I items are ALWAYS mandatory for base compliance, and for SQL 2005 there are only 7 of those.
Summed up, my basic thought is that PBM is a great way to largely automate continued compliance checks for many STIG items, however, it will not cover many.
CEWII
July 7, 2011 at 2:41 pm
Eventually I managed to get this to work though it is not clear to me why. This morning I thought the issue was related to the double quotes, because if I took them out of the query a condition based on the remainder of the code would work.
We have annual audits and the auditors are not consistent with respect to which STIGS are required and which are not. Can you refer me to a document indicating the SQL 2005 STIGS that are required and that are not required?
July 7, 2011 at 3:11 pm
The DISA website for SQL is here:
http://iase.disa.mil/stigs/app_security/database/sql.html
I was working in a STIG compliant environment where EVERY server was required to be compliant.
Items labeled at Category I: DG0001, DG0067, DG0128, DG0129, DG0167, DM1758, DM6101
Must ALL be addressed IF they apply to your environment, like if you don't have SSAS running then DM6101 is N/A. For DG0129 if you use all MS connectivity products it is Not a Finding. For DG0167, unless you have sensitive data per the DOD description or the data to the app from the database server is directly over the internet (not very often) this one is generally Not a Finding.
Items labeled at Category II may or may not be required, for the ones you decide not to tackle write up a paragraph why or how you are otherwise handling it. Some are basically just best practices but not important enough to put much effort into. some are related to other Cat II items. There are 164 of them.
Items labeled at Category III are often ignored, read through them but don't stress them. There are 20 of these.
There are a total of 191 items. I/II/III 7/164/20
Having a strong understanding of what the ACTUAL STIG item says and what it means is a great way to blunt auditors who only know what their company gave them. These are the real government issued STIG compliance requirements not some auditing companies distillation.
Also, keep in mind the main document is 240 pages long, and is an extremely painful read.
CEWII
July 12, 2011 at 7:13 pm
I am working with PBM with the STIG at DISA. We FINALLY got a 2008 server I could use to do this with. So I will share what I have in terms of what Policies I create for the STIG in this thread.
Al
July 13, 2011 at 8:19 am
Sounds great, I have been working slowly at that for the better part of 8 months. I think people would love to have a set of policies that they could just load and get reports on levels of compliance.
CEWII
August 4, 2011 at 10:33 am
It would be great if you would share the PBM with all of us. Having 20 some SQL servers to stig will be a painfully process since we have not done it for SQL yet. Please post them as soon as you can Grasshopper!
January 6, 2016 at 10:57 am
alanspeckman (7/12/2011)
I am working with PBM with the STIG at DISA. We FINALLY got a 2008 server I could use to do this with. So I will share what I have in terms of what Policies I create for the STIG in this thread.Al
Hello Alan, I am working on the STIGs for 2008R2 and ran across this post. I know it is 4+ years old, however I wonder if you got anywhere with the policies.
Thanks
January 8, 2016 at 6:58 am
I'm no longer with DISA, finished my 5 year contract back in 2013. I don't have anything from my time there to share. The key however to doing STIG with PBM is ExecuteSQL. You create a condition for a policy that runs your STIG check in ExecuteSQL()
http://blogs.msdn.com/b/sqlpbm/archive/2008/07/03/executesql.aspx
Use that example to get you started. You can do alot within PBM and executeSQL, not just STIG.
October 11, 2020 at 10:00 pm
I realize this is an old thread, but if anyone is still looking for comprehensive vulnerability scanning tools for SQL STIGs, you can now use ASSET to STIG SQL 2014 & 2016 installations in just minutes.
There's a video of the tool in action here:
https://borellisecuritysoftware.com/products/asset-automated-sql-security-evaluation-tool
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply