Import distribution groups to Office 365: Exchange Online with PowerShell

Today we faced an issue where a client needed to migrate their GroupWise distribution groups to Office 365. Since there is no easy way doing this we developed a PowerShell script to automate this proces. Well, actually it are two scripts.

The scripts are divided in one, creating the distribution groups and part two is adding the members to the newly created groups. First we have to gather the input for creating the distribution groups in Office 365. For this I only used the required attributes for creating a distribution group.

$import = Import-Csv -Path “C:\temp\Create-DG.csv” -Delimiter “;”
foreach ($item in $import) {
New-DistributionGroup -Name $item.Name -DisplayName $item.DisplayName -PrimarySmtpAddress $item.PrimairyEmailAddress -Type $item.Type
Export-Csv -Path “C:\temp\New-DistributionGroup_LogFile_$(get-date -Format ddMMyyyy).csv”
}

Once the distribution groups are created we can head on adding the members to them using the PowerShell script below.

$import = Import-Csv -Path “C:\temp\Add-DGMembers.csv” -Delimiter “;”
foreach ($item in $import) {
Add-DistributionGroupMember -Identity $item.GroupName -Member $item.UPN -Verbose
Out-File -FilePath “C:\temp\Add-DGMembers_LogFile_$(get-date -Format ddMMyyyy).log”
}

As we can see both the scripts import a .csv file containing the content. The .csv files and the PowerShell scripts can be downloaded below.

DOWNLOAD HERE

Please note that the distribution groups can be either created on-premises in Active Directory and then synced to Windows Azure Active Directory (Office 365) or either directly created in Office 365 with the help of remote PowerShell. Based on your infrastructure and migration scenario it can differ which is the best way to go. Functionally there will be no difference for Exchange Online. However in some hybrid scenarios it can be best to create the distribution groups on-premises and sync them to Office 36 with the help of Azure Active Directory Sync (DirSync).

Convert security groups to mail-enabled and universal for Office 365 with PowerShell

When carrying out projects for Enterprise clients I commonly face challenges with companies not meeting the system requirements for Office 365. One of the most commonly seen missing requirements are on the Identity and Access Management part of Office 365.

When migrating legacy Identity and Access Management infrastructures to Office 365 you quickly bump in to Microsoft’s Active Directory Services (ADS). To migrate this service to Windows Azure Active Directory – which is part of every Office 365 license – you can use the Windows Azure Active Directory Sync tool. Or as most IT Professionals know it “DirSync”, this is a special edition of FIM.

Now back to businness. To migrate legacy security groups to Windows Azure Active Directory, for products such as Exchange Online it is a requirement to have a GroupScope of Universal.(see image below)

Get-ADGroup-GroupScope

Since most companies still use Global security groups these need to be converted. Therefore I use a PowerShell script which automates this proces. For this script to work, import the ActiveDirectory module in PowerShell or run the script with Active Directory Module for Windows PowerShell.

Clear-Host

if((Get-Module | where {$_.Name -eq “ActiveDirectory”}) -eq $null){
Import-Module ActiveDirectory
}
$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
Set-Location $scriptPath
Write-Output “Output will be stored in ” (Get-Location)

$SeaBase = “DC=corp,DC=local”
$SeaVal = “CN=Mailbox_*”
$SeaScope = “Subtree”
$GrpList = “ADSecGrp.csv”
$UniGrpList = “Uni_ADSecGrp.csv”
$strLogFile = “ErrorLog.txt”
$DomainAdmin = Get-Credential

#Search for all Groups that are of type Security and scope is Global and starts with “Mailbox_”
$SecGrps = Get-ADGroup -SearchScope $SeaScope -SearchBase $SeaBase -Filter {GroupCategory -eq “Security” -and GroupScope -eq “Global”}

foreach ($secGrp in $SecGrps) {
try {
$DN = $secGrp | Where-Object {$_.DistinguishedName -like $SeaVal}
$DN | Export-Csv $GrpList -Append
} catch {
throw
Break
}
}

(Get-Content $GrpList | Select-Object -Skip 1) | Set-Content $GrpList

Write-Output “Check $GrpList to verify all exported security Groups are of type Global”
Write-Output “Press Y to continue”
$selection = read-host
if ($selection -eq “y” -or $selection -eq “Y”){
Write-Output “$GrpList CSV File Checked….”
foreach($G in Import-Csv $GrpList){
try {
$D = $G.DistinguishedName
Get-ADGroup -Identity $G.SID
Set-ADGroup -Identity $G.SID -GroupScope Universal -Credential $DomainAdmin
} catch {
$ErrorMessage = $_.Exception.Message
Write-Output “Error converting for $D ..`n Error Message : $ErrorMessage” | Add-Content $strLogFile
Throw
Break
}
$DN = Get-ADGroup -Identity $G.SID
$DN | Export-Csv $UniGrpList -Append
}
(Get-Content $UniGrpList | Select-Object -Skip 1) | Set-Content $UniGrpList
Write-Output “Check $UniGrpList to verify all modified security Groups are of type Universal”
}else{
Write-Output “Script Stopped by User” | Add-Content $strLogFile
Break
}

As you can see the script contains several variables. With these you can define the scope of OU’s or name convention for existing security groups. When running the PowerShell script it builds up a CSV-file called Uni_ADSecGrp.csv. When paused you can open and check the file to see if it contains the groups which you wish to convert. If so, you can insert “Y” to the script and it proceeds running.

After we have succesfully changed the GroupScopes to Universal we can carry on and use the second PowerShell script which mail-enables the security groups so they meet the requirements for Exchange Online. Besides the conversion to mail-enabled it also hides the groups from the Global Address List.

Run this script on one of the legacy Exchange servers with the use of the  Exchange Management Shell.

Clear-Host

if((Get-Module | where {$_.Name -eq “ActiveDirectory”}) -eq $null){
 Import-Module ActiveDirectory
}

Add-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010
 $env:ExchangeInstallPath\bin\RemoteExchange.ps1
Connect-ExchangeServer -auto

Write-Output “Output will be stored in ” (Get-Location)

$GrpList = “Final_ADSecGrp.csv”
$strLogFile = “enableErrorLog.txt”
$log = “AfterLog.txt”
$ErrorLog = “ErrorLog.txt”

foreach($G in Import-Csv “Uni_ADSecGrp.csv”){

try {
Get-ADGroup -Identity $G.SID

Enable-DistributionGroup -Identity $G.DistinguishedName -Alias $G.Name
Set-DistributionGroup -Identity $G.DistinguishedName -HiddenFromAddressListsEnabled $true
Get-DistributionGroup -Identity $G.DistinguishedName | Add-Content $Log
$x = Get-DistributionGroup -Identity $G.DistinguishedName
if($x -ne $Null){
Write-Output $G.DistinguishedName
}else{
Write-Output $G.DistinguishedName | Add-Content $ErrorLog
}
} catch {
$ErrorMessage = $_.Exception.Message
Write-Output “Error Enabl-DistributionGroup for $G.DistinguishedName …..`nError Message : $ErrorMessage” | Add-Content $strLogFile
throw
Break
}

}

Once you have succesfully executed the second script you can add these objects to your Windows Azure Directory Sync cycle. Please be aware that when you convert the groups, the groups may not contain unsupported characters such as namespaces or & characters.

Best of luck to you all with carrying out succesfull Office 365 deployments. Before I publish the post, I have thank to my colleague and teammate Dev Chaudhari for working on the scripting!

SharePoint Online performance issue caused by deleted term store admin user objects

After working with Microsoft for over a month on a daily basis we found the cause of a serious performance issue within SharePoint Online. This issue affected several enterprise customers of ours.

This issue only occurs on sites which use the “managed navigation” functionality. In SharePoint, you can choose between two navigation methods: structural navigation or managed navigation. Structural navigation is based on site structure. Managed navigation is based on term sets. For further information read this article.

Rootcause symptoms which identify the cause:
When clients initiate a webrequest to the SharePoint Online site it takes 5 to 30 seconds untill the server returns data to the GET method. See this screenshot from Internet Exporer “Profiler”. 

IE_Profiler

Rootcause analysis:
After intensively troubleshooting several tenants we noticed strange behaviour the way the system communicates with the Managed Metadata Service Application. Since the sites are using managed navigation the content will be represented using a managed metadata term set. Because of this configuration EVERY FIRST WEBREQUEST is relies on your defined term sets and its configuration.

Solution:
Allright. Now that we have some understanding how the system relies on the Managed Metadata Service Application when using a managed navigation let’s proceed to implementing the solution.

The reason why the system “waits” for over 15 seconds on this specific part of SharePoint is caused by corrupt or missing user permissions on the database of the Service Application. As we explained “Managed navigation is using term store for its items, every time a request made to the term store, it will try to look for these users which takes some time until the process gives up.  Therefore causes the delay.”

1. Check if there are “present” Term Store Administrators defined in the Office365 portal by going to the SharePoint Admin center.
2. Select “term store” on the left hand side menu.
3. Check if you see any current users defined or SID’s i.e. s-1-5-21-2522053391-2242016340-1337919630-744690. See the image listed below.

Term_store_permissions
4. If you have identified these unknown SID’s on your environment OR identified other non system service-accounts please feel free to remove the permissions to instantly improve performance. 

Now.. if you are using AD FS and DirSync for providing single sign-on access to Office365 services then stick with me. If for instance while developing and building SharePoint solutions several users (developers or admins) have been granted the “Term Set Administrator” permission. At some point the project finishes and their account will be disabled or deleted at the on-premises Active Directory Services. Standard behaviour is that this deletion synchronizes to the cloud with DirSync. This process succesfully deletes the userobject on all front-end systems of Office365 but NOT correctly on all back-end systems in regarding to the Managed Metadata Service Application. In our case the user’s SID was still present on the Managed Metadata SQL database and therefore first webrequests got stuck on this explicit permission.

The only solution for this is contacting Microsoft Support and request them to delete the SID’s and/or old user objects on the database. Once deleted you will instantly notice great performance improvement on your environment!

Set Calendar Reviewer rights to all users in Office365

Most companies have policies which permit all users to share their calendars with everyone. There are several PowerShell scripts available online to configure this. However the tricky thing is most enterprises have different Outlook client languages. Because the Outlook client language determines the mailboxfolder attribute to it’s unique language. In this case it is set to the Dutch version “kalender” and the Engilsh version “calendar”.

  1. The fist thing which needs to be done is creating a mail-enabled universal securitygroup on-premises which contains all user objects. Make sure it syncs to Office365 with Windows Azure DirSync.
  2. Once the objects have been succesfully synchronized to the cloud run the script below to determine which language the calendar mailbox folder is:

    $GroupMembers = Get-DistributionGroupMember SG_CalendarReviewer
    $GroupMembers | foreach {
    try
    {
    $isNL = ((Get-MailboxRegionalConfiguration -Identity $_.PrimarySmtpAddress | where {$_.Language -eq “nl-NL”} | Measure).Count -eq 1)
    if ($isNL)
    {
    $identity = $_.PrimarySmtpAddress + “:\Kalender”
    }
    else
    {
    $identity = $_.PrimarySmtpAddress + “:\Calendar”
    }$permission = Get-MailboxFolderPermission -Identity $identity -ErrorAction SilentlyContinue | where {$_.User.toString() -eq “SG_CalendarReviewer”}
    if ($permission)
    {
    write-host “$identity has permission”
    }
    else
    {
    write-warning “$identity doest not have permission”
    } }
    catch
    {

    }
    }

  3. Once you have determined which language the mailboxfolder is for the calendar run this PowerShell script to grant all users in the created securitygroup with the ” Reviewer” on every mailbox belonging to each user object in the securitygroup. The PowerShell script listed below grants the access-right to all English calendars:

    $GroupMembers = Get-DistributionGroupMember SG_CalendarReviewer
    $GroupMembers | foreach {
    $identity = $_.PrimarySmtpAddress + “:\calendar”
    $permissionExists = ((Get-MailboxFolderPermission -Identity john.doe@domain.com:\calendar | where {$_.User.toString() -eq “SG_CalendarReviewer”} | measure).Count -eq 0)
    #Add-MailboxFolderPermission -Identity -User SG_CalendarReviewer -AccessRights Reviewer
    }

  4. Now change the mailboxfolderpermission attribute to the appropiate language and run the last script again.

Bypass Spam Filter for Exchange Online in Office365

A common issue several businnesses face is spam. However, sometime companies want to whitelist or either bypass specific domains or users for their e-mail. In this case the request was to accept e-mail from a specific domain for marketing purposes.

The way to configure this in Office365 without having to add an accepted domain is by configuring a transport rule. This can be done by following the below steps:

  1. Login to https://portal.microsoftonline.com.
  2. Select Admin -> Exchange in the top right corner.
  3. Select Mail Flow in the menu on the left.
  4. Click on “Add Rule for Mail Flow” and choose for “Bypass Spam Filtering”.
  5. Select on the “Apply this rule if”  for  “The sender is” “Domain is”
  6. Specify Domain. Add your list of whitelisted Domain.
  7. Select on “Stopm Processsing more rules”
  8. Click Save to save the configuration.

Nintex Workflow 2010 issue: Error establishing database connection. SQL Error: 515 VALUE NULL

Today I came across an issue at one or our clients where Nintex Workflow 2010 was up/downgraded. After starting a workflow it threw an correlation ID. After looking up this error in ULS Logging I could find the error message described below:

Error establishing database connection. SQL Error: 515.: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column ‘DatabaseID’, table ‘DEV_Nintex_Workflow4.3.3.dbo.Storage’; column does not allow nulls. INSERT fails.  The statement has been terminated.  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   

 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()    at System.Data.SqlClient.SqlDataReader.get_MetaData()    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)    at Nintex.Workflow.Administration.Database.ExecuteReader(SqlCommand command, CommandBehavior behavior) (Build:2330)

The resolution for this issue is related to either a missing Config or Content Database for Nintex. If you look within Central Admin, you actually have to specify the database to use TWICE. This is due to the fact that Nintex has a “Config” and “Content” database – mostly (usually) within the same SQL database.

NintexIssue

First post!

Welcome at my blog. On this site you will find technical articles and solutions about various Microsoft products such as:

  • Office 365
  • Exchange Online
  • SharePoint
  • Microsoft SQL Server
  • ADFS and DirSync

Please stay tuned for new blogs to be released very soon!

Blog at WordPress.com.

Up ↑