Azure Active Directory Sync (DirSync) seems so simple on the surface doesn’t it? “Next, Next, Finish”, right? Ha! If you’ve ever had to revisit your DirSync server to troubleshoot or make a configuration change, you know there can be more than meets the eye. A lot of useful information happens to be scattered across various registry keys, SQL tables and XML files. If you’re not familiar with the FIM Management Console, and these other locations it might be hard to see what’s going on.
Here’s a free script that aims to help by creating a dashboard highlighting useful DirSync configurations. See the image below for a sample output. Before you run it you should be aware of the limitations listed in the “known issues” area of the script.
Oct 2014 Update: Fellow MVP, Michael Van Horenbeeck has written an update to this script for use with the new Azure AD Sync Tool. Please be sure to check it out here: http://vanhybrid.com/2014/10/26/azure-ad-sync-tool-html-report/
You can Review the script below or download it and try it for yourself!
<# Description: This script gathers DirSync information from various locations and reports to the screen. November 5 2013 Mike Crowley http://mikecrowley.us Known Issues: 1) All commands, including SQL queries run as the local user. This may cause issues on locked-down SQL deployments. 2) For remote SQL installations, the SQL PowerShell module must be installed on the dirsync server. (http://technet.microsoft.com/en-us/library/hh231683.aspx) 3) The Azure Service account field is actually just the last account to use the Sign In Assistant. There are multiple entries at that registry location. We're just taking the last one. 4) Assumes Dirsync version 6385.0012 or later. #> #Console Prep cls Write-Host "Please wait..." -F Yellow ipmo SQLps #Check for SQL Module if ((gmo sqlps) -eq $null) { write-host "The SQL PowerShell Module Is Not loaded. Please install and try again" -F Red write-host "http://technet.microsoft.com/en-us/library/hh231683.aspx" -F Red Write-Host "Quitting..." -F Red; sleep 5; Break } #Get Dirsync Registry Info $DirsyncVersion = (gp 'hklm:SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\Microsoft Online Directory Sync').DisplayVersion $DirsyncPath = (gp 'hklm:SOFTWARE\Microsoft\MSOLCoExistence').InstallPath $FullSyncNeededBit = (gp 'hklm:SOFTWARE\Microsoft\MSOLCoExistence').FullSyncNeeded $FullSyncNeeded = "No" If ((gp 'hklm:SOFTWARE\Microsoft\MSOLCoExistence').FullSyncNeeded -eq '1') {$FullSyncNeeded = "Yes"} #Get SQL Info $SQLServer = (gp 'HKLM:SYSTEM\CurrentControlSet\services\FIMSynchronizationService\Parameters').Server if ($SQLServer.Length -eq '0') {$SQLServer = $env:computername} $SQLInstance = (gp 'HKLM:SYSTEM\CurrentControlSet\services\FIMSynchronizationService\Parameters').SQLInstance $MSOLInstance = ($SQLServer + "\" + $SQLInstance) $SQLVersion = Invoke-Sqlcmd -ServerInstance $MSOLInstance -Query "SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')" #Get Password Sync Status [xml]$ADMAxml = Invoke-Sqlcmd -ServerInstance $MSOLInstance -Query "SELECT [ma_id] ,[ma_name] ,[private_configuration_xml] FROM [FIMSynchronizationService].[dbo].[mms_management_agent]" | ? {$_.ma_name -eq 'Active Directory Connector'} | select -Expand private_configuration_xml $PasswordSyncBit = (Select-Xml -XML $ADMAxml -XPath "/adma-configuration/password-hash-sync-config/enabled" | select -expand node).'#text' $PasswordSyncStatus = "Disabled" If ($PasswordSyncBit -eq '1') {$PasswordSyncStatus = "Enabled"} #Get Account Info $ServiceAccountGuess = (((gci 'hkcu:Software\Microsoft\MSOIdentityCRL\UserExtendedProperties' | select PSChildName)[-1]).PSChildName -split ':')[-1] $ADServiceAccountUser = $ADMAxml.'adma-configuration'.'forest-login-user' $ADServiceAccountDomain = $ADMAxml.'adma-configuration'.'forest-login-domain' $ADServiceAccount = $ADServiceAccountDomain + "\" + $ADServiceAccountUser #Get DirSync Database Info $SQLDirSyncInfo = Invoke-Sqlcmd -ServerInstance $MSOLInstance -Query "SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files WHERE DB_NAME(database_id) = 'FIMSynchronizationService'" $DirSyncDB = $SQLDirSyncInfo | ? {$_.Logical_Name -eq 'FIMSynchronizationService'} $DirSyncLog = $SQLDirSyncInfo | ? {$_.Logical_Name -eq 'FIMSynchronizationService_log'} #Get connector space info (optional) $ADMA = Invoke-Sqlcmd -ServerInstance $MSOLInstance -Query "SELECT [ma_id] ,[ma_name] FROM [FIMSynchronizationService].[dbo].[mms_management_agent] WHERE ma_name = 'Active Directory Connector'" $AzureMA = Invoke-Sqlcmd -ServerInstance $MSOLInstance -Query "SELECT [ma_id] ,[ma_name] FROM [FIMSynchronizationService].[dbo].[mms_management_agent] WHERE ma_name = 'Windows Azure Active Directory Connector'" $UsersFromBothMAs = Invoke-Sqlcmd -ServerInstance $MSOLInstance -Query "SELECT [ma_id] ,[rdn] FROM [FIMSynchronizationService].[dbo].[mms_connectorspace] WHERE object_type = 'user'" $AzureUsers = $UsersFromBothMAs | ? {$_.ma_id -eq $AzureMA.ma_id} $ADUsers = $UsersFromBothMAs | ? {$_.ma_id -eq $ADMA.ma_id} #Get DirSync Run History $SyncHistory = Invoke-Sqlcmd -ServerInstance $MSOLInstance -Query "SELECT [step_result] ,[end_date] ,[stage_no_change] ,[stage_add] ,[stage_update] ,[stage_rename] ,[stage_delete] ,[stage_deleteadd] ,[stage_failure] FROM [FIMSynchronizationService].[dbo].[mms_step_history]" | sort end_date -Descending #GetDirSync interval (3 hours is default) $SyncTimeInterval = (Select-Xml -Path ($DirsyncPath + "Microsoft.Online.DirSync.Scheduler.exe.config") -XPath "configuration/appSettings/add" | select -expand Node).value #Generate Output cls Write-Host "Report Info" -F DarkGray Write-Host "Date: " -F Cyan -NoNewline ; Write-Host (Get-Date) -F DarkCyan Write-Host "Server: " -F Cyan -NoNewline ; Write-Host $env:computername -F DarkCyan Write-Host Write-Host "Account Info" -F DarkGray Write-Host "Active Directory Service Account: " -F Cyan -NoNewline ; Write-Host $ADServiceAccount -F DarkCyan Write-Host "Azure Service Account Guess: " -F Cyan -NoNewline ; Write-Host $ServiceAccountGuess -F DarkCyan Write-Host Write-Host "DirSync Info" -F DarkGray Write-Host "Version: " -F Cyan -NoNewline ; Write-Host $DirsyncVersion -F DarkCyan Write-Host "Path: " -F Cyan -NoNewline ; Write-Host $DirsyncPath -F DarkCyan Write-Host "Password Sync Status: " -F Cyan -NoNewline ; Write-Host $PasswordSyncStatus -F DarkCyan Write-Host "Sync Interval (H:M:S): " -F Cyan -NoNewline ; Write-Host $SyncTimeInterval -F DarkCyan Write-Host "Full Sync Needed? " -F Cyan -NoNewline ; Write-Host $FullSyncNeeded -F DarkCyan Write-Host Write-Host "User Info" -F DarkGray Write-Host "Users in AD connector space: " -F Cyan -NoNewline ; Write-Host $ADUsers.count -F DarkCyan Write-Host "Users in Azure connector space: " -F Cyan -NoNewline ; Write-Host $AzureUsers.count -F DarkCyan Write-Host "Total Users: " -F Cyan -NoNewline ; Write-Host $UsersFromBothMAs.count -F DarkCyan Write-Host Write-Host "SQL Info " -F DarkGray Write-Host "Version: " -F Cyan -NoNewline ; Write-host $SQLVersion.Column1 $SQLVersion.Column2 $SQLVersion.Column3 -F DarkCyan Write-Host "Instance: " -F Cyan -NoNewline ; Write-Host $MSOLInstance -F DarkCyan Write-Host "Database Location: " -F Cyan -NoNewline ; Write-Host $DirSyncDB.Physical_Name -F DarkCyan Write-Host "Database Size: " -F Cyan -NoNewline ; Write-Host $DirSyncDB.SizeMB "MB" -F DarkCyan Write-Host "Database Log Size: " -F Cyan -NoNewline ; Write-Host $DirSyncLog.SizeMB "MB" -F DarkCyan Write-Host Write-Host "Most Recent Sync Activity" -F DarkGray Write-Host "(For more detail, launch:" $DirsyncPath`SYNCBUS\Synchronization Service\UIShell\miisclient.exe")" -F DarkGray Write-Host " " ($SyncHistory[0].end_date).ToLocalTime() -F DarkCyan -NoNewline ; Write-Host " --" $SyncHistory[0].step_result -F Gray Write-Host " " ($SyncHistory[1].end_date).ToLocalTime() -F DarkCyan -NoNewline ; Write-Host " --" $SyncHistory[1].step_result -F Gray Write-Host " " ($SyncHistory[2].end_date).ToLocalTime() -F DarkCyan -NoNewline ; Write-Host " --" $SyncHistory[2].step_result -F Gray Write-Host
If you like this post, you may like my others on DirSync: https://mikecrowley.wordpress.com/tag/dirsync/
Awesome, very handy indeed – thanks for sharing
Hello, Mike: This dashboard looks like something I am very interested in. Unfortunately I am getting mostly gibberish when I run it. I am running DirSync 1.0.6635.43 on Server 2012 r2.
Under DirSync Info I am getting a Disabled Password Sync Status.
Under Recent Sync Activity, powershell is reporting “Cannot index into a null array”
The MIISClient.exe is showing all kinds of activty and things appear to be working well. But your dashboard would be a great double-check.
ensure you have the sql module loaded, execution policy is set to unrestricted, UAC isn’t a factor and that you have at least 3 previous run history events.
In case it’s helpful, it’s possible to install the SQL PowerShell module without installing SQL Server. I have the details here:
http://blog.smu.edu/wis/2012/11/26/sql-server-powershell-module-sqlps/
If you have Dirsync v6985 or later, you can now get the password hash status by running:
Get-PasswordHashSyncConfiguration -SourceConnector ‘Active Directory Connector’
If DirSync 1.0.6985.0000 and newer requires .NET Framework 4.5.1, when I run the script from a Windows 2012 server, will .NET Framework 3.5 be a requirement as well to run your script?
I don’t think so. We’re just talking to SQL and the registry.
Is this script working for Azure AD Connect as well?
Not really. I am going to release an update at some point in the future, but you should be aware that much of what I had to go dig for in SQL, the registry, etc is now available by the AAD Connect cmdlets. See this post for detail on that, or let me know if you’re looking for something else.