How to bulk create users in a hybrid environment with Office 365 Exchange Online

In a hybrid Exchange environment, user accounts are created on-premises, but then licensed through the Office 365 portal (to enable mailbox access). Admins often provision hybrid user accounts incorrectly, and sometimes this needs to be cleaned up after the fact.  But, if you want to do it right the first time, then PowerShell is your one-stop shop for getting the job done, both on-premises, and in the cloud.

Whether you are creating just one user account or several, this method can be used (for a single account, you would just fill in a single line on the CSV file). If you want to use these scripts, you will require the following:

1. On-premises Exchange Server 2013 or 2016 w/ the Exchange management shell
2. Azure AD Connect
3. Microsoft Online Service Sign-in Assistant for IT Professionals RTW
4. Windows Azure Active Directory Module for Windows PowerShell (64-bit version)

It is best if all of these are available on the same server, but if for example you have Azure AD Connect installed on a different server than your Exchange management shell, just know that you cannot run the manual delta sync without access to Azure AD Connect.

To begin, you will have a script that provisions user accounts on the local Exchange Server and in the local AD. The beginning of this script is a set of parameters (variables) that will be leveraged in the account creation process, and which correspond to columns in the CSV file. You will notice that there are two distinct parts to this script. First, the script will create the on-premises user account, but using the New-RemoteMailbox cmdlet–which will ensure that the routing address is correctly setup, and the on-premises Exchange understands that this will be an Office 365 mailbox, not an on-premises one. Second, the script will actually take a pre-existing reference or template account, and copy the security groups from that account, adding them all to the new user. Here is what the script looks like:

#The parameters for this script:

Param(
$TemplateUser,

$UserAlias,

$DisplayName,

$FirstName,

$LastName,

$OU,

$UPN,

$Password
)

#Create the new on-premises user with a remote mailbox
#NOTE: You can append the -Archive parameter for enabling an Archive mailbox also

New-RemoteMailbox -Alias $UserAlias -Name $DisplayName -FirstName $FirstName -LastName $LastName -OnPremisesOrganizationalUnit $OU -UserPrincipalName $UPN -Password $Password -ResetPasswordOnNextLogon:$false

#Get the user group membership from a template user

$UserGroups =@()
$UserGroups = (Get-ADUser -Identity $TemplateUser -Properties MemberOf).MemberOf

#Add the new user into the same groups as the template user

foreach ($Group in $UserGroups) {
    Add-ADGroupMember -Identity $Group -Members $UserAlias
}

#Display the group membership of the new user

Write-Host $UPN is now a member of the following groups:
(Get-ADUser -Identity $UserAlias -Properties MemberOf).MemberOf

#Note that after the account provisioning process is complete, it can take several minutes for mailbox and other services to become available to the user.

Name this first script New-HybridUser.ps1. Now, before we push this script through an Import-CSV pipe, I want to look at another script, which will actually assign the cloud licenses. Call this one, Assign-License.ps1:

#Notes about the parameters for this script:
#Use "TenantName:STANDARDPACK" for E1
#Use "TenantName:ENTERPRISEPACK" for E3
#Or Get-MsolAccountSKU to see other licenses available in the tenant
#ALSO: MAKE SURE ACCOUNTS HAVE SYNCED VIA AAD CONNECT FIRST!

Param(
$UPN,

$UsageLocation = "US",

$AccountSkuId
)


#Set the location for this user

Set-MsolUser -UserPrincipalName $UPN -UsageLocation $UsageLocation 

#Assign the license

Set-MsolUserLicense -UserPrincipalName $UPN -AddLicenses $AccountSkuId 

The reason we have two different scripts, is that we are going to tie these together in one “master” script. Why not just use one script to begin with? Because there are some things that only need to happen once, for example, running the synchronization of Azure AD Connect, and supplying credentials to connect to the MSOL service. This script can be called “Bulk-HybridUser.ps1”:

#On-premises section requirements: 
#Run this from your Hybrid Exchange Server 2013 or 2016 (requires Exchange management shell)
#Azure AD Connect should be present for the Start-ADSyncSyncCycle cmdlet to run

#Add the Exchange PS Snap-in
write-host "Loading the PowerShell Snap-in for Exchange Management"
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn

#Bulk-create hybrid users on-premises using CSV import
write-host "Creating user(s) from CSV file"
IMPORT-CSV NewHybridUsers.csv | FOREACH { ./New-HybridUser.PS1 -TemplateUser $_.TemplateUser -UserAlias $_.UserAlias -DisplayName $_.DisplayName -FirstName $_.FirstName -LastName $_.LastName -UPN $_.UPN -Password (ConvertTo-SecureString -String $_.Password -AsPlainText -Force) -OU $_.OU }

#Force a sync of Azure AD Connect so the new users show up in the MSOL tenant
#This command requires you to be on the server with Azure AD Connect installed
write-host "Running a delta sync from Azure AD Connect"
Start-ADSyncSyncCycle -PolicyType Delta

Start-Sleep -Seconds 60

#Cloud section requirements: 
#Install the Microsoft Online Service Assistant for IT Professionals 
#You must also have the Windows Azure Active Directory Module for PowerShell

#Get credentials for the MSOnline Service
write-host "Input your admin credentials for Office 365"
$MSOLCred = Get-Credential

#Connect to the MSOnline Service
write-host "Connecting to MSOnline"
Import-Module MSOnline
Connect-MsolService -Credential $MSOLCred

#Bulk-assign licenses in Office 365 using CSV import
write-host "Assigning user licenses and activating the mailboxes"
IMPORT-CSV NewHybridUsers.csv | FOREACH { ./Assign-License.PS1 -UPN $_.UPN -AccountSkuId $_.AccountSkuId }

All of these scripts should be stored in the same working directory, and PowerShell should be run as admin, with RemoteSigned set as as the ExecutionPolicy. Walking through this script:

1. We are adding the Exchange management console to this session (only needs to be done once)
2. We pipe the CSV through the user account creation process using FOREACH (the CSV can contain one user or many)
3. After the users are created, we force a delta sync, and give it some time to complete/take effect (only needs to be run once)
4. We are connecting to the MSOL service (Azure AD/Office 365) –you will be prompted for those credentials
5. Another FOREACH loop to assign the licenses to each user

And that is about all there is to it.

Note about assigning licenses. You can connect to the MSOL tenant and see what licenses are available using:

Get-MsolAccountSku

Usually they will be something like:

tenantname:ENTERPRISEPACK (e.g. contoso:ENTERPRISEPACK)

ENTERPRISEPACK is how they denote Office 365 E3 plan, whereas STANDARDPACK would be for E1. There are others, of course. And if you have a variety of licenses, you would specify the appropriate one in the CSV file corresponding with the record of each user. Here is an example of a CSV file, with just a single example user. Here we are modeling Paul Bunyan’s account on that of Charlie Brown (cbrown in the directory). Name your CSV NewHybridUsers.csv:

TemplateUser,UserAlias,DisplayName,Firstname,LastName,UPN,Password,OU,AccountSkuId
cbrown,PBunyan,Paul Bunyan,Paul,Bunyan,[email protected],somepasswordhere,contoso.local/Contoso Users/Information Technology,contoso:STANDARDPACK

Obviously in your environment you need to enter the variables that make the most sense–what OU to store the user in, and all of that. By the way, Happy Holidays to everyone out there.