AzSDK

2.3.1

Framework/Core/SVT/Services/SQLDatabase.ps1

using namespace Microsoft.Azure.Commands.Sql.Auditing.Model
using namespace Microsoft.Azure.Commands.Sql.ServerUpgrade.Model
using namespace Microsoft.Azure.Commands.Sql.TransparentDataEncryption.Model
using namespace Microsoft.Azure.Commands.Sql.ThreatDetection.Model

Set-StrictMode -Version Latest 
class SQLDatabase: SVTBase
{       
    hidden [PSObject] $ResourceObject;

    SQLDatabase([string] $subscriptionId, [string] $resourceGroupName, [string] $resourceName): 
        Base($subscriptionId, $resourceGroupName, $resourceName) 
    { 
        $this.GetResourceObject();
    }
    
    SQLDatabase([string] $subscriptionId, [SVTResource] $svtResource): 
        Base($subscriptionId, $svtResource) 
    { 
        $this.GetResourceObject();
    }

    hidden [PSObject] GetResourceObject()
    {
        if (-not $this.ResourceObject) {
            $this.ResourceObject =   Get-AzureRmResource -ResourceName $this.ResourceContext.ResourceName -ResourceGroupName $this.ResourceContext.ResourceGroupName 
                                                         
            if(-not $this.ResourceObject)
            {
                throw ("Resource '{0}' not found under Resource Group '{1}'" -f ($this.ResourceContext.ResourceName), ($this.ResourceContext.ResourceGroupName))
            }
        }
        return $this.ResourceObject;
    }

    hidden [ControlResult] CheckSqlServerVersionUpgrade([ControlResult] $controlResult)
    {
        $upgradeStatus = Get-AzureRmSqlServerUpgrade -ResourceGroupName  $this.ResourceContext.ResourceGroupName -ServerName $this.ResourceContext.ResourceName -ErrorAction Stop

        $controlResult.AddMessage([MessageData]::new("Current status of SQL Database server upgrade -",
                                                     $upgradeStatus));

        if ($upgradeStatus.Status -eq [ServerUpgradeStatus]::Completed)   
        {
            $controlResult.VerificationResult = [VerificationResult]::Passed
        }                 
        else
        {
            $controlResult.VerificationResult = [VerificationResult]::Failed
        }

        return $controlResult;
    }

    
    hidden [ControlResult] CheckSqlServerAuditing([ControlResult] $controlResult)
    {
        $serverAudit = Get-AzureRmSqlServerAuditingPolicy -ResourceGroupName $this.ResourceContext.ResourceGroupName -ServerName $this.ResourceContext.ResourceName -ErrorAction Stop
       
        $controlResult.AddMessage([MessageData]::new("Current audit status for SQL server [$($this.ResourceContext.ResourceName)]:", $serverAudit))
        
        if($null -ne $serverAudit){ 
            #Check for Audit type as Blob (Table audit type going to be deprecated)
            if($serverAudit.AuditType -eq [AuditType]::Blob){
                
                $isCompliant = (($serverAudit.AuditState -eq [AuditStateType]::Enabled) `
                               -and ($serverAudit.RetentionInDays -eq $this.ControlSettings.SqlServer.AuditRetentionPeriod_Min -or $serverAudit.RetentionInDays -eq $this.ControlSettings.SqlServer.AuditRetentionPeriod_Forever))
                
                if ($isCompliant){
                           $controlResult.VerificationResult = [VerificationResult]::Passed
                    }                 
                    else{
                        $controlResult.AddMessage([VerificationResult]::Failed, 
                                                  "Audit settings are either disabled OR not retaining logs for at least $($this.ControlSettings.SqlServer.AuditRetentionPeriod_Min) days for resource - [$($this.ResourceContext.ResourceName)]");
                    }
            }
            else{
                $controlResult.AddMessage([VerificationResult]::Failed, "SQL server auditing is not enabled with 'Audit Type' as 'Blob' for SQL server ["+ $this.ResourceContext.ResourceName +"]");
            }

        }
        else{
            $controlResult.AddMessage("Unable to get audit details for SQL server [$($this.ResourceContext.ResourceName)]"); 
        }
   
        return $controlResult;
    }

   hidden [ControlResult[]] CheckSqlDatabaseAuditing([ControlResult] $controlResult)
   {
        [ControlResult[]] $resultControlResultList = @()

        $sqlDataBaseDtls = Get-AzureRmSqlDatabase -ResourceGroupName $this.ResourceContext.ResourceGroupName -ServerName $this.ResourceContext.ResourceName -ErrorAction Stop | 
            Where-Object { $_.DatabaseName -ne "master" } 

        if(($sqlDataBaseDtls | Measure-Object ).Count -eq 0)
        {
            $controlResult.AddMessage([MessageData]::new("No database found on SQL Server ["+ $this.ResourceContext.ResourceName +"]"));
            #Passing the status as there is no database found on the SQL Server
            $controlResult.VerificationResult = [VerificationResult]::Passed; 
            $resultControlResultList += $controlResult
        }
        else
        {
            $sqlDataBaseDtls | 
            ForEach-Object {
                $dbName = $_.DatabaseName;
                [ControlResult] $childControlResult = $this.CreateControlResult($dbName);    
                        
                $dbAuditPolicy = Get-AzureRmSqlDatabaseAuditingPolicy `
                                    -ResourceGroupName $this.ResourceContext.ResourceGroupName `
                                    -ServerName $this.ResourceContext.ResourceName `
                                    -DatabaseName $dbName `
                                    -ErrorAction Stop
                        
                if($null -ne $dbAuditPolicy){ 
   
                     $childControlResult.AddMessage([MessageData]::new("Current audit details for database [$($dbName)]:", $dbAuditPolicy));

                     #Check for Audit type as Blob (Table audit type going to be deprecated)
                     if($dbAuditPolicy.AuditType -eq [AuditType]::Blob){
                
                                    $isCompliant = (($dbAuditPolicy.AuditState -eq [AuditStateType]::Enabled) `
                                                   -and ($dbAuditPolicy.RetentionInDays -eq $this.ControlSettings.SqlServer.AuditRetentionPeriod_Min -or $dbAuditPolicy.RetentionInDays -eq $this.ControlSettings.SqlServer.AuditRetentionPeriod_Forever))
                
                                    if($isCompliant){
                                               $childControlResult.VerificationResult = [VerificationResult]::Passed
                                        }                 
                                        else{
                                            $childControlResult.AddMessage([VerificationResult]::Failed, 
                                                                           "Audit settings are either disabled OR not retaining logs for at least $($this.ControlSettings.SqlServer.AuditRetentionPeriod_Min) days for database - [$($dbName)]");
                                        }
                                }
                                else{
                                    $childControlResult.AddMessage([VerificationResult]::Failed, "SQL database auditing is not enabled with 'Audit Type' as 'Blob' for SQL database - [$($dbName)]");
                                }
                    }
                    else{
                         $childControlResult.AddMessage("Unable to get database audit details for [$($dbName)]"); 
                    }
                $resultControlResultList += $childControlResult
            }
        }
         
        return $resultControlResultList; 
    }
       
    hidden [ControlResult[]] CheckSqlDatabaseTDE([ControlResult] $controlResult)
        {
            [ControlResult[]] $resultControlResultList = @()

            $sqlDataBaseDtls = Get-AzureRmSqlDatabase -ResourceGroupName $this.ResourceContext.ResourceGroupName -ServerName $this.ResourceContext.ResourceName -ErrorAction Stop | 
                Where-Object { $_.DatabaseName -ne "master" } 
               
            if(($sqlDataBaseDtls | Measure-Object ).Count -eq 0)
            {
                $controlResult.AddMessage([MessageData]::new("No database found on SQL Server - ["+ $this.ResourceContext.ResourceName +"]"));
                #Since there is no database found we are passing this control
                $controlResult.VerificationResult = [VerificationResult]::Passed; 
                $resultControlResultList += $controlResult
            }
            else
            {
                $sqlDataBaseDtls | ForEach-Object {
                        $dbName = $_.DatabaseName;
                        [ControlResult] $childControlResult = [ControlResult]@{
                            ChildResourceName = $dbName;
                        };      
                        $tdeStatus = Get-AzureRmSqlDatabaseTransparentDataEncryption `
                                        -ResourceGroupName $this.ResourceContext.ResourceGroupName `
                                        -ServerName $this.ResourceContext.ResourceName `
                                        -DatabaseName $dbName `
                                        -ErrorAction Stop

                        $childControlResult.AddMessage([MessageData]::new("Current TDE Status for - ["+ $dbName +"]", 
                                                        ($tdeStatus )));      
                                
                        if($tdeStatus.State -eq [TransparentDataEncryptionStateType]::Enabled){
                                $childControlResult.VerificationResult = [VerificationResult]::Passed; 
                        }
                        else{
                                $childControlResult.VerificationResult = [VerificationResult]::Failed;
                        }
                  $resultControlResultList += $childControlResult
               } #End of ForEach-Object
            }
            return  $resultControlResultList;
         }

      
       hidden [ControlResult] CheckSqlServerADAdmin([ControlResult] $controlResult)
        {
            $adAdmin = Get-AzureRmSqlServerActiveDirectoryAdministrator -ResourceGroup $this.ResourceContext.ResourceGroupName -Server $this.ResourceContext.ResourceName -ErrorAction Stop
              
            $controlResult.AddMessage([MessageData]::new("Current status of Active Directory Admin for ["+ $this.ResourceContext.ResourceName +"] is"));

            if(($adAdmin | Measure-Object).Count -gt 0){
                 $controlResult.VerificationResult = [VerificationResult]::Passed
                 $controlResult.AddMessage([MessageData]::new("Active Directory admins are assigned on SQL Server - ["+ $this.ResourceContext.ResourceName +"]", 
                                                             ($adAdmin)));
            }
            else{
                $controlResult.VerificationResult = [VerificationResult]::Failed
                $controlResult.AddMessage([MessageData]::new("No Active Directory admin is assigned on SQL Server - ["+ $this.ResourceContext.ResourceName +"]"));
            }
            return $controlResult
        }

       
        hidden [ControlResult] CheckSqlServerThreatDetection([ControlResult] $controlResult)
        { 
            $isCompliant = $false
            $serverThreat = Get-AzureRmSqlServerThreatDetectionPolicy `
                                -ResourceGroupName $this.ResourceContext.ResourceGroupName `
                                -ServerName $this.ResourceContext.ResourceName `
                                -ErrorAction Stop

            $controlResult.AddMessage([MessageData]::new("Current threat detection status for server ["+ $this.ResourceContext.ResourceName +"] is",
                                                        ($serverThreat)));

            $excludedTypeCount = ($serverThreat.ExcludedDetectionTypes | Measure-Object ).Count

            if($excludedTypeCount -gt 0){
                $controlResult.AddMessage([MessageData]::new("All the required audit event types are not enabled for SQL Server - ["+ $this.ResourceContext.ResourceName +"]"));
            }

            $isCompliant =  (($serverThreat.ThreatDetectionState -eq [ThreatDetectionStateType]::Enabled) `
                                -and ($excludedTypeCount -eq 0) `
                                -and (($serverThreat.EmailAdmins  -eq $True) -or ($null -ne $serverThreat.NotificationRecipientsEmails)))
            if ($isCompliant) {
                $controlResult.VerificationResult = [VerificationResult]::Passed
            }                 
            else{
                $controlResult.VerificationResult = [VerificationResult]::Failed
            }
             
          return $controlResult
        }

        hidden [ControlResult[]] CheckSqlDatabaseThreatDetection([ControlResult] $controlResult)
        {
            [ControlResult[]] $resultControlResultList = @()

            $sqlDataBaseDtls = Get-AzureRmSqlDatabase -ResourceGroupName $this.ResourceContext.ResourceGroupName -ServerName $this.ResourceContext.ResourceName -ErrorAction Stop | 
                Where-Object { $_.DatabaseName -ne "master" } 
               
            if(($sqlDataBaseDtls | Measure-Object ).Count -eq 0)
            {
                $controlResult.AddMessage([MessageData]::new("No database found on SQL Server - ["+ $this.ResourceContext.ResourceName +"]"));
                #Since there is no database found we are passing this control
                $controlResult.VerificationResult = [VerificationResult]::Passed; 
                $resultControlResultList += $controlResult
            }
            else
            {
                $sqlDataBaseDtls | ForEach-Object {
                    $dbName = $_.DatabaseName;
                    [ControlResult] $childControlResult = [ControlResult]@{
                        ChildResourceName = $dbName;
                    };      
 
                    #$telemetryTimerPerDb = [System.Diagnostics.Stopwatch]::StartNew()
                    $dbThreat = Get-AzureRmSqlDatabaseThreatDetectionPolicy `
                                    -ResourceGroupName $this.ResourceContext.ResourceGroupName `
                                    -ServerName $this.ResourceContext.ResourceName `
                                    -DatabaseName $dbName `
                                    -ErrorAction Stop

                    $childControlResult.AddMessage([MessageData]::new("Current threat detection state for database - ["+ $dbName +"]", 
                                                                      ($dbThreat)));      
                            
                    $dbAuditPolicy = Get-AzureRmSqlDatabaseAuditingPolicy `
                                    -ResourceGroupName $this.ResourceContext.ResourceGroupName `
                                    -ServerName $this.ResourceContext.ResourceName `
                                    -DatabaseName $dbName `
                                    -ErrorAction Stop

                    $childControlResult.AddMessage([MessageData]::new("Current audit status for database - ["+ $dbName +"]", 
                                                                      ($dbAuditPolicy ))); 
                    
                    if(Get-Member -InputObject $dbAuditPolicy -Name 'UseServerDefault' -MemberType Properties)
                    {
                       $isCompliant = $dbAuditPolicy.UseServerDefault -eq [UseServerDefaultOptions]::Enabled
                    }
                    else
                    {
                      $isCompliant = $false  
                    }


                    if(-not $isCompliant) {
                        if(($dbThreat.ThreatDetectionState -eq [ThreatDetectionStateType]::Enabled) `
                            -and ($dbThreat.ExcludedDetectionTypes.Count -eq 0) `
                            -and (($dbThreat.EmailAdmins -eq $True) -or ($dbThreat.NotificationRecipientsEmails.Length -gt 0)))
                        {
                            $isCompliant = $True
                        }
                        else{
                            $isCompliant = $false                
                        }
                    }

                if($isCompliant) {
                     $childControlResult.VerificationResult = [VerificationResult]::Passed; 
                }
                else {
                     $childControlResult.VerificationResult = [VerificationResult]::Failed;
                }
               
                 $resultControlResultList += $childControlResult
               }
            }
           
            return  $resultControlResultList;
        }
    
       hidden [ControlResult] CheckSqlDatabaseFirewallEnabled([ControlResult] $controlResult)
        {
          $firewallDtls = Get-AzureRmSqlServerFirewallRule -ResourceGroupName $this.ResourceContext.ResourceGroupName  -ServerName $this.ResourceContext.ResourceName
          if(($firewallDtls | Measure-Object ).Count -gt 0){
               $controlResult.VerificationResult = [VerificationResult]::Passed
            }
            else{
               $controlResult.VerificationResult = [VerificationResult]::Failed 
            }
          return $controlResult
        }

        hidden [ControlResult] CheckSqlDatabaseFirewallIPAddressRange([ControlResult] $controlResult)
        {
          #As current function will check firewall ip address renages, if firewall is enabled. When it enabled, it allows any traffic from services within your Azure subscription to pass through.
          #Default record will be their with Start IP address as 0.0.0.0 and End Ip address as 0.0.0.0
            $firewallDtls = Get-AzureRmSqlServerFirewallRule -ResourceGroupName $this.ResourceContext.ResourceGroupName  -ServerName $this.ResourceContext.ResourceName
            if(($firewallDtls | Measure-Object ).Count -gt 0)
            {
                $controlResult.AddMessage([MessageData]::new("Current firewall settings for - ["+ $this.ResourceContext.ResourceName +"]", 
                                                             $firewallDtls));

                $anyToAnyRule =  $firewallDtls | Where-Object { $_.StartIpAddress -eq $this.ControlSettings.IPRangeStartIP -and $_.EndIpAddress -eq  $this.ControlSettings.IPRangeEndIP}
                if (($anyToAnyRule | Measure-Object).Count -gt 0)
                {
                    $controlResult.AddMessage([VerificationResult]::Failed, 
                                              [MessageData]::new("Firewall rule covering all IPs (Start IP address: $($this.ControlSettings.IPRangeStartIP) To End IP Address: $($this.ControlSettings.IPRangeEndIP)) is defined."));
                }
                else 
                {
                    $controlResult.VerificationResult = [VerificationResult]::Verify
                }
                $controlResult.SetStateData("Firewall IP addresses", $firewallDtls);
            }
            else
            {
                $controlResult.AddMessage([VerificationResult]::Failed, "Control can not be validated: Firewall is not enabled");
            }
            return $controlResult
        }
}