AzSDK

2.2.0

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)
    {
        #Facing issue while refrencing AuditEventType class.
        #--------------Enforced Events-------------------------------------------------
        $enforcedEvents = ([AuditEventType]::Login_Failure, `
                [AuditEventType]::Login_Success, `
                [AuditEventType]::ParameterizedSQL_Failure, `
                [AuditEventType]::PlainSQL_Failure,`
                [AuditEventType]::StoredProcedure_Failure,`
                [AuditEventType]::TransactionManagement_Failure`
                ) 
        #---------------------------------------------------------------------------
        
        $controlResult.AddMessage([MessageData]::new("Following event types must be enabled under SQL Server Auditing:", 
                                    ($enforcedEvents | Select-Object -ExcludeProperty * )));

        $serverAudit = Get-AzureRmSqlServerAuditingPolicy -ResourceGroupName $this.ResourceContext.ResourceGroupName -ServerName $this.ResourceContext.ResourceName -ErrorAction Stop
        $controlResult.AddMessage([MessageData]::new("Current audit status for server ["+ $this.ResourceContext.ResourceName +"]", 
                                    $serverAudit))
        $excudedTypesCount = ((($enforcedEvents) |
                            Select-Object @{ Name = "Status"; Expression = { $serverAudit.EventType.Contains($_) } }).Status |
                            Where-Object { $_ -eq $false } | Measure-Object ).Count 
        
        if($excudedTypesCount -gt 0) {
            $controlResult.AddMessage([MessageData]::new("All the required audit event types are not enabled for SQL Server - ["+ $this.ResourceContext.ResourceName +"]"));
        }

        $isCompliant =  ((($serverAudit.AuditState -eq [AuditStateType]::Enabled)) `
                           -and ($excudedTypesCount -eq 0) -and ($serverAudit.RetentionInDays -eq $this.ControlSettings.SqlServer.AuditRetentionPeriod_Days))
        
        if ($isCompliant){
                $controlResult.VerificationResult = [VerificationResult]::Passed
            }                 
            else{
                $controlResult.VerificationResult = [VerificationResult]::Failed
            }

        return $controlResult;
    }

   hidden [ControlResult[]] CheckSqlDatabaseAuditing([ControlResult] $controlResult)
   {
        [ControlResult[]] $resultControlResultList = @()
        #--------------Enforced Events--------------------------------------------------
        $enforcedEvents =  ([AuditEventType]::Login_Failure, `
                            [AuditEventType]::Login_Success, `
                            [AuditEventType]::ParameterizedSQL_Failure, `
                            [AuditEventType]::PlainSQL_Failure,`
                            [AuditEventType]::StoredProcedure_Failure,`
                            [AuditEventType]::TransactionManagement_Failure)
        #-------------------------------------------------------------------------------

        $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);    
                        
                $childControlResult.AddMessage([MessageData]::new("Following event types must be enabled under SQL Server Database Auditing - ["+ $dbName +"]", 
                                                                ($enforcedEvents | Select-Object -ExcludeProperty *)));      

                $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) 
                {
                    $excudedTypesCount = ((($enforcedEvents) |
                            Select-Object @{ Name = "Status"; Expression = { $dbAuditPolicy.EventType.Contains($_) } }).Status |
                            Where-Object { $_ -eq $false } | Measure-Object ).Count 

                    if($excudedTypesCount -gt 0)
                    {
                        $childControlResult.AddMessage([MessageData]::new("All the required audit event types are not enabled for SQL Database '$dbName'"));
                    }
                            
                    if(($dbAuditPolicy.AuditState -eq [AuditStateType]::Enabled) `
                        -and ($excudedTypesCount -eq 0) -and ($dbAuditPolicy.RetentionInDays -eq $this.ControlSettings.SqlServer.AuditRetentionPeriod_Days)) {
                                $isCompliant = $True
                    }
                    else{
                        $isCompliant = $false                
                    }
                }                          
                #$propClone = $telemetryProperties.Clone() #
                #$propClone.Add($Global:TeleKeys::NestedResourceName, $dbName)

                if($isCompliant){
                    $childControlResult.VerificationResult = [VerificationResult]::Passed; 
                }
                else
                {
                    $childControlResult.AddMessage([VerificationResult]::Failed, "All required Audit settings are not configured correctly for SQL Database '$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
        }
}