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