Framework/Core/FixControl/Services/SQLDatabaseFix.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 SQLDatabaseFix: FixServicesBase { SQLDatabaseFix([string] $subscriptionId, [ResourceConfig] $resourceConfig, [string] $resourceGroupName): Base($subscriptionId, $resourceConfig, $resourceGroupName) { } [MessageData[]] FixADAdmin([PSObject] $parameters) { [MessageData[]] $detailedLogs = @(); if((Get-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName $this.ResourceGroupName -ServerName $this.ResourceName | Measure-Object).Count -le 0) { $adAdmin = $parameters.ActiveDirectoryAdminEmailId; $detailedLogs += [MessageData]::new("Setting up Active Directory admin [$adAdmin] for server [$($this.ResourceName)]..."); $adAdminResult = Set-AzSqlServerActiveDirectoryAdministrator ` -ResourceGroupName $this.ResourceGroupName ` -ServerName $this.ResourceName ` -DisplayName $adAdmin ` -ErrorAction Stop $detailedLogs += [MessageData]::new("Active Directory admin has been set for server [$($this.ResourceName)]", $adAdminResult); } else { $detailedLogs += $this.PublishCustomMessage("Active Directory admin has already been set for server [$($this.ResourceName)]", [MessageType]::Update); } return $detailedLogs; } [MessageData[]] FixSqlDatabaseTDE([PSObject] $parameters) { [MessageData[]] $detailedLogs = @(); try { $sqlDatabases = @(); $sqlDatabases += Get-AzSqlDatabase -ResourceGroupName $this.ResourceGroupName -ServerName $this.ResourceName -ErrorAction Stop | Where-Object { $_.DatabaseName -ne "master" } $sqlDatabases | ForEach-Object { $database = $_ if($database.Status -eq 'Online') { try { $tdeStatus = Get-AzSqlDatabaseTransparentDataEncryption ` -ResourceGroupName $this.ResourceGroupName ` -ServerName $this.ResourceName ` -DatabaseName $database.DatabaseName ` -ErrorAction Stop if($tdeStatus.State -ne [TransparentDataEncryptionStateType]::Enabled) { $detailedLogs += [MessageData]::new("Enabling SQL TDE on database [$($database.DatabaseName)]..."); $tdeStatus = Set-AzSqlDatabaseTransparentDataEncryption ` -ResourceGroupName $this.ResourceGroupName ` -ServerName $this.ResourceName ` -DatabaseName $database.DatabaseName ` -State Enabled ` -ErrorAction Stop $detailedLogs += [MessageData]::new("SQL TDE has been enabled on database [$($database.DatabaseName)]", $tdeStatus); } } catch { $detailedLogs += [MessageData]::new("Error while fetching TDE status of database [$($database.DatabaseName)]"); } } else { $detailedLogs += $this.PublishCustomMessage("The database [$($database.DatabaseName)] is offline, run the script again when resource is online", [MessageType]::Warning); } } } catch { $detailedLogs += [MessageData]::new("Error while fetching databases of SQL Server [$this.ResourceName]"); } return $detailedLogs; } [MessageData[]] EnableServerAuditingPolicy([PSObject] $parameters) { [MessageData[]] $detailedLogs = @(); $storageAccountName = $parameters.StorageAccountName; $storageAccount = Get-AzResource -Name $storageAccountName -ResourceType 'Microsoft.Storage/storageAccounts' if(($storageAccount|Measure-Object).Count -eq 0) { throw "Cannot find a storage account with the name '$($storageAccountName)'. It either does not exist, associated with a different subscription or you do not have the appropriate credentials to access it." } $detailedLogs += [MessageData]::new("Setting up audit policy for server [$($this.ResourceName)] with storage account [$storageAccountName]..."); Set-AzSqlServerAudit ` -ResourceGroupName $this.ResourceGroupName ` -ServerName $this.ResourceName ` -StorageAccountResourceId $storageAccount.ResourceId ` -BlobStorageTargetState Enabled ` -RetentionInDays 0 ` -ErrorAction Stop $detailedLogs += [MessageData]::new("Audit policy has been set up for server [$($this.ResourceName)]"); return $detailedLogs; } [MessageData[]] EnableDatabaseAuditingPolicy([PSObject] $parameters, [string] $databaseName) { [MessageData[]] $detailedLogs = @(); $storageAccountName = $parameters.StorageAccountName; $storageAccount = Get-AzResource -Name $storageAccountName -ResourceType 'Microsoft.Storage/storageAccounts' if(($storageAccount|Measure-Object).Count -eq 0) { throw "Cannot find a storage account with the name '$($storageAccountName)'. It either does not exist, associated with a different subscription or you do not have the appropriate credentials to access it." } $detailedLogs += [MessageData]::new("Setting up audit policy for database [$databaseName] with storage account [$storageAccountName]..."); Set-AzSqlDatabaseAudit ` -ResourceGroupName $this.ResourceGroupName ` -ServerName $this.ResourceName ` -DatabaseName $databaseName ` -StorageAccountResourceId $storageAccount.ResourceId ` -BlobStorageTargetState Enabled ` -RetentionInDays 0 ` -ErrorAction Stop $detailedLogs += [MessageData]::new("Audit policy has been set up for database [$databaseName]"); return $detailedLogs; } [MessageData[]] EnableServerThreatDetection([PSObject] $parameters) { [MessageData[]] $detailedLogs = @(); $storageAccountName = $parameters.StorageAccountName; $securityContactEmails = $parameters.SecurityContactEmails; $detailedLogs += [MessageData]::new("Setting up threat detection for server [$($this.ResourceName)] with storage account [$storageAccountName]..."); # Check if audit is not enabled if(-not $this.IsServerAuditEnabled()) { $detailedLogs += $this.EnableServerAuditingPolicy($parameters) } # TODO: We are temporarily suppressing the alias deprecation warning message given by the below Az.SQL cmdlet. Update-AzSqlServerAdvancedThreatProtectionSetting ` -ResourceGroupName $this.ResourceGroupName ` -ServerName $this.ResourceName ` -StorageAccountName $storageAccountName ` -EmailAdmins $true ` -NotificationRecipientsEmails $securityContactEmails ` -RetentionInDays 0 ` -ErrorAction Stop ` -WarningAction SilentlyContinue $detailedLogs += [MessageData]::new("Threat detection has been set up for server [$($this.ResourceName)]"); return $detailedLogs; } # TODO: This function is not being called. We should delete it if not used. [MessageData[]] EnableDatabaseThreatDetection([PSObject] $parameters, [string] $databaseName) { [MessageData[]] $detailedLogs = @(); $storageAccountName = $parameters.StorageAccountName; $securityContactEmails = $parameters.SecurityContactEmails; $detailedLogs += [MessageData]::new("Setting up threat detection for database [$databaseName] with storage account [$storageAccountName]..."); # Check if audit is not enabled if(-not $this.IsDatabaseAuditEnabled($databaseName)) { $detailedLogs += $this.EnableDatabaseAuditingPolicy($parameters, $databaseName); } Update-AzSqlDatabaseAdvancedThreatProtectionSetting ` -ResourceGroupName $this.ResourceGroupName ` -ServerName $this.ResourceName ` -DatabaseName $databaseName ` -StorageAccountName $storageAccountName ` -EmailAdmins $true ` -NotificationRecipientsEmails $securityContactEmails ` -RetentionInDays 0 ` -ErrorAction Stop $detailedLogs += [MessageData]::new("Threat detection has been set up for database [$databaseName]"); return $detailedLogs; } hidden [bool] IsServerAuditEnabled() { $result = $false; $serverAudit = Get-AzSqlServerAudit -ResourceGroupName $this.ResourceGroupName -ServerName $this.ResourceName $result = ($serverAudit -and $serverAudit.BlobStorageTargetState -eq [AuditStateType]::Enabled) return $result } hidden [bool] IsDatabaseAuditEnabled([string] $databaseName) { $result = $false; $dbAudit = Get-AzSqlDatabaseAudit -ResourceGroupName $this.ResourceGroupName -ServerName $this.ResourceName -DatabaseName $databaseName; $result = ($dbAudit -and $dbAudit.BlobStorageTargetState -eq [AuditStateType]::Enabled) return $result } } |