WIDBA

WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Monday, March 7, 2011

Putting on your SOX - Registry ACLs

In the second installment of the Powershell SOX series, we examine reading registry entries on servers being audited.  This script has some extra code in it because registry paths very between SQL 2000 and SQL 2008 in my environment and even depending on which registry area you are in.  If you are asked to take screenshots by the auditors in your company, consider seeing if they will take this and save yourself some time, especially after the first audit.


$OutputFile = "C:\SOX2011\SQL_SOX_ACL_SOX2011.htm"
$type = [Microsoft.Win32.RegistryHive]::LocalMachine
# Create 2d array of server and the "instancename" (depends on version of SQL)
$Srv = @("Server","MSSQL10.Brewers"),("Server2","MSSQL10.Packers")

$headerString = "-------------------------------------------------------------------------<br/>"
$title = "<h1>SQL Registry ACL SOX 2011</h1>"
$title | Out-File $OutputFile

Foreach($server in $Srv) {
    $serverName = $server[0].ToString();
    $instanceName = $server[1].ToString();
   
    # Instance Name is different in the System\CurrentControlSet.. for some reason
    # (in System is MSSQL$, in Software its MSSQL10.)
    $altInstancename = $instanceName.Replace("10.","$");
   
    $header = "<h2>Registry ACL for:" + $serverName  + "</h2>"
    $header   | out-File $OutputFile  -append
   
    # Note registry paths may be different between versions, etc
    $arr = @("SOFTWARE\MICROSOFT\MSSQLSERVER")`
        ,("SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\$instanceName") `
        ,("SYSTEM\CurrentControlSet\Services\$altInstanceName")

    # Loop each registry key path in $arr
    foreach($key in $arr)
    {
        $subHeader = "<h4>" + $key + "</h4>"
        $subHeader | out-File  $OutputFile -append
        $headerString | out-File $OutputFile -append   
        Write-Host "Key:" $key
       
        # Read Key from registry using .NET
        $regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $serverName )
        $regKey = $regKey.OpenSubKey($key)

        if(!$regKey) {   
            Write-Host "The key does not exist!";
        } else {       
        $regKey.GetAccessControl().GetAccessRules($true,$true,[System.Security.Principal.NTAccount]) |
            Where-Object -FilterScript { $_.RegistryRights -notlike "[-,0-9][0-9][0-9]*"}   |
            ConvertTo-HTML |
            out-File $OutputFile -append   
        }   
    } #Foreach Key
} # Foreach Server   

Next up we will get into accounts and services on the server.  Have a nice audit.

No comments:

Post a Comment