Category Archives: AzureAD

Automation, AzureAD, Microsoft Teams, Office 365, Powershell, Skype Online

The mysterious X-MS-Forest header

Leave a comment

When working with the api.interfaces.records.teams.microsoft.com API, I noticed that the MS portal uses an X-MS-Forest header.

At first, ignoring this went fine as doing GET calls to this api didn’t seem to require it. But, of course the moment I wanted more, it suddenly WAS required (PUT/POST).

The question was; how does the portal determine the value for this header and how do we replicate that? Well, that wasn’t difficult: apparently a call to api.interfaces.records.teams.microsoft.com/Teams.Tenant/tenants suffices and returns the value for the X-MS-Forest header for the tenant identified in your token. Example:

    $headers = Get-GraphToken -tenantid $tenantId -scope "https://api.interfaces.records.teams.microsoft.com/user_impersonation"
    #get the correct forest
    $tenantInfo = Invoke-RestMethod -Method GET -uri "https://api.interfaces.records.teams.microsoft.com/Teams.Tenant/tenants" -UseBasicParsing -ContentType "application/json" -Headers $headers
    #add the X-MS-Forest header (required) for subsequent calls
    $headers["X-MS-Forest"] = $tenantInfo.serviceDiscovery.Headers.'X-MS-Forest'
Automation, AzureAD

ODBC SQL driver error

Leave a comment

If you know how to clear the ODBC Driver 17’s token cache, please comment 🙂


Ran into the following error today and putting it here for searches as literally nothing came up:

Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]User option must be specified, if Authentication option is 'ActiveDirectoryInteractive'.

This was through an excel Macro, connecting to an Azure PaaS database. All other users were just getting prompted, but one kept having this issue.

We managed to ‘solve’ the issue by going into the macro properties and adding User ID=xxx@xxx.com to the connection string where xxx@xxx.com was the user’s actual UPN.

Automation, Azure, AzureAD, EMS, Intune, Powershell, Security

Automated Stale Device Cleanup in Azure Active Directory using a runbook

28 Comments

As with cleaning up inactive guest users, inactive devices also pose several issues for organizations.

Microsoft recommends cleaning up stale devices after 90 days, but does not provide a service option or automation to do so.

Therefore, here’s another runbook you may run to just report on your inactive devices, or to automatically (and optionally periodically) clean up inactive devices in your environment when the removeInactiveDevices switch is supplied.

Managed identity

When run locally, interactive sign in is required. When running as a runbook in Azure automation, the Managed Identity of the automation account is leveraged. This requires you to set Device.ReadWrite.All or Device.Read.All permissions depending on if you want to script to do the cleanup as well.

Autopilot / on premises devices

Note that the script will log an error (and not attempt to delete the device) when a device is an autopilot record (not a real device) or when the device is synced from an on-premises active directory.

Disable vs Delete

The runbook also has a disable option, in which it will first disable a device and wait a configurable ($disableDurationInDays) period of time before actually deleting a device.

Reports

If you wish, you can also let the script mail you a report in CSV format. Add the Mail.Send graph permissions like you did with device permissions and give the MailFrom and MailTo parameters a value.

Download

Download get-AzureADInactiveDevices.ps1 from Gitlab

Disclaimer

As always, the script is provided as-is and should be reviewed and then used at your own risk.

Automation, AzureAD, Identity, Office 365, Powershell, Security

Guest User Last Sign-in date time in Azure Active Directory and automatic cleanup

24 Comments

Azure AD’s sign in logs also only go back 30 days, which makes it highly recommended to stream Azure AD’s sign in logs to a Log Analytics workspace (Azure Monitor). You just need one single P1 license in your tenant to be able to enable this.

However, even if you don’t stream your sign in logs, Microsoft does keep track of when an account last signed in.

My script gets the last sign in data of all guest accounts in your tenant, without any dependencies other than the Az PS module.

If a guest user has never signed in, the creationDate is used to determine inactivity. Otherwise either the last interactive or last non interactive sign in is used (whichever is most recent).

Additionally, the script can also be configured to automatically clean up any guest accounts that have been inactive for a given number of days by using the -removeInactiveGuests switch.

Even in large environments, processing only takes a few minutes at most.

Download

Download the script from my Gitlab here:

https://gitlab.com/Lieben/assortedFunctions/-/blob/master/get-AzureAdInactiveGuestUsers.ps1

Limitations

Microsoft started using these properties in april 2020, so accounts active before that will seem like they have never been active.

Scheduling

This script supports running non-interactive as a runbook in Azure Automation if you supply the -nonInteractive switch. Before this will work, you’ll have to enable Managed Identity on your automation account and run a small script to assign graph permissions to the Managed Identity: AuditLog.Read.All and Organization.Read.All

Reports

If you wish, you can also let the script mail you a report in CSV format. Add the Mail.Send graph permissions like you did with device permissions and give the MailFrom and MailTo parameters a value.

Disclaimer

As always this script is provided as-is and should be reviewed and then used at your own risk.

Automation, Azure, AzureAD, Identity, Office 365, PowerApps, Powershell

Inviting an external user to a PowerApp programmatically

Leave a comment

Another week, another use case for Managed Identities in Automation Accounts!

The scenario today concerns a PowerApp and connected resources that should be shared with external identities, automatically of course. For each user this requires a guest account in the host / resource tenant, and a license. The license can be applied in the home tenant of the guest, or in your tenant.

Key points:

  1. Runbook that invites a user and adds the resulting guest account to a security group
  2. Security group gives access to the PowerApp and underlying (SpO) resources, and uses Group Based Licensing to license the guest for PowerApps and Sharepoint Online
  3. Logic App that is triggered by the PowerApp (trigger on create item in a sharepoint list), and starts the runbook
  4. When the invited user (guest) redeems the invitation, they are directed to a Sharepoint page first so Sharepoint syncs their profile. Otherwise, the PowerApp will not have access to any lists in Sharepoint Online as Guests are not synced to SpO until they access SpO directly.

I may demo the PowerApp, Logic App and Sharepoint lists at some point, but the main thing I wanted to share today is the Azure Runbook that creates the Guest invitation and adds the Guest to a security group using the Managed Identity of the Automation account, instead of service accounts or other pre-2021 solutions:

https://gitlab.com/Lieben/assortedFunctions/-/blob/master/invite-guestUser.ps1