In 2013, Exchange Server MVP Mike Crowley wrote a script which would interactively report on the Office 365 Directory Synchronization tool. In 2014, Mike and I worked to update the script so that an HTML report would be generated. This would allow you to schedule the script and have the output emailed to you without the need to run the script interactively.
Before you can actually run the script, you will have to install SQL PowerShell on the AADSync machine first. DirSync had this installed by default, but it seems that AADSync does not. To install the SQL PS module, you must install the following components separately:
- Microsoft® System CLR Types for Microsoft® SQL Server® 2012
- Microsoft® SQL Server® 2012 Shared Management Objects
- *Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012
The binaries can be installed from the installation instructions on the following page: http://www.microsoft.com/en-us/download/details.aspx?id=29065
Once you have installed the components, run the following command from the AADSync server and verify that the SQLPS module is listed:
Get-Module -ListAvailable
Once you have verified the SQLPS module is installed and available, you can run the script.
This time around I have decided to publish the script through Github. You can download it from HERE. Alternatively, the script also available from the Technet Script Gallery, HERE.
Please use the script for what it’s worth, and always test in a lab first. Comments/feedback and feature requests are always welcome!
It appears with the newest version of AADSync generates a much larger result when running the SQL query (line 88). This results in an error in line 126 where the resultant query is cast to XML. The default length returned for the invoke-sqlcmd is 4000 characters. You can see in the error that the XML file is not complete and the error states that a tag is not closed in line 4001.
using -MaxCharLength in line 88 allows for the output to not be truncated and the script runs as expected.
Thanks! I will take a look at that shortly. It might be something environmental too. I’ve had it run against a bunch of newer implementations and I’ve not run into the issue (yet). But it’s definitely worth adding! Thanks again 🙂
Any chance you could update this for Azure AD Connect v 1.1.281.0?
Hi Ben,
Thanks for letting me know. I’m now in the process of doing so 🙂
It should be online in the next few days –including some extra features/better error handling!
Cheers,
Michael
Ben, script has now been updated and is available for download!
Fantastic! I’ll give it a whirl! Thank you
Guess I will be waiting a bit longer. Installed our Azure AD Connect to use an external SQL DB. 😀