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 interaction_required error since a conditional access policy is not met for Azure resources.
Example:
HTTP/1.1 400 Bad Request
"error":"interaction_required"
"error_description":"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 'xxx”
“claims”:” {""access_token"":{""capolids"":{""essential"":true,""values"":[""yyy""]}}}”

To handle this, the user need to catch the interaction_required 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

#Loads Active Directory Authentication Library
function Load-ActiveDirectoryAuthenticationLibrary(){
    $moduleDirPath = [Environment]::GetFolderPath("MyDocuments") + "\WindowsPowerShell\Modules"
    $modulePath = $moduleDirPath + "\AADGraph"

    if(-not (Test-Path ($modulePath+"\Nugets"))) {New-Item -Path ($modulePath+"\Nugets") -ItemType "Directory" | out-null}
    $adalPackageDirectories = (Get-ChildItem -Path ($modulePath+"\Nugets") -Filter "Microsoft.IdentityModel.Clients.ActiveDirectory*" -Directory)

    if($adalPackageDirectories.Length -eq 0){
        Write-Host "Active Directory Authentication Library Nuget doesn't exist. Downloading now ..." -ForegroundColor Yellow
        if(-not(Test-Path ($modulePath + "\Nugets\nuget.exe")))
        {
            Write-Host "nuget.exe not found. Downloading from http://www.nuget.org/nuget.exe ..." -ForegroundColor Yellow
            $wc = New-Object System.Net.WebClient
            $wc.DownloadFile("http://www.nuget.org/nuget.exe",$modulePath + "\Nugets\nuget.exe");
        }
        $nugetDownloadExpression = $modulePath + "\Nugets\nuget.exe install Microsoft.IdentityModel.Clients.ActiveDirectory -Version 2.14.201151115 -OutputDirectory " + $modulePath + "\Nugets | out-null"
        Invoke-Expression $nugetDownloadExpression
    }

    $adalPackageDirectories = (Get-ChildItem -Path ($modulePath+"\Nugets") -Filter "Microsoft.IdentityModel.Clients.ActiveDirectory*" -Directory)
    $ADAL_Assembly = (Get-ChildItem "Microsoft.IdentityModel.Clients.ActiveDirectory.dll" -Path $adalPackageDirectories[$adalPackageDirectories.length-1].FullName -Recurse)
    $ADAL_WindowsForms_Assembly = (Get-ChildItem "Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll" -Path $adalPackageDirectories[$adalPackageDirectories.length-1].FullName -Recurse)
    if($ADAL_Assembly.Length -gt 0 -and $ADAL_WindowsForms_Assembly.Length -gt 0){
        Write-Host "Loading ADAL Assemblies ..." -ForegroundColor Green
        [System.Reflection.Assembly]::LoadFrom($ADAL_Assembly[0].FullName) | out-null
        [System.Reflection.Assembly]::LoadFrom($ADAL_WindowsForms_Assembly.FullName) | out-null
        return $true
    }
    else{
        Write-Host "Fixing Active Directory Authentication Library package directories ..." -ForegroundColor Yellow
        $adalPackageDirectories | Remove-Item -Recurse -Force | Out-Null
        Write-Host "Not able to load ADAL assembly. Delete the Nugets folder under" $modulePath ", restart PowerShell session and try again ..."
        return $false
    }
}

#Acquire AAD token
function AcquireToken($queryParamater){
    $clientID = "dabc52c4-106b-4179-9df2-2f791f44ba14"
    $redirectUri = "https://pimmsgraph"

    $authority = "https://login.microsoftonline.com/common"
    $authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority,$false
    if($queryParamater -ne $null)
    {
        $authResult = $authContext.AcquireToken("https://graph.microsoft.com",$ClientID,$redirectUri,[Microsoft.IdentityModel.Clients.ActiveDirectory.PromptBehavior]::Auto, [Microsoft.IdentityModel.Clients.ActiveDirectory.UserIdentifier]::AnyUser, $queryParamater)
        Set-Variable -Name mfaDone -Value $true -Scope Global
    }
    else
    {
        $authResult = $authContext.AcquireToken("https://graph.microsoft.com",$ClientID,$redirectUri,[Microsoft.IdentityModel.Clients.ActiveDirectory.PromptBehavior]::Always)
    }
    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;

Load-ActiveDirectoryAuthenticationLibrary
AcquireToken

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("interaction_required"))
    {
        $errorObject = ConvertFrom-Json $err
        $message = ConvertFrom-Json $errorObject.error.message
        $queryString = "claims=" + $message.claims
        Write-Host "Prompting the user again since since a conditional access policy is enabled..." -ForegroundColor Green
        AcquireToken $queryString
        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;

Wednesday, July 18, 2018

OData Client Code Generator for VS 2015

I had been using OData Client Code Generator for VS 2015 to generate client for my OData service.
See https://blogs.msdn.microsoft.com/odatateam/2014/03/11/tutorial-sample-how-to-use-odata-client-code-generator-to-generate-client-side-proxy-class/

The extension used to be deployed in my VS 2015 but recently it disappeared.
When I tried to search for it in Extension and Updates, it would show a newer version of OData Client Code Generator which won't work for VS 2015.

After looking around, I found the older version here https://github.com/OData/lab/blob/Tools/Tools/ODataT4ItemTemplate.2.4.0.vsix
You can download and install it from here and restart VS 2015.

After that, you should be able to generate the OData client as usual in VS 2015.

Thursday, June 7, 2018

PowerShell sample for Privileged Identity Management (PIM) for Azure AD Roles

PIM for Azure AD Roles provides Just in Time (JIT) capability for Azure AD Roles. See more at https://docs.microsoft.com/en-us/azure/active-directory/active-directory-privileged-identity-management-getting-started 
How cool would it be if I can use the MSGraph PIM api’s to build custom applications. For example, you have multiple roles where you want to activate every day. It would be time consuming to activate them one by one. Instead, you can build a custom app using PowerShell or UI so that you can activate to all of these roles in one shot.
In this blog, I will share a sample to list all your eligible roles and activate or deactivate them. You will also be able to assign someone to a role.
I will share the full source code so you can customize it to suit your needs. Just save this as a .ps1 file and run it with PowerShell.
Note: There is a limitation with MSGraph PIM api's that it wont work if your role requires MFA which should be fixed soon.

Screenshot
Setup
Source code
#Loads Active Directory Authentication Library
function Load-ActiveDirectoryAuthenticationLibrary(){
    $moduleDirPath = [Environment]::GetFolderPath("MyDocuments") + "\WindowsPowerShell\Modules"
    $modulePath = $moduleDirPath + "\AADGraph"

    if(-not (Test-Path ($modulePath+"\Nugets"))) {New-Item -Path ($modulePath+"\Nugets") -ItemType "Directory" | out-null}
    $adalPackageDirectories = (Get-ChildItem -Path ($modulePath+"\Nugets") -Filter "Microsoft.IdentityModel.Clients.ActiveDirectory*" -Directory)

    if($adalPackageDirectories.Length -eq 0){
        Write-Host "Active Directory Authentication Library Nuget doesn't exist. Downloading now ..." -ForegroundColor Yellow
        if(-not(Test-Path ($modulePath + "\Nugets\nuget.exe")))
        {
            Write-Host "nuget.exe not found. Downloading from http://www.nuget.org/nuget.exe ..." -ForegroundColor Yellow
            $wc = New-Object System.Net.WebClient
            $wc.DownloadFile("http://www.nuget.org/nuget.exe",$modulePath + "\Nugets\nuget.exe");
        }
        $nugetDownloadExpression = $modulePath + "\Nugets\nuget.exe install Microsoft.IdentityModel.Clients.ActiveDirectory -Version 2.14.201151115 -OutputDirectory " + $modulePath + "\Nugets | out-null"
        Invoke-Expression $nugetDownloadExpression
    }

    $adalPackageDirectories = (Get-ChildItem -Path ($modulePath+"\Nugets") -Filter "Microsoft.IdentityModel.Clients.ActiveDirectory*" -Directory)
    $ADAL_Assembly = (Get-ChildItem "Microsoft.IdentityModel.Clients.ActiveDirectory.dll" -Path $adalPackageDirectories[$adalPackageDirectories.length-1].FullName -Recurse)
    $ADAL_WindowsForms_Assembly = (Get-ChildItem "Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll" -Path $adalPackageDirectories[$adalPackageDirectories.length-1].FullName -Recurse)
    if($ADAL_Assembly.Length -gt 0 -and $ADAL_WindowsForms_Assembly.Length -gt 0){
        Write-Host "Loading ADAL Assemblies ..." -ForegroundColor Green
        [System.Reflection.Assembly]::LoadFrom($ADAL_Assembly[0].FullName) | out-null
        [System.Reflection.Assembly]::LoadFrom($ADAL_WindowsForms_Assembly.FullName) | out-null
        return $true
    }
    else{
        Write-Host "Fixing Active Directory Authentication Library package directories ..." -ForegroundColor Yellow
        $adalPackageDirectories | Remove-Item -Recurse -Force | Out-Null
        Write-Host "Not able to load ADAL assembly. Delete the Nugets folder under" $modulePath ", restart PowerShell session and try again ..."
        return $false
    }
}
 
#Acquire AAD token
function AcquireToken($mfa){
    $clientID = "c7c64917-42bd-4a36-8ed6-af40122626eb"
    $redirectUri = "https://pimmsgraph"
 
    $authority = "https://login.microsoftonline.com/common"
    $authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority,$false
    if($mfa)
    {
        $authResult = $authContext.AcquireToken("https://graph.microsoft.com",$ClientID,$redirectUri,[Microsoft.IdentityModel.Clients.ActiveDirectory.PromptBehavior]::Auto, [Microsoft.IdentityModel.Clients.ActiveDirectory.UserIdentifier]::AnyUser, "amr_values=mfa")
        Set-Variable -Name mfaDone -Value $true -Scope Global
    }
    else
    {
        $authResult = $authContext.AcquireToken("https://graph.microsoft.com",$ClientID,$redirectUri,[Microsoft.IdentityModel.Clients.ActiveDirectory.PromptBehavior]::Always)
    }
    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
}
 
#Gets my jit assignments
function MyJitAssignments(){
    $url = $serviceRoot + "privilegedRoleAssignments/my?`$expand=roleInfo&`$filter=isElevated+eq+false" 

    Write-Host $url
    $response = Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri $url -Method Get
    $assignments = ConvertFrom-Json $response.Content
    Write-Host ""
    Write-Host "Role assignments..." -ForegroundColor Green
    $i = 0
    $obj = @()
    foreach ($assignment in $assignments.value)
    {
        $item = New-Object psobject -Property @{
        Id = ++$i
        RoleAssignmentId =  $assignment.id
        RoleId =  $assignment.roleInfo.id
        RoleName =  $assignment.roleInfo.name
        UserId = $assignment.userid
    }
    $obj = $obj + $item
    }
 
    return $obj
}
 
#Gets my active assignments
function MyActivatedAssignments(){
    $url = $serviceRoot + "privilegedRoleAssignments/my?`$expand=roleInfo&`$filter=isElevated+eq+true+and+expirationDateTime+ne+null" 

    Write-Host $url
    $response = Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri $url -Method Get
    $assignments = ConvertFrom-Json $response.Content
    Write-Host ""
    Write-Host "Role assignments..." -ForegroundColor Green
    $i = 0
    $obj = @()
    foreach ($assignment in $assignments.value)
    {
        $item = New-Object psobject -Property @{
        Id = ++$i
        RoleAssignmentId =  $assignment.id
        RoleId =  $assignment.roleInfo.id
        RoleName =  $assignment.roleInfo.name
        UserId = $assignment.userid
        ExpirationDateTime = $assignment.expirationDateTime
    }
    $obj = $obj + $item
    }
 
    return $obj
}

#List roles
function ListRoles(){
    $url = $serviceRoot + "privilegedRoles?&`$orderby=name"
    Write-Host $url

    $response = Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri $url -Method Get
    $roles = ConvertFrom-Json $response.Content
    $i = 0
    $obj = @()
    foreach ($role in $roles.value)
    {
        $item = New-Object psobject -Property @{
        Id = ++$i
        RoleId =  $role.id
        RoleName =  $role.name
    }
    $obj = $obj + $item
    }
 
    return $obj
}

#List Assignment
function ListAssignmentsWithFilter($roleId){
    $url = $serviceRoot + "privilegedRoleAssignments?`$expand=roleInfo&`$filter=roleId+eq+'" + $roleId + "'"
    Write-Host $url

    $response = Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri $url -Method Get
    $roleAssignments = ConvertFrom-Json $response.Content
    $i = 0
    $obj = @()
    foreach ($roleAssignment in $roleAssignments.value)
        {
        $item = New-Object psobject -Property @{
        Id = ++$i
        RoleAssignmentId =  $roleAssignment.id
        RoleId = $roleAssignment.roleInfo.id
        RoleName = $roleAssignment.roleInfo.name
        IsElevated = $roleAssignment.isElevated
        ExpirationDateTime = $roleAssignment.expirationDateTime
        UserId = $roleAssignment.userId
    }
    $obj = $obj + $item
}
 
return $obj
}

#List Users
function ListUsers($user_search){
    $url = $MSGraphRoot + "users?`$filter=startswith(displayName,'" + $user_search + "')"
    Write-Host $url

    $response = Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri $url -Method Get
    $users = ConvertFrom-Json $response.Content
    $i = 0
    $obj = @()
    foreach ($user in $users.value)
    {
        $item = New-Object psobject -Property @{
        Id = ++$i
        UserId =  $user.id
        UserName =  $user.DisplayName
    }
    $obj = $obj + $item
    }

    return $obj
}

#Activates the user
function Activate($isRecursive = $false){
    if($isRecursive -eq $false)
    {
        $assignments = MyJitAssignments
        $assignments | Format-Table -AutoSize -Wrap Id,RoleName
        $choice = Read-Host "Enter Id to activate"
        $hours = Read-Host "Enter Activation duration in hours"
        $reason = Read-Host "Enter Reason"
    }

    $roleId = $assignments[$choice-1].RoleId
    $url = $serviceRoot + "privilegedRoles('" + $roleId + "')/selfActivate"
    $postParams = '{"duration":"' + $hours + '","reason":"' + $reason + '"}'
    write-Host $postParams

    try
    {
        $response = Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri $url -Method Post -ContentType "application/json" -Body $postParams
        Write-Host "Role activated successfully ..." -ForegroundColor Green
    }
    catch
    {
        $stream = $_.Exception.Response.GetResponseStream()
        $stream.Position = 0;
        $streamReader = New-Object System.IO.StreamReader($stream)
        $err = $streamReader.ReadToEnd()
        $streamReader.Close()
        $stream.Close()
 
        if($mfaDone -eq $false -and $err.Contains("MfaRule"))
        {
            Write-Host "Prompting the user for mfa ..." -ForegroundColor Green
            AcquireToken true
            Activate $true
        }
        else
        {
            Write-Host $err -ForegroundColor Red
        }
    }
}
 
#Deactivates the user
function Deactivate($isRecursive = $false){
    if($isRecursive -eq $false)
    {
        $assignments = MyActivatedAssignments
        $assignments | Format-Table -AutoSize -Wrap Id,RoleName,ExpirationDateTime
        $choice = Read-Host "Enter Id to deactivate"
    }

    $roleId = $assignments[$choice-1].RoleId
    $url = $serviceRoot + "privilegedRoles('" + $roleId + "')/selfDeactivate"
    $response = Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri $url -Method Post -ContentType "application/json"
    Write-Host "Role deactivated successfully ..." -ForegroundColor Green
}
 
#List RoleAssignment
function ListAssignment(){
    #List and Pick a role
    $roles = ListRoles
    $roles | Format-Table -AutoSize -Wrap Id, RoleName, RoleId
    $role_choice = Read-Host "Pick a role Id"
    $roleId = $roles[$role_choice-1].RoleId
    write-Host $roleId

    #List Member
    $roleAssignments = ListAssignmentsWithFilter $roleId
    $roleAssignments | Format-Table -AutoSize -Wrap Id, RoleName, UserId, IsElevated, ExpirationDateTime
}

#Assign a user to Eligible
function AssignmentEligible() {
    #List and Pick a role
    $roles = ListRoles
    $roles | Format-Table -AutoSize -Wrap Id, RoleName
    $role_choice = Read-Host "Pick a role Id"
    $roleId = $roles[$role_choice-1].RoleId
    write-Host $roleId

    #Search user by Name, and pick a user
    $user_search = Read-Host "user Name start with..."
    $users = ListUsers($user_search)
    $users | Format-Table -AutoSize -Wrap Id, UserName, UserId
    $user_choice = Read-Host "Pick a user Id"
    $userId = $users[$user_choice-1].UserId


    $url = $serviceRoot + "privilegedRoleAssignments"
    $postParams = '{"roleId":"' + $roleId + '","userId":"' + $userId + '"}'
    write-Host $postParams
        
    $response = Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri $url -Method Post -ContentType "application/json" -Body $postParams
    Write-Host "Assignment added successfully ..." -ForegroundColor Green
}


#Show menu
function ShowMenu(){
    Write-Host ""
    Write-Host "Azure AD JIT - PowerShell Menu v1.0"
    Write-Host "  1. List your eligible role assignments"
    Write-Host "  2. Activate an eligible role"
    Write-Host "  3. Deactivate an active role"
    Write-Host "  4. List Assignment against a role"
    Write-Host "  5. Assign a user to a role"
    Write-Host "  6. Exit"
}
 
############################################################################################################################################################################
 
$global:serviceRoot = "https://graph.microsoft.com/beta/"
$global:MSGraphRoot = "https://graph.microsoft.com/v1.0/"
$global:headerParams = ""
$global:assigneeId = ""
$global:mfaDone = $false;
 
Load-ActiveDirectoryAuthenticationLibrary
AcquireToken
 
do
{
    ShowMenu
    #Write-Host "Enter your selection"
    $input = Read-Host "Enter your selection"
    switch ($input)
    {
        '1'
        {
            $assignments = MyJitAssignments
            $assignments | Format-Table -AutoSize -Wrap Id,RoleName
        }
        '2'
        {
            Activate
        }
        '3'
        {
            Deactivate
        }
        '4'
        {
            ListAssignment
        }
        '5'
        {
            AssignmentEligible
        }
        '6'
        {
            return
        }
    }
}
until ($input -eq '6')
 
Write-Host ""

Friday, May 11, 2018

user_interaction_required – Not able to add/refresh account in VS 2015


One day all of a sudden most of our team members were not able to add/refresh their account in VS 2015. We were getting an error
---------------------------
Microsoft Visual Studio
---------------------------
We could not refresh the credentials for the account xxx

user_interaction_required: One of two conditions was encountered: 1. The PromptBehavior.Never flag was passed, but the constraint could not be honored, because user interaction was required. 2. An error occurred during a silent web authentication that prevented the http authentication flow from completing in a short enough time frame
---------------------------
OK  
---------------------------
I then started looking at the network traces to figure out what’s wrong. I saw that there was an interaction between login.microsoftonline.com and tokenprovider.termsofuse.identitygovernance.azure.com after which the error would occur.


Now, I saw a prompt to accept Terms Of Use in a different tenant that my home tenant. Looked like someone had enabled a Terms of Use Conditional Access policy on that tenant. See more details about Terms of Use here https://docs.microsoft.com/en-us/azure/active-directory/active-directory-tou

On analyzing more, it looks like VS tries to get a token for all the tenants you belong to. If one of the tenant has a Conditional Access policy like Terms of Use which requires a user input, VS 2015 will not be able to show it to you. So will you have to upgrade to VS 2017 or disable the Conditional Access policy.

Once this is done, everything should start working as usual.