Manage LocalDB AAD Connect SQL Database

Hello,

When you install AADConnect, you have a choice to make for the database. You can install your own SQL server and manage it, or you can can with a free LocalDB installed and managed by AADConnect.

Manage LocalDB AAD Connect SQL Database – AADConnect Choice

Identify Instance Name

If you chose the LocalDB, sometimes, you may want to have a look to the database itself. You can use SQL Server Management Studio to connect, you just need to know the instance name. You can find the instance name with SqlLocalDB.exe that you can find in ‘C:Program FilesMicrosoft SQL Server110ToolsBinn’.

Set-Location -Path 'C:\Program Files\Microsoft SQL Server\110\Tools\Binn'
SqlLocalDB.exe info
SqlLocalDB.exe info .\ADSync

Manage LocalDB AAD Connect SQL Database – Instance Name

Install SSMS and Connect to the Instance

Once you get the instance name, you need to install SSMS, you can download it from here.

Manage LocalDB AAD Connect SQL Database – SSMS Install

Then use the instance name you got from SqlLocalDB.exe and Windows Authentication:

Manage LocalDB AAD Connect SQL Database – SSMS Connection

Manage LocalDB AAD Connect SQL Database

Now that you are connected, you can reorganize/rebuild index, backup, shrink, etc…

Manage LocalDB AAD Connect SQL Database -Rebuild Indexes

Manage LocalDB AAD Connect SQL Database – Database Tasks

If you have some performances issues with your AADConnect, this can be handy. You can also the SQL Server Management Studio to learn more about how AADConnect metaverse works:

Manage LocalDB AAD Connect SQL Database – Metaverse Table

There is 140 row in this table, you can recognize a few of them, some of them come from AD, some other from Azure AD:

Manage LocalDB AAD Connect SQL Database – Metaverse Rows

Thanks to SSMS you can explore how AADConnect works without having to install and license a dedicated SQL instance.

You can also see the last modification, or the last error of each object from there:

SELECT TOP (1000) [last_import_modification_date]
      ,[last_export_modification_date]
      ,[password_sync_target]
      ,[is_password_hash_sync_retry]
      ,[initial_import_error_date]
      ,[last_import_error_date]
      ,[import_error_code]
      ,[count_export_error_retries]
      ,[initial_export_error_date]
      ,[last_export_error_date]
      ,[export_error_code]
      ,[import_error_detail]
      ,[export_error_detail]
      ,[password_change_history]
      ,[password_incoming_timestamp]
      ,[password_outgoing_timestamp]
  FROM [ADSync].[dbo].[mms_connectorspace]

Manage LocalDB AAD Connect SQL Database – Recent Import Export

Note: Manual modification of any records inside this database instance is highly not recommended.

Security Note: If you password sync in your AADConnect, your database may contain some information about the hashing of those, so you may want to look closely at who can access it.

Manage LocalDB AAD Connect SQL Database – Passwords Hash Information