Friday, August 2, 2019

Handle Conditional Access challenge for Privileged Identity Management on Microsoft Graph

Privileged Identity Management (PIM) for Azure resources api’s are available on Microsoft Graph (MSGraph) so that developers can automate the PIM operations like activation, assignment, etc. To learn more, see http://www.anujchaudhary.com/2018/02/powershell-sample-for-privileged.html

Some organizations enable conditional policies like Multi factor authentication (MFA) for accessing any Azure resources. When users go to PIM through Azure Portal, they are prompted for MFA while logging into the Azure Portal. When they access the PIM UI, everything works since they have already performed MFA.

However, if the users are accessing PIM api’s for Azure resources through MSGraph, they might not be prompted for MFA on login since no conditional access policy might be enabled for MSGraph. When a PIM api is called, it fails with 400 Bad Request invalid_grant error since a conditional access policy is not met for Azure resources.
Example:
HTTP/1.1 400 Bad Request
{
  "error": {
    "code": "invalid_grant",
    "message": "{\"Name\":\"MsalUiRequiredException\",\"Message\":\"AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access '797f4846-ba00-4fd7-ba43-dac1f8f63013'.\\r\\nTrace ID: 7bdbe148-89e6-4493-a150-93dac7a06c00\\r\\nCorrelation ID: ff221ee5-ebb9-42d0-8f70-dbffde1b2104\\r\\nTimestamp: 2020-09-16 01:16:03Z\",\"Claims\":\"{\\\"access_token\\\":{\\\"capolids\\\":{\\\"essential\\\":true,\\\"values\\\":[\\\"051744ca-6abe-4095-b526-14a7f4033309\\\"]}}}\"}",
    "innerError": {
      "date": "2020-09-16T01:16:03",
      "request-id": "82cd21d1-6413-4512-a2d1-fa1d0a3c5826",
      "client-request-id": "82cd21d1-6413-4512-a2d1-fa1d0a3c5826"
    }
  }
}

To handle this, the user needs to catch the error, get the claims challenge and send in a login request with claims challenge as an extra query string parameter.


Below is a PowerShell sample which showcases on how to handle the conditional access challenge when calling PIM api's on MSGraph. Just save this as a .ps1 file and run it with PowerShell.

Sceenshot










Source code

#Acquire AAD token
function AcquireToken($claims){
    $clientID = "dabc52c4-106b-4179-9df2-2f791f44ba14"
    $redirectUri = "https://pimmsgraph"
 
    $authority = "https://login.microsoftonline.com/common"
    if($claims -ne $null)
    {
        $authResult = Get-MSALToken -Scopes @("https://graph.microsoft.com/.default") -ClientId $ClientID -RedirectUri $redirectUri -Authority $authority -Interactive -ExtraQueryParameters @{claims=$claims}
        Set-Variable -Name mfaDone -Value $true -Scope Global
    }
    else
    {
        $authResult = Get-MSALToken -Scopes @("https://graph.microsoft.com/.default") -ClientId $ClientID -RedirectUri $redirectUri -Authority $authority -Interactive
    }
    if($authResult -ne $null)
    {
        Write-Host "User logged in successfully ..." -ForegroundColor Green
    }
    Set-Variable -Name headerParams -Value @{'Authorization'="$($authResult.AccessTokenType) $($authResult.AccessToken)"} -Scope Global
    Set-Variable -Name assigneeId -Value $authResult.UserInfo.UniqueId -Scope Global

#List resources
function ListResources(){
    $url = $serviceRoot + "resources?`$filter=(type+eq+'subscription')" 
     Write-Host $url

    $response = Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri $url -Method Get
    $resources = ConvertFrom-Json $response.Content
    $i = 0
    $obj = @()
    foreach ($resource in $resources.value)
    {
        $item = New-Object psobject -Property @{
        Id = ++$i
        ResourceId =  $resource.id
        ResourceName =  $resource.displayName
        ResourceType =  $resource.type
    }
    $obj = $obj + $item
}
 
return $obj
}

#Disaplay resources
function DisplayResources(){
    $resources = ListResources
    $resources | Format-Table -AutoSize -Wrap Id,ResourceName,ResourceType
}
 
############################################################################################################################################################################
 
$global:serviceRoot = "https://graph.microsoft.com/beta/privilegedAccess/azureResources/"
$global:MSGraphRoot = "https://graph.microsoft.com/v1.0/"
$global:headerParams = ""
$global:assigneeId = ""
$global:mfaDone = $false;
 
# Install msal.ps
if(!(Get-Module | Where-Object {$_.Name -eq 'PowerShellGet' -and $_.Version -ge '2.2.4.1'})) { Install-Module PowerShellGet -Force }
if(!(Get-Package msal.ps)) { Install-Package msal.ps }

$Authed = AcquireToken $global:clientID $global:redirectUri $global:resourceAppIdURI $global:authority $false
if ($Authed -eq $false)
{
    return
}

try
{
    DisplayResources
}
catch
{
    $stream = $_.Exception.Response.GetResponseStream()
    $stream.Position = 0;
    $streamReader = New-Object System.IO.StreamReader($stream)
    $err = $streamReader.ReadToEnd()
    $streamReader.Close()
    $stream.Close()
 
    if($err.Contains("invalid_grant"))
    {
        $errorObject = ConvertFrom-Json $err
        $message = ConvertFrom-Json $errorObject.error.message
        $claims = $message.claims
        Write-Host "Prompting the user again since since a conditional access policy is enabled..." -ForegroundColor Green
        AcquireToken $claims
        DisplayResources
    }
    else
    {
        Write-Host $err -ForegroundColor Red
    }
}

 
Write-Host ""

Thursday, June 20, 2019

SQL interceptors

SQL interceptors are a way to apply filtering by tenant for securing multi-tenant applications. Here is a good read on it http://xabikos.com/2014/11/18/Create-a-multitenant-application-with-Entity-Framework-Code-First-Part-2/

However, you need to be careful with it since they modify your query at runtime. 
Specifically, DbExpressionBuilder.Bind(databaseExpression) in the interceptor causes a random variable to be created which creates a random query text for the same query on every reinitialize which is generally a recycle on the VM where the application is running.

This puts unnecessary unnecessary pressure on QDS (Query Data Store).
Also, if you force a query plan for a specific query hash, a new query hash will be generated the next time so the forced plan will not work.

To fix this, make sure to bind it with a specific variable name like DbExpressionBuilder.BindAs(databaseExpression, "Filter")

Troubleshooting SQL Azure issues

Query Performance Insights

The most common place to look for SQL Azure issues is Query Performance Insights on Azure portal https://docs.microsoft.com/en-us/azure/sql-database/sql-database-query-performance

Troubleshoot SQL query timeouts

There are various Data Management Views (DMV’s) created by SQL Azure team https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs
I tweaked them a little below:


Find query hashes which are timing out


Look for Aborted_Execution_Count column.
-- Top 15 CPU consuming queries by query hash
-- note that a query  hash can have many query id if not parameterized or not parameterized properly
-- it grabs a sample query text by min
WITH AggregatedCPU AS (SELECT q.query_hash, p.query_plan_hash, SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec, SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions) AS avg_cpu_millisec, MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec, MAX(max_logical_io_reads) max_logical_reads, COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans, COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids, SUM(CASE WHEN rs.execution_type_desc='Aborted' THEN count_executions ELSE 0 END) AS Aborted_Execution_Count, SUM(CASE WHEN rs.execution_type_desc='Regular' THEN count_executions ELSE 0 END) AS Regular_Execution_Count, SUM(CASE WHEN rs.execution_type_desc='Exception' THEN count_executions ELSE 0 END) AS Exception_Execution_Count, SUM(count_executions) AS total_executions, MIN(qt.query_sql_text) AS sampled_query_text
                       FROM sys.query_store_query_text AS qt
                            JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
                            JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
                            JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_id
                            JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=rs.runtime_stats_interval_id
                       WHERE rs.execution_type_desc IN ('Regular', 'Aborted', 'Exception')AND rsi.start_time>=DATEADD(HOUR, -24, GETUTCDATE())
                       GROUP BY q.query_hash, p.query_plan_hash), OrderedCPU AS (SELECT query_hash, query_plan_hash, total_cpu_millisec, avg_cpu_millisec, max_cpu_millisec, max_logical_reads, number_of_distinct_plans, number_of_distinct_query_ids, total_executions, Aborted_Execution_Count, Regular_Execution_Count, Exception_Execution_Count, sampled_query_text, ROW_NUMBER() OVER (ORDER BY total_cpu_millisec DESC, query_hash ASC) AS RN
                                                              FROM AggregatedCPU)
SELECT OD.query_hash, OD.query_plan_hash, OD.total_cpu_millisec, OD.avg_cpu_millisec, OD.max_cpu_millisec, OD.max_logical_reads, OD.number_of_distinct_plans, OD.number_of_distinct_query_ids, OD.total_executions, OD.Aborted_Execution_Count, OD.Regular_Execution_Count, OD.Exception_Execution_Count, OD.sampled_query_text, OD.RN
FROM OrderedCPU AS OD
WHERE OD.RN<=15
ORDER BY total_cpu_millisec DESC;


Compare query plans for timing out query hash


Look for avg_cpu_millisec column. The query plan with lower value is better. If there is only one query plan, then you need to change your query to use the right indexes.
-- Top 15 CPU consuming queries by query hash
-- note that a query  hash can have many query id if not parameterized or not parameterized properly
-- it grabs a sample query text by min
WITH AggregatedCPU AS (SELECT q.query_hash, p.query_plan_hash, SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec, SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions) AS avg_cpu_millisec, MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec, MAX(max_logical_io_reads) max_logical_reads, COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans, COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids, SUM(CASE WHEN rs.execution_type_desc='Aborted' THEN count_executions ELSE 0 END) AS Aborted_Execution_Count, SUM(CASE WHEN rs.execution_type_desc='Regular' THEN count_executions ELSE 0 END) AS Regular_Execution_Count, SUM(CASE WHEN rs.execution_type_desc='Exception' THEN count_executions ELSE 0 END) AS Exception_Execution_Count, SUM(count_executions) AS total_executions, MIN(qt.query_sql_text) AS sampled_query_text
                       FROM sys.query_store_query_text AS qt
                            JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
                            JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
                            JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_id
                            JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=rs.runtime_stats_interval_id
                       WHERE rs.execution_type_desc IN ('Regular', 'Aborted', 'Exception')AND rsi.start_time>=DATEADD(HOUR, -24, GETUTCDATE())
                       GROUP BY q.query_hash, p.query_plan_hash), OrderedCPU AS (SELECT query_hash, query_plan_hash, total_cpu_millisec, avg_cpu_millisec, max_cpu_millisec, max_logical_reads, number_of_distinct_plans, number_of_distinct_query_ids, total_executions, Aborted_Execution_Count, Regular_Execution_Count, Exception_Execution_Count, sampled_query_text, ROW_NUMBER() OVER (ORDER BY total_cpu_millisec DESC, query_hash ASC) AS RN
                                                              FROM AggregatedCPU)
SELECT OD.query_hash, OD.query_plan_hash, OD.total_cpu_millisec, OD.avg_cpu_millisec, OD.max_cpu_millisec, OD.max_logical_reads, OD.number_of_distinct_plans, OD.number_of_distinct_query_ids, OD.total_executions, OD.Aborted_Execution_Count, OD.Regular_Execution_Count, OD.Exception_Execution_Count, OD.sampled_query_text, OD.RN
FROM OrderedCPU AS OD
WHERE OD.query_hash= query_hash
ORDER BY total_cpu_millisec DESC;


Analyze the query plan


Once you have the query plan hash for which the query is timing out, you can view and analyze it by running this query
select qsq.query_hash
 ,qsp.query_plan_hash
 ,qsq.query_id
 ,qsp.plan_id
 ,qsq.query_text_id
 ,qsq.is_internal_query
 ,qsrts.first_execution_time
 ,qsrts.last_execution_time
 ,qsqt.query_sql_text
 ,CAST(qsp.query_plan AS XML)
 ,qsrts.count_executions
from
 [sys].[query_store_runtime_stats] qsrts
 inner join [sys].[query_store_plan] qsp on qsrts.plan_id = qsp.plan_id
 inner join [sys].[query_store_query] qsq on qsp.query_id = qsq.query_id
 inner join [sys].[query_store_query_text] qsqt on qsq.query_text_id = qsqt.query_text_id
where  qsp.query_plan_hash = query_plan_hash


Force a query plan


If you have multiple query plans for a query, where one query plan is performing far better than the other, you can force that query plan. You need to analyze both query plans before you decide a force a specific plan so that it will work for both large and small volume of data.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-query-store-force-plan-transact-sql?view=sql-server-2017

select * from sys.query_store_plan where is_forced_plan=1

EXEC sp_query_store_force_plan query_id, plan_id;