Azure Helper Functions

AzureFunctions.ps1

<#
.SYNOPSIS
The function returns an OAuth2 access token for the resource specified with URI.

.DESCRIPTION
Obtains and returns an OAuth2 access token for the resource specified with URI.

.OUTPUTS
Token object
#>
function Get-TokenClientCredentials {
   Param(
      # Tenant ID
      [Parameter(Mandatory)][ValidateNotNull()][ValidateNotNullOrEmpty()]  
      [String]$TenantId,
      # Application Id for which a token is being requested.
      [Parameter(Mandatory)][ValidateNotNull()][ValidateNotNullOrEmpty()]  
      [String]$ApplicationId,
      # Resource URI for which the token is requested unless Scope parameter is specified.
      [Parameter(Mandatory)][ValidateNotNull()][ValidateNotNullOrEmpty()]  
      [String]$ApplicationSecret,
      # Resource ID for which the token is requested. If not specified, Resource parameter is used.
      [Parameter()][ValidateNotNull()][ValidateNotNullOrEmpty()]  
      [string]$Secret,
      [Parameter()][ValidateNotNull()][ValidateNotNullOrEmpty()]  
      [string]$Resource  
    )  

    If ($Resource -and -not $Scope) {
        If ($Resource.EndsWith("/")) {
            $Scope =  "$Resource" + ".default"
        }
        Else {
            $Scope =  "$Resource" + "/.default"
        }
    }

    $TokenEndpoint = $tokenEndPoint = "https://login.microsoftonline.com/{0}/oauth2/v2.0/token" -f $TenantId

    $body = @{
        'scope'         = $Scope
        'client_id'     = $ApplicationId
        'grant_type'    = 'client_credentials'
        'client_secret' = $ApplicationSecret
    }

    $params = @{
        ContentType = 'application/x-www-form-urlencoded'
        Headers     = @{'accept' = 'application/json' }
        Body        = $body
        Method      = 'POST'
        URI         = $TokenEndpoint
    }

    Try {
        $token = Invoke-RestMethod @params
        Return $Token
    }
    Catch {
        Write-Error "Failed to obtain access token for scope '$Scope'. Exception message: $($_.Exception.Message). Error details: $($_.ErrorDetails)"
        Throw
    }
}


<#
.SYNOPSIS
The function returns a Key Vault secret value as plain text.

.DESCRIPTION
Retrieves and returns a Key Vault secret value as plain text.

.OUTPUTS
Secret plain text
#>
function Get-AzKeyVaultSecretAsPlainText {
   Param(
      # Key Vault Name
      [Parameter(Mandatory)][ValidateNotNull()][ValidateNotNullOrEmpty()]  
      [String]$KeyVaultName,
      # Secret Name for to be retrieved from the Key Vault.
      [Parameter(Mandatory)][ValidateNotNull()][ValidateNotNullOrEmpty()]  
      [String]$SecretName
    )  

    $Secret = (Get-AzKeyVaultSecret -VaultName $KeyVaultName -Name $SecretName)

    # This construct is needed for Windows PowerShell. In PowerShell 7+ ConvertFrom-SecureString could be used
    $ssPtr = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($secret.SecretValue)
    try {
       $secretValueText = [System.Runtime.InteropServices.Marshal]::PtrToStringBSTR($ssPtr)
    } finally {
       [System.Runtime.InteropServices.Marshal]::ZeroFreeBSTR($ssPtr)
    }
    return $secretValueText
}

<#
.SYNOPSIS
The function determines if the client is able to reach Sql Server.

.DESCRIPTION
The function is trying to connect to Sql Server and run a simple query.

If the operation is successful, the return result is True. 
Otherwise the function returns the client IP address if it was detected
or False if the client IP address was not detected.

.OUTPUTS
True, the client IP address or False.
#>
function Detect-SqlFirewallClientIp {
    param(
        $ConnectionParams
    )

    try {
        Write-Verbose "Trying to reach SQL server"
        $query = 'SELECT getdate() AS THE_DATE'
        $output = (Invoke-Sqlcmd @ConnectionParams -Query $query -ErrorVariable errors) | Out-String
        Return $output.Contains('THE_DATE') -eq $true
    } catch {
        Write-Verbose "Failed to reach SQL server. $($_.Exception.Message)"
    }

    if ($errors.Count -le 0) {
        Return $false
    }

    $message = $errors[0].ToString()
    $pattern = "([0-9]+)\.([0-9]+)\.([0-9]+)\.([0-9]+)"
    $regex = New-Object -TypeName System.Text.RegularExpressions.Regex -ArgumentList $pattern

    if ($message.Contains("sp_set_firewall_rule") -eq $true -and $regex.IsMatch($message) -eq $true) {
        $IpAddress = $regex.match($message).Groups[0].Value
        Return $IpAddress
    } else {
        Write-Verbose "Error message contains no sp_set_firewall_rule and ip address."
        Return $false
    }
}


$GuidPattern = "([0-9a-z]{8})(-[0-9a-z]{4}){3}-([0-9a-z]{12})"

<#
.SYNOPSIS

```powershell
$ResourceGroupName = 'svetlina'
$ServerName = 'svetlina-sqlsrv'
$IpAddress = '10.0.0.1'
$GuidPattern = "([0-9a-z]{8})(-[0-9a-z]{4}){3}-([0-9a-z]{12})"

$RuleName = "tmp-$(New-Guid)"
New-AzSqlServerFirewallRule -FirewallRuleName $RuleName -ResourceGroupName $ResourceGroupName -ServerName $ServerName -StartIpAddress $IpAddress -EndIpAddress $IpAddress

Remove-MatchingAzSqlFirewallRule -ResourceGroupName $ResourceGroupName -ServerResourceName $ServerName -FirewallRuleNamePattern "tmp-$GuidPattern" -Verbose
```

.DESCRIPTION
Remove Azure Sql Server firewall rules with names matching given pattern.

.OUTPUTS
True, the client IP address or False.
#>
function Remove-MatchingAzSqlFirewallRule {
    [cmdletbinding()]
    param(
        # Resource Group Name
        [string]$ResourceGroupName,
        # Azure Sql Server name - FQDN or resource name.
        [string]$ServerResourceName,
        # Pattern to match
        [string]$FirewallRuleNamePattern
    )

    $ServerResourceName = $ServerResourceName.Split('.')[0]
    $Rules = Get-AzSqlServerFirewallRule -ServerName $ServerResourceName -ResourceGroupName $ResourceGroupName

    $RuleNameMatchRegex = New-Object -TypeName System.Text.RegularExpressions.Regex -ArgumentList $FirewallRuleNamePattern

    $Rules | ForEach {
        if ($RuleNameMatchRegex.IsMatch($_.FirewallRuleName)) {
            Write-Verbose "Remove rule $($_.FirewallRuleName)"
            $Result = Remove-AzSqlServerFirewallRule -ServerName $ServerResourceName -ResourceGroupName $ResourceGroupName -FirewallRuleName $_.FirewallRuleName
        }
    }
}