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.
To implement Azure AD Connect with a full-fledged Microsoft SQL Server, you’ll need to meet the following 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.
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.
In the organization, make sure there is consensus on the name for the Azure AD Connect database.
Azure AD Connect version 184.108.40.206 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.
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.
When setting up the Availability Group Listener, it is recommended that you set the RegisterAllProvidersIP property to 0.
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.
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:
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
- Click OK to create the
- 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:
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:
Install-ADServiceAccount -Identity AADC1gMSA
Then, we can uninstall the Active Directory Domain Services role again:
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"
This returns all the information on the database the specific Azure AD Connect installation utilizes.
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.
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