Share via


Azure: Send B2B Invitations using PowerShell

Introduction

 This script is used to send invitations to all guest users with InvitationStatus as Null. Script follows below steps:

  1. Read all guest users with InvitationStatus as Null from table [DBName].[dbo].[tblB2BInvitationStatus]
  2. Send Invitations to all un-invited guest users
  3. Update table [DBName].[dbo].[tblB2BInvitationStatus] with the new InvitationStatus and InvitationDate

Managing AzureAD authentication request using Service Principal object

Follow below article for managing AzureAD authentication request using Service Principal object.

This requires registering an app on Azure followed by providing "Invite guest users to the organization" permission using MS Graph API. Follow Example 3 on below article:

Script

# Functions Starts from  here
# Function  to read  guest user  data from  table [DBName].[dbo].[tblB2BInvitationStatus]
Function GetAllRecords
{
  PARAM ($SelectSQLCommand )
  try
  {
   ## open  database connection
   $SQLconnection. Open()
   #$cmd = $SQLconnection.CreateCommand()
   $SqlCommand. CommandText = $SelectSQLCommand
   $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
   $SqlAdapter. SelectCommand = $SqlCommand
   $dt = New-Object System.Data.DataSet
   $SqlAdapter. Fill($dt )
   $SQLconnection. Close()
   return $dt
  }
  catch
  {
   write-host "Caught an exception:"  -ForegroundColor Red
   write-host "Exception Type: $($_. Exception. GetType(). FullName)" -ForegroundColor Red
   write-host "Exception Message: $($_. Exception. Message)" -ForegroundColor Red
  }
}
# Function  to Update  table [DBName].[dbo].[tblB2BInvitationStatus] post sending invites.
Function UpdatetblB2BInvitationStatus
{
  param ([String] $AccountName,[String] $InvitationStatus,[String]$InvitationDate )
  $SqlCommand. Commandtext =
  "Update tblB2BInvitationStatus set InvitationStatus='$InvitationStatus',
  InvitationDate='$InvitationDate' where AccountName='$AccountName'"
  try
  {
   # Open  SQL Connection
   $SQLconnection. Open()
   if ($sqlConnection. State -eq [Data.ConnectionState]:: Open)
   {
    # Now I'll execute the query and close the database connection
    $SqlCommand. ExecuteNonQuery()
    $SQLconnection. Close()
   }
}
catch
{
  write-host "Caught an exception:" -ForegroundColor Red
  write-host "Exception Type: $($_. Exception. GetType(). FullName)" -ForegroundColor Red
  write-host "Exception Message: $($_. Exception. Message)" -ForegroundColor Red}
}
# Function to send B2B Invitation using PowerShell Cmdlets
Function SendInvitationToUsers
{
  param ([String] $DisplayName, [String] $Email )
  try
  {
   # Send B2B Invitation
   $InvitationResult = New-AzureADMSInvitation `
   -InvitedUserDisplayName $DisplayName `
   -InvitedUserEmailAddress $Email `
   -SendInvitationMessage $false `
   -InviteRedirectUrl "http://myapps.microsoft.com"
   return $InvitationResult
  }
  catch
  {
   write-host "Caught an exception:" -ForegroundColor Red
   write-host "Exception Type: $($_. Exception. GetType(). FullName)" -ForegroundColor Red
   write-host "Exception Message: $($_. Exception. Message)" -ForegroundColor Red
  }
}
# Variables
# New-AzureADMSInvitation return Object
$InvitationResult = New-Object psobject
#Guest User Invitation Status
$InvitationStatus = New-Object psobject
# Guest User InvitationDate
$InvitationDate = New-Object psobject
# Provide B2B Invite AzureAD Application ID
$B2BInviteapplication = "XXXX-XXXX-XXXX-XXXX-XXXXXXXX"
# Provide Source Azure AD Tenant ID
$SourceAzureADTenantID = "XXXX-XXXX-XXXX-XXXX-XXXXXXXX"
# Get local Self Signed Certificate
$Cert = Get-ChildItem -Path cert:\LocalMachine\my -DNSName "*foo*"
# These lines creates new database connection
$SQLconnection = New-Object System.Data.SqlClient.SqlConnection
$SQLconnection. ConnectionString = "Data Source=SQLServerName;Initial Catalog=DBName;Integrated Security=SSPI;"
# These two lines create a SQL command, which is what is used to send select query to SQL Server
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand
$SqlCommand. Connection = $SQLconnection
# Select SQL query to get all Guest users with InvitationStatatus as 'Null'
$SelectSQLCommand =
"Select UserPrincipalName,FirstName,LastName,AccountName,Email from [DBName].[dbo].[tblB2BInvitationStatus] where InvitationStatus is null"
# Create new SQL DataSet Object.
$GuestUsersDataSet = New-Object System.Data.DataSet
$GuestUsersDataSet = GetAllRecords -SelectSQLCommand $SelectSQLCommand
if ($GuestUsersDataSet. Tables[0] )
{
  # Connect with AzureAD Tenant using Service Principal Object.
  Connect-AzureAD -TenantId $SourceAzureADTenantID -ApplicationId $B2BInviteapplication -CertificateThumbprint     $Cert. Thumbprint
  foreach ($Row in $GuestUsersDataSet. Tables[0]. Rows)
  {
  # Send Invitation to guest users with null InvitationStatus
  $InvitationResult =
  SendInvitationToUsers -DisplayName
  ($Row. FirstName + ' ' + $Row. LastName) -Email $Row. Email
  if ($InvitationResult )
  {
   UpdatetblB2BInvitationStatus `
   -AccountName $Row. AccountName `
   -InvitationStatus $InvitationResult. Status `
   -InvitationDate (Get-Date ). ToString("yyyy-MM-dd" )
   Write-host "B2B Invite Sent to "  $Row. AccountName -ForegroundColor Green
   $InvitationResult = $null
   }
  }
}