Querying the Entra Connect Sync local SQL database

 

I'm working on a longer blog post, had this section written but had to alter my approach for the upcoming blog post. I thought this snippet was too good to never publish so here it is on its own.

A simple PowerShell for querying the local SQL database for Entra Connect Sync instead of using the ADSync cmdlets like Get-ADSyncMVObject, which can't query, or Get-ADSyncCSObject, which can query and page but is very slow.

This example exports a user's source anchor, DN and original sync date from the local Connect Sync database instance. The connection string syntax isn't easy to find.

$DB = Get-ItemPropertyValue -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server Local DB\Shared Instances\ADSync2022' -Name 'InstanceName'

$connStr = "Server=np:\\.\pipe\$DB\tsql\query;Database=ADSync;Integrated Security=True;"

$conn = New-Object System.Data.SqlClient.SqlConnection($connStr)

$conn.Open()

$cmd = $conn.CreateCommand()

$cmd.CommandText = "SELECT mv.sourceAnchor, mv.distinguishedName, ld.cloudSourceAnchor AS initialSyncDate FROM [ADSync].[dbo].[mms_metaverse] AS mv WITH (NOLOCK) INNER JOIN [ADSync].[dbo].[mms_metaverse_lineagedate] AS ld WITH (NOLOCK) ON ld.object_id = mv.object_id WHERE mv.cloudSourceAnchor is not null AND mv.sourceObjectType='User' and ld.cloudSourceAnchor > '2000-01-01'"

$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)

$table   = New-Object System.Data.DataTable

$adapter.Fill($table) | Out-Null

$table | Export-Csv -Path .\anchors.csv -NoTypeInformation

$conn.Close()

You should end up with a CSV containing data that looks like this:

"sourceAnchor","distinguishedName","initialSyncDate"

"9001001","CN=user1,OU=userids,DC=logon,DC=loderdom,DC=com","8/27/2026 3:53:13 PM"

"9001002","CN=user2,OU=userids,DC=logon,DC=loderdom,DC=com","8/27/2026 3:53:13 PM"

"9001003","CN=user3,OU=userids,DC=logon,DC=loderdom,DC=com","8/27/2026 3:53:13 PM"

"9001004","CN=user4,OU=userids,DC=logon,DC=loderdom,DC=com","8/31/2026 5:30:47 PM"