Most Microsoft-based Hybrid Identity implementations use Active Directory Federation Services (AD FS) Servers, Web Application Proxies and Azure AD Connect installations. In this series, labeled Hardening Hybrid Identity, we’re looking at hardening these implementations, using recommended practices.
In this part of the series, we’ll look at the benefits of implementing Azure AD Connect with a back-end SQL Server (cluster) as opposed to implementing it with the accompanying SQL Server Express installation.
Why deploy Azure AD Connect with a full-fledged Microsoft SQL Server?
There are two main reasons to deploy Azure AD Connect with a Microsoft SQL Server back-end:
SQL Server Information Security Measures
The organizations for which I’ve deployed Azure AD Connect with SQL Server chose to do so mainly because they have a strategy to centralize their Microsoft SQL databases on a highly-available Microsoft SQL cluster. This way, all the information security measures surrounding that data had to be applied only once. The same rules apply to Active Directory Federation Services (AD FS) implementations.
No database limit
By default, Azure AD Connect installs Microsoft SQL Server Express Edition. Databases for this free edition of Microsoft SQL Server are limited to 10 GB. This limit makes another case for a full-fledged Microsoft SQL Server, as it opposes a limit of 100,000 objects in scope for Azure AD Connect in the real world.
Drawbacks of deploying Azure AD Connect with a full-fledged Microsoft SQL Server
There are also two main drawbacks to deploying Azure AD Connect with a Microsoft SQL Server back-end (next to the increased management load of the SQL Server (cluster)):
No Automatic virtual Service Account (vSA)
In a default setup, the Azure AD Connect service runs as a virtual service account. To allow the service to connect to the SQL back-end however, a service account needs to be used.
To counter this drawback, we’ll deploy Azure AD Connect with a gMSA.
This adds a couple of requirements, like domain membership for the Windows Server installation running Azure AD Connect.
No Automatic Upgrades
However, there is also a big drawback when deploying Azure AD Connect with a Microsoft SQL Server back-end: the Automatic Upgrades feature is not supported. When you deploy Azure AD Connect with SQL Server, you’ll need to manually upgrade the Azure AD Connect installation and keep up with its support statements.
Getting ready
To implement Azure AD Connect with a full-fledged Microsoft SQL Server, you’ll need to meet the following requirements:
Software requirements
Download the latest version of Azure AD Connect. After downloading, place it on the hard disk of the Windows Server installation intended to run Azure AD Connect.
Requirement for gMSAs
To use group Managed Service Accounts (gMSAs), the Active Directory environment needs to adhere to the following requirements:
- The Active Directory schema needs to run Windows Server 2012, or up.
- At least one Domain Controller needs to run Windows Server 2012, or up.
- The Active Directory domain needs to run the Windows Server 2008 R2 Domain Functional Level (DFL) to support automatic password and SPN management.
Note:
When using multiple Azure AD Connect installations (of which only one is not configured as Staging Mode server), you can use the same gMSA for all installations without problems.
Database requirements
In the organization, make sure there is consensus on the name for the Azure AD Connect database.
Note:
Azure AD Connect version 1.3.20.0 introduced the concept of naming databases. Before, all Azure AD Connect databases needed to be named ADSync. This caused issues with Staging Mode servers. If your organization is about to deploy multiple Azure AD Connect installations using SQL Server, name the databases properly, so it is traceable to a specific Azure AD Connect installation.
Important:
Azure AD Connect installations should never use the same SQL Server database. Deploy a separate database per Azure AD Connect installation.
System requirements for SQL Servers
For AD FS with SQL Server-based databases, have a SQL Server available on the network, that is also resolvable via DNS and reachable by the proposed AD FS server(s).
Make sure the Microsoft SQL Server is configured with a TLS certificate to be able to encrypt the data with the AD FS Servers. Also make sure the Microsoft SQL Server installation supports TLS 1.2 by installing the required hotfixes as described in KB3135244.
Requirements for SQL Server Always On Availability
When using a Microsoft SQL Server Always On Availability (AOA) group as the back-end SQL Server, the database needs to be created and can be made part of the AOA group after installing Azure AD Connect.
During installation, Azure AD Connect detects whether the SQL instance provided is enabled for AOA or not. When AOA is enabled, Azure AD Connect further figures out if SQL AOA is configured to use synchronous replication or asynchronous replication.
Note:
When setting up the Availability Group Listener, it is recommended that you set the RegisterAllProvidersIP property to 0.
Privilege requirements
Per step, you’ll need the following specific permissions:
For the account that will be used to create the group Managed Service Account (gMSA):
- Domain Administrator privileges in Active Directory.
For the account that will be used to create the database for Azure AD Connect on the SQL Server (cluster):
- SysAdmin (sa) privileges on the Microsoft SQL Server.
For the account that will be used to install the group Managed Service Account (gMSA) on the Windows Server intended to run Azure AD Connect:
- Local Administrator privileges on the Windows Server installations.
- Domain user privileges in Active Directory.
For the account that will be used to run the Azure AD Connect wizard:
- Enterprise Administrator privileges in Active Directory, through a direct membership of the Enterprise Admins group (when opting to create the Azure AD Connect service account to communicate with Active Directory automatically, otherwise, Domain Administrator privileges are sufficient).
- Local Administrator privileges on the Windows Server installations you intend to run Azure AD Connect on. (This is a default permission that comes with the above admin privileges when the server is domain-joined)
- db_owner privileges on the Microsoft SQL Server.
Note:
After the initial configuration of Azure AD Connect you can remove the permissions above, but they may have to be re-added for consecutive runs to reconfigure Azure AD Connect. These latter situations may be under time pressure.
For the account that will be entered in the Azure AD Connect wizard to connect to Azure AD:
- Global Administrator privileges in the Azure AD tenant.
How to install Azure AD Connect with SQL Server
Deploying Azure AD Connect with a full-fledged Microsoft SQL Server consists of the following steps:
- Creating the gMSA
- Creating the database and configuring Always-on Availability
- Installing and configuring Azure AD Connect
Creating the gMSA
We need a group Managed Service Account (gMSA) to be used as the Azure AD Connect service account. A gMSA is the safest way to host this account from Active Directory.
When using SQL Server, the service account becomes really important, because it is not only used to run the Azure AD Connect service, but also to connect to the SQL Server backend.
Use the following lines of PowerShell on a system with the Active Directory Module for Windows PowerShell installed, while signed in with a user account that is a member of the Domain Admins group, supposing AADC01 is the hostname of the server intended to run Azure AD Connect:
Import-Module ActiveDirectory
New-ADServiceAccount AADC1gMSA -DNSHostName AADC1gMSA.domain.tld -PrincipalsAllowedToRetrieveManagedPassword "CN=AADC01,CN=Computers,DC=domain,DC=tld"
Creating the database and configuring Always-on Availability
On the SQL Server, perform the following steps:
- Start Microsoft SQL Server Management Studio.
- Connect to your server in the Connect to Server dialog screen.
- In the left navigation pane, right-click on Databases and
select New Database…. - In the New Database dialog screen, enter the name for the
database.
- Click OK to create the
database. - In the left navigation pane, expand Security.
- Right-click the logins node and select New login….
The Login – New dialog screen opens on the General page. - Specify AADC1gmsa$ as the Login name:. and make sure Windows authentication is selected as the login method.
- In the left navigation pane, click on User Mapping.
- On the User Mapping page, select the Azure AD Connect database you created in steps 3 through 5 from the list of databases in the Users mapped to this login:.
- In the Database role membership for: ADSyncAADC01 select db_owner.
- Click OK to create the login and set the database permissions.
- Close Microsoft SQL Server Management Studio.
If you want to add the database to an Always-On Availability group, install and configure Azure AD Connect before doing so.
Installing and configuring Azure AD Connect
First, we need to install the Active Directory module for Windows PowerShell, as a cmdlet we need is part of this module:
Install-WindowsFeature RSAT-AD-PowerShell
Now, we can run the following lines of Windows PowerShell on the Azure AD Connect server to install the group Managed Service Account (gMSA) in an elevated PowerShell window:
Import-Module ActiveDirectory
Install-ADServiceAccount -Identity AADC1gMSA
Then, we can uninstall the Active Directory Domain Services role again:
Uninstall-WindowsFeature RSAT-AD-PowerShell
With the gMSA ready to go, we start the installation of Azure AD Connect, by double-clicking the Azure AD Connect installer (AzureADConnect.msi):
- On the Welcome to Azure AD Connect page, select the I agree to the license terms and privacy notice option. Click Continue afterward.
- On the Express Settings page, click Customize.
- On the Install required components page, make the following changes:
- Select the User an existing SQL Server option.
- Enter the hostname of the Microsoft SQL Server (listener) in the SERVER NAME field, or click the Browse button.
- Leave the INSTANCE NAME field blank to use the default SQL Server instance.
- Enter the database name for Azure AD Connect you created earlier in the DATABASE NAME field.
- Select the Use an existing service account option.
- Select the Managed Service Account option.
- Type the name of the group Managed Service Account (gMSA) you created earlier in the SERVICE ACCOUNT NAME field. End its name with $ to indicate it is a gMSA.
- Select the User an existing SQL Server option.
- Click Install.
From the User sign-in page of Azure AD Connect onward, perform the same steps as you would normally perform to configure Azure AD Connect.
How to retrieve the SQL Server (listener) Azure AD Connect uses
When an organization uses Azure AD Connect with a Microsoft SQL Server back-end, it is useful to find the database server used.
Perform the following steps to find the Microsoft SQL Server that holds the database for a specific Azure AD Connect installation through the Microsoft Azure Active Directory Connect wizard:
- Sign in interactively to the Windows Server running Azure AD Connect.
- Open Azure AD Connect from the Start Menu or Desktop.
- On the Welcome to Azure AD Connect page, click Configure.
- On the Additional tasks page, select the View current configuration task.
- Click Next.
- Scroll down on the Review Your Solution page.
- On the last line of the configuration items, you’ll find the SQL SERVER NAME and SQL SERVER INSTANCE NAME mentioned underneath their respective headings.
- Click Exit.
Alternatively, perform the following lines of Windows PowerShell to find the Microsoft SQL Server that holds the database for a specific Azure AD Connect installation, while signed in interactively to the Windows Server running Azure AD Connect:
Import-Module "C:\Program Files\Microsoft Azure AD Sync\Bin\ADSync\ADSync.psd1"
Get-ADSyncDatabaseConfiguration
This returns all the information on the database the specific Azure AD Connect installation utilizes.
Concluding
A full-fledged Microsoft SQL Server can be used to host the database for Azure AD Connect. I couldn’t believe there is no official documentation on how to configure Azure AD Connect to do so, so I wrote it myself.
Further reading
Move Azure AD Connect database from SQL Server Express to SQL Server
Prerequisites for Azure AD Connect
Troubleshoot SQL connectivity issues with Azure AD Connect
Field Notes: How has your Azure AD Connect been configured?
New features in AD DS in Windows Server 2012, Part 8: Group MSAs (gMSAs)
Using Azure AD Connect with a gMSA
Thank you!
I have to agree that it's been a stunning omission for years re no documentation on AAD Connect with SQL server AND using a GMSA account.
So thanks again for filling the breach. Really appreciate including the info on how to find the current DB configuration as well.
One thing I'm curious about whether there would be major issues creating a standby staging server by copying the main server's database, running custom setup on the staging server (after configuring all the accounts/db permissions) and seeing if that works. Might save a few steps with custom sync rules? Obviously not a "supported configuration" but might be interesting to try.
I don't think that'll work as the database also includes configuration. Some of it is encrypted. Some can't be reused.
I'm curious as to way you'd want to shave time of implementing an Azure AD Connect Staging Mode server. I see it as a task that doesn't have time constraints set to it, as you'd typically implement it right after implementing the actively synchronizing Azure AD Connect installation (or at least when the configuration is somewhat settled).
Yes, you're right, not ordinarily a step I'd consider for the Staging server, but it was anticipating a potential issue re implementing one at very short notice for a swing migration (that wasn't required).
But you're right, there would be encrypted config and all kinds of things, and it was a horrible idea anyway. Thanks!
Objective : Migrate AAD connect server from old to new without using staging server and use the existing SQL DB.
=========================================================
Existing AAD connect server- 2012 R2
AAD connect – 1.5.30.0
SQL server DB – instance(Full blown server)
sql service account – s1
=======================
New AAD connect server 2019
AAD connect – 1.5.30.0
sql service account – s1
Objective:
Action plan:
==================
1. Old/Existing AAD connect server – Turn on staging mode from Active Mode
2. Stop the AAD sync service on the old server.
3. New server – powershell – AADconnect.exe /useexistingdatabase
https://docs.microsoft.com/en-us/azure/active-directory/hybrid/how-to-connect-install-existing-database
Is it ok use the same SQL DB by the old AAD connect server in staging mode while i deploy the new AAD connect server and point to the same DB only for couple of minutes ?
Hi Shadab,
In the method you describe, the action to enable Staging Mode serves no purpose.
When the Azure AD Sync service is stopped, the former Azure AD Connect server will quit its database connection.
Using the /UseExistingDatabase switch, doesn't mean the new Azure AD Connect re-uses the database contents. It means the new Azure AD Connect re-uses the database configuration (name, connection, permissions, login).
Using the method you describe, you can't start the Azure AD Connect Sync service on the former Azure AD Connect server after configuring the new Azure AD Connect server. Its database contents have been overwritten and the contents no longer match the contents it expects. There is no rollback if the new Azure AD Connect server doesn't configure correctly.
Tip!
The new Azure AD Connect server will use a new service account in Azure AD. When you decommission the former Azure AD Connect server, don't forget to decommission the former service account in Azure AD.
Hi guys,
After reading the comments Im still a bit confused how to properly migrate AD Connect to a new server.
We want to keep our settings and customizations.
And we also have the DB hosted on full SQL cluster. Regards
Hi Nuno,
I would always perform an Azure AD Connect Swing Migration.
Howeverm I wouldn't use the Export/Import functionality for this particular scenario, as the database information is part of the exported information.
Your blog is very informative, thank you. I have been looking to setup a standby server. My current setup is a standalone sql server and and another server hosting the azure ad connect service. During initial setup of a standby can I point it to the current sql server or do I make new DB on the server or do I stand up a new sql server? Any input is appreciated.
Hi Rey,
Since Azure AD Connect version 1.3.20.0 (released April 2019), you can specify the database name to use on a remote SQL Server.
This feature enables reusing the same SQL Server (cluster) for databases of multiple Azure AD Connect installations. It is the most cost-effective option.
You might also opt to implement a new database server. However, the administrative effort to maintain SQL increases. It might make sense though in certain scenarios.
If it was VSA then installation wizard would automatically assign appropriate permissions to that service account. How about gMSA? Do we have to define the permissions in advance some how? If yes, what would those permissions be? I am talking about the permissions in Directory but not Database permissions.
No. Active Directory permissions are not set on the VSA or gMSA.
They are set on the AD Connector account. Please refer to this blogpost to properly delegate Directory permissions to the Azure AD Connect AD Connector account using groups.
Hi team,
Thanks for the guide, and for keeping up with the comments! I'm currently trying to install AD Connect 2.1.1.0 using a gMSA and a SQL AG. The installation is failing with the error:
"Error 25009.The Microsoft Azure AD Connect synchronization services setup wizard cannot configure the specified database. Error: ALTER DATABASE statement failed.The operation cannot be performed on database "ADSync" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group."
As far as I can tell, we've got everything setup according to recommendations here and the official MS documentation.
Is there anything obvious we might've missed?
Hi Cameron,
The database should become part of the Availability Group after you've initially configured Azure AD Connect.
Hi Sander,
Do I need to create a separate DB for each installation of Azure AD Connect?
Can I place them on the same SQL Server Instance?
Yes, every Azure AD Connect / Entra Connect installation needs its own database.
Yes, you can host all the databases on the same SQL Server instance.
Hi Sander! Thanks a lot for this documentation!!
Do you see any issue assigning the gMSA to a group of servers to consume it (like: Set-ADServiceAccount –Identity gMSA01 –PrincipalsAllowedToRetrieveManagedPassword “Group for gMSA”?
I want to have an ADConnect staging server using the same gMSA account, as well as use a gMSA to run the ADConnect service engine.
Thanks again!
Hi Nathan,
I advise against re-unsing a gMSA for multiple servers, from an account lifecycle management point of view. I feel the (g)MSA should live and die with the Entra Connect installation.
As the gMSA is also used to connect to the database server, you won't follow the principle of least privilege per Entra Connect installation and you'll introduce the risk of connecting to the wrong database for an Entra Connect installation.
When you use gMSAs for multiple servers and you mangle the gMSA in terms of privileges or permissions or even delete the object, you'll mangle multiple Entra Connect installations. It's effective, but not the way you want it to…