Checks ideas: CIS, DISA, Idera, others

[potatoqualitee]

CIS.pdf

DISA: https://github.com/sqlcollaborative/disa-docs/blob/master/sql2014.md

[wsmelton]

STIG_SQLServer2014_v1r5.pdf

[potatoqualitee]

http://wiki.idera.com/pages/viewpage.action?pageId=43024977 and associated health checks on the left per @peturgretars

[tboggiano]

Question I'm going to be writing a CIS audit in PowerShell for my work has there be any side issues on this or any other discussion as there are a lot of test that have to be written?

[tboggiano]

Attaching all the CIS benchmarks because they are slightly different for each version of SQL Server. CIS_Microsoft_SQL_Server_2017_Benchmark_v1.0.0.pdf CIS_Microsoft_SQL_Server_2008_R2_Benchmark_v1.6.0.pdf CIS_Microsoft_SQL_Server_2012_Benchmark_v1.5.0.pdf CIS_Microsoft_SQL_Server_2014_Benchmark_v1.4.0.pdf CIS_Microsoft_SQL_Server_2016_Benchmark_v1.1.0.pdf

[potatoqualitee]

That'd be awesome! No work has been done with CIS within dbachecks as of yet

[peturgretars]

Would be really nice to get CIS checks added to dbachecks yes :)

[SQLDBAWithABeard]

Funny enough, I was having a conversation about that only yesterday - Watch this space ;-)

[wsmelton]

If remediation was also needed for CIS checks (which for those that use it is) it would benefit having it as a separate module. If you are going to go through the work of building the checks, remediation may as well be apart of that same work as well (where it can).

If not, is fine, but it deserves a custom report that is dedicated to the CIS checks. Alternative if it is included with all the other checks for dbachecks a dedicated tag or configuration would be beneficial where users can do reports or generate the PBI data for only those checks.

[tboggiano]

I'm open for whatever way you want to do, but some items yes you could write code to fix the issues. That's what I will doing when I write things internally for my company so it would not be a big deal for me to this as well. I just may need some more hand holding as I do this.

[SQLDBAWithABeard]

dbachecks is for validation only.

Thats not to say that a remediation module (which I think is a good idea) could not make use of the json output

I see getting dbachecks to be able to run CIS checks as

1. Writing any missing checks
2. Tagging all CIS checks with CIS
3. Writing a command to set any configuration required by CIS for the checks Set-DbcCisConfig
4. Possibly could wrap the whole thing in an other command Invoke-DbcCisChecks which would run Set-DbcCisConfig and then run Invoke-DbcCheck -Check Cis | Update-DbcPowerBiDataSource -Environment CiS or similar
5. If a separate PowerBi report would be more useful then one could be written to look at the CIS json

[tboggiano]

I'm working on creating a matrix for all the different versions of SQL in Excel as the test change for different versions. Then I see what test exists and we can tag the existing ones as a start. Start writing missing checks. Then we can decide what to do about reporting and remediation. I will be writing remediation as I go using dbatools where I can so I'll save those in my repo until we decide if maybe we want a separate project for compliance remediation.

[tboggiano]

Attaching a spreadsheet matrix with all the test, 2008 R2 has one test the rest don't. Then new tests were added for 2012 and above. CIS_Audit_Matrix.xlsx

[potatoqualitee]

ohh that matrix is awesome! can you plz make one for DISA Stigs 😅

[dbaduck]

Tracy,

I was looking at the same thing as you for CIS. I am glad to help. What can I do to help or share in the outcome of the development? Is the goal to have all of them as dbachecks? That would be awesome to have something like Rob was talking about with a comprehensive command to run all the checks against a version.

Let me know how I can help. Ben Miller

From: Tracy Boggiano [email protected] Sent: Thursday, July 25, 2019 1:11 PM To: sqlcollaborative/dbachecks [email protected] Cc: Subscribed [email protected] Subject: Re: [sqlcollaborative/dbachecks] Checks ideas: CIS, DISA, Idera, others (#116)

Attaching a spreadsheet matrix with all the test, 2008 R2 has one test the rest don't. Then new tests were added for 2012 and above. CIS_Audit_Matrix.xlsxhttps://github.com/sqlcollaborative/dbachecks/files/3432914/CIS_Audit_Matrix.xlsx

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://github.com/sqlcollaborative/dbachecks/issues/116?email_source=notifications&email_token=ACFCLDBV3YYCP4R43BL7J7TQBH3EJA5CNFSM4EJTIPYKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD22PPAI#issuecomment-515176321, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ACFCLDBKBQTD36C3E6DUWMTQBH3EJANCNFSM4EJTIPYA.

[SQLDBAWithABeard]

I have opened #643 to track adding CIS. This is looking really awesome and just how I imagined dbachecks would be used

[wsmelton]

@potatoqualitee I can probably build an Excel file out for STIGs, if I can download the XML file still for each version.

Which it looks like DoD has finally moved to a new site for them: https://public.cyber.mil/stigs/downloads/

[tboggiano]

@wsmelton up to you but I already started building the matrix yesterday. Chrissy forgot to tell me that there were over a hundred rules just for the instance, now I'm working on copying the rules for the database. I can pass it off to you if you like or I can finish out probably sometime this week.

[tboggiano]

@wsmelton I'm looking to create a STIG project now could you possibly do that Excel file that is mentioned in two comments above. If you need anything let me know. Looks like you might need the XML, I never finished my matrix and lost it (shame one me).