Today I will continue my series of blog posts on Azure Active Directory Connect. In today’s entry I will cover the database backend that powers the Azure Active Directory Sync portion of the AADC solution.
AADC supports both a full-fledged version of SQL running on a dedicated server or a localized database that is installed automatically during the initial installation. The decision as to which to use depends on the number of objects in the on-premises Active Directory environment that AADC will synchronize from. I will focus in on the localized database.
My first assumption was that Microsoft would use a Windows Internal Database (WID) solution for the backend, as they do with server roles like Active Directory Federation Services (AD FS) and Active Directory Rights Management Services (AD RMS). Instead Microsoft has chosen to use a version of Microsoft SQL server called Microsoft SQL Express LocalDB. You can think of LocalDB as the middle ground between MS SQL Compact and MS SQL Express in terms of functionality and use case.
So we know the version of SQL, but how do we connect to it? Is it a named instance?
Try as I may I could not find an Azure AD Connect PowerShell cmdlet that provided me with the connection string information. My next stop was the registry. As luck may have it, I found the information in HKLMSYSTEMCurrentControlSetServicesADSyncParameters as seen in the screenshot below.
The registry keys we are interested in are the Server, SQLInstance, and DBName. These entries indicate that the instance is running on the local server, the instance name is ADSync, and the database is named ADSync. This provides enough information to connect to the database using SQL Management Studio. We’ll use this string (localdb).ADSync to connect to the SQL instance.. This took me a while to put together because the (localdb) portion is specific to SQL Express LocalDB.
After we connect to the instance we can further explore the databases (if we are so inclined). Many of the databases are self-explanatory and don’t require further discussion. I did find it interesting as to the structure of the data for the synchronization rules in the synchronization_rule field within the dbo.mms_synchronization_rule table. I’m also curious as to the how the passwords for the management agents are encrypted prior to being base-64 encoded in the encrypted_configuration field in the dbo.mms_managmenent_agent table.
In the next post I’ll take a look at the API the Azure AD Connector uses to synchronize information to Azure Active Directory.