HOWTO: Deploy Azure AD Connect with SQL Server

Reading Time: 8 minutes

Hybrid Identity

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:

  1. Creating the gMSA
  2. Creating the database and configuring Always-on Availability
  3. 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.

Create an aptly named database for Azure AD Connect in Microsoft SQL Server Management Studio (click for original screenshot)

  • 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.

Set db_owner permissions on the database for the Azure AD Connect service account (click for original screenshot)

  • 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:
    1. Select the User an existing SQL Server option.
      1. Enter the hostname of the Microsoft SQL Server (listener) in the SERVER NAME field, or click the Browse button.
      2. Leave the INSTANCE NAME field blank to use the default SQL Server instance.
      3. Enter the database name for Azure AD Connect you created earlier in the DATABASE NAME field.
    2. Select the Use an existing service account option.
      1. Select the Managed Service Account option.
      2. 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.

Specify the SQL Server specifics and service account for Azure AD Connect (click for original screenshot)

  • 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.

SQL Server Name and SQL Server Instance Name in the Azure AD Connect Wizard (click for original screenshot)

  • 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

15 Responses to HOWTO: Deploy Azure AD Connect with SQL Server

  1.  

    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).

       
  2.  

    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!

  3.  

    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.

       
  4.  

    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

  5.  

    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 you're building an Azure AD Connect server in another datacenter or DR datacenter, then you might want to bring SQL Server there, too.
      • If you're adopting the Active Directory Administrative Tier model and want to place Azure AD Connect and its database in 'Tier 0', then you might want to deploy a 'Tier 0 database server' for the Azure AD Connect and AD FS databases.
       
  6.  

    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.

  7.  

    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.

       
  8.  

    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.

       

leave your comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.