Enabling Active Directory (AD) Authentication in Arc-enabled SQL Managed instance

Active Directory (AD) has been the main Identity and Access Management (IAM) over the last 20 plus years for those traditional SQL Server customers. Nowadays, more and more organizations are actively migrating to the public cloud such as Microsoft Azure. Data sovereignty and legacy concerns are slowing their footsteps. Azure-Arc enabled SQL Managed instance comes in and makes it possible for those customers take advantage of the key capabilities of SQL Server RDBMS engine while storing their legacy data in their private datacenter or  any other certified platform with better manageability and lower maintenance cost, truly enabling the infinity possible to enable cloud native data pattern in the hybrid cloud identity scenario.

Background

In 2017, we reinvented SQL Server by bringing SQL Server engine to the Linux world, SQL Server as a comprehensive data analytics platform is to be now officially capable of up and running everywhere.  We enabled AD authentication for SQL Server on Linux and Linux containers by using a keytab file.  The keytab file is a cryptographic file containing Service principal names (SPNs) and hostnames, and it is used to automatically log into Active Directory (AD) instead of configuring a Windows service account as we did for SQL Server in Windows.

In December 2021, we enabled Azure Arc-enabled SQL Managed instance in Active Directory (AD) authentication with Customer-managed keytab (CMK) which allows customers to create and manage their own keytab file with tools such as adutil or bring their existing keytab file.  Today I’m proud to present the Azure-arc enabled AD-integrated SQL Managed instance with system-managed keytab (SMK).

What is the difference between Azure-Arc enabled SQL Managed instance Active Directory in (AD) authentication with customer-managed keytab (CMK) and system-managed keytab (SMK)?

Active Directory (AD) authentication is a key security feature for Arc-enabled SQL Managed instance. AD authentication is Kerberos protocol-based authentication, it enables domain-joined Windows or Linux clients to authenticate to SQL Server with their AD domain account without being prompted for a password, it also makes it possible to enable a centralized password policy across your organization where you can set up password complexity requirements so people can choose a compliant password for their accounts or decide how often those AD users are going to change their passwords.

In the customer-managed keytab (CMK) mode,  it is up to users to provide a pre-created Active Directory (AD) account and registered Service Principal Names (SPNs) under that AD account, and then generate the keytab file using active directory(AD) management tools such as adutil utility.

In the the system-managed keytab (SMK ) mode, users only need to provide an Organizational Unit (OU) and a domain service AD account which has specific permission on the Organizational Unit (OU) level in the Active Directory.  The system will generate an AD account for the SQL Managed Instance automatically and registering the SPNs automatically on that AD account. The keytab file is generated and it is delivered to the SQL Managed instance.

In action

The Arc-enabled SQL Managed instances uses an existing on-premises Active Directory (AD) domain for authentication. Users need to follow these steps to enable Active Directory authentication for an Arc-enabled SQL Managed Instance:

• Deploy a data controller.
• Deploy an Active Directory (AD) connector to enable Active Directory Authentication where you determine the AD integration mode of the deployment is customer-managed keytab (CMK) or system-managed keytab (SMK)  for Arc-enabled SQL Managed Instances. You can deploy the AD connector with Azure CLI.
• Deploy Arc-enabled SQL Managed instance. Directory (AD) Authentication in Arc-enabled SQL Managed instance, we need to reference the Active Directory (AD) Connector in the yaml definition file or with Azure CLI while deploying Arc-enabled SQL Managed instance to enabling Active Directory authentication feature. 

The following diagram demystifies aforementioned steps about how users enable Active Directory (AD) authentication in Arc-enabled SQL Managed Instance with 3 steps:

          Enabling AD Authentication in Arc-enabled SQL Managed instance

Looking forward

Active Directory (AD) Authentication for Arc-enabled SQL Managed instance is one of the most popular demands from our security sensitive customers. It fits in well inside the organization in the hybrid identity scenario. Feel free to comment below about your feedback and which are the hybrid identity solutions your organization looking into?