PolarDB:Integrate PolarDB for MySQL with a self-managed AD domain

Background information

Active Directory (AD) is a directory service from Microsoft. A directory is a hierarchical structure that stores information about objects on a local area network. An organization can store items such as computer accounts, user accounts, and groups in a directory to enhance security and simplify management.

PolarDB for MySQL allows you to integrate with an AD domain. By configuring AD domain service information, you can associate your cluster with Active Directory to enable Kerberos authentication.

Version requirements

Your PolarDB for MySQL cluster must meet the following requirements:

• Database engine version: 8.0.1.

• Minor version: 8.0.1.1.44.2 or later. If your cluster does not meet this requirement, you must upgrade its minor version.

• Product edition: Enterprise Edition.

• Series: Cluster Edition.

Usage notes

Prerequisites

• You have created an ECS instance. To connect PolarDB for MySQL to a custom domain, you must use an internal network, and the ECS instance must meet the following conditions:

  • The system image is Windows Server 2016 or a later version, with the language set to English.

• You have created an ECS instance for client authentication. The ECS instance that hosts the PolarDB for MySQL client must meet the following conditions:

  • The ECS instance that hosts the PolarDB for MySQL client must be in the same Virtual Private Cloud (VPC) as the target PolarDB for MySQL cluster.

  • The ECS instance that hosts the PolarDB for MySQL client must be in the same VPC as the ECS instance that hosts the AD domain controller.

• You are logged on with your primary Alibaba Cloud account.

Procedure

Step 1 (Optional): Configure an AD domain controller

1. Remotely log on to the ECS instance that runs Windows Server 2022.

   Note

   The AD domain controller requires a Windows Server operating system. We recommend using Windows Server 2016 or later. This topic uses Windows Server 2022 as an example.

2. Search for and open Server Manager.

3. Click Add roles and features and configure the parameters as follows.

   Page	Description
   Installation type	Keep the default settings.
   Server selection	Keep the default settings.
   Server roles	Select Active Directory Domain Services, and click Add Features in the pop-up dialog box. Select DNS Server, and click Add Features in the pop-up dialog box. If prompted that your computer lacks a static IP address, we recommend configuring one to prevent automatic IP address changes from making the DNS server unavailable.
   Features	Keep the default settings.
   AD DS	Keep the default settings.
   DNS Server	Keep the default settings.
   Confirmation	Click Install.

4. After the installation is complete, click Close.

5. In the left-side navigation pane, click AD DS, and then click More in the upper-right corner.

6. Click Promote this server to a domain and configure the parameters as follows.

   You can find this option in Server Manager on the All Servers Task Details and Notifications page. It is in the Action column for the Post-deployment Configuration task.

   Page	Description
   Deployment configuration	Select Add a new forest and set the domain name. In the Root domain name box, enter polardb.domain.
   Domain controller options	Set the Directory Services Restore Mode (DSRM) password. On the Domain Controller Options page, set both Forest functional level and Domain functional level to Windows Server 2016. Select the Domain Name System (DNS) server and Global Catalog (GC) checkboxes.
   DNS options	Clear the Create DNS delegation checkbox.
   Additional options	Keep the default settings.
   Paths	Keep the default settings.
   Review options	Keep the default settings.
   Prerequisites check	Click Install. Note The system restarts after the installation is complete.

7. Wait for the system to restart, then search for and open Server Manager again.

8. In the left-side navigation pane, click AD DS. Then, right-click the target domain controller server on the right and select Active Directory Users and Computers to open the AD user management module.

9. Right-click polardb.domain > Users, and select New > User.

10. Set the login user, then click Next.

    For Full name and User logon name, enter polardbmtestuser. Select @polardb.domain as the domain suffix, then click Next.

11. Set the logon password, select the Password never expires option, and then click Next > Finish.

12. Double-click the newly created user. In the Account options section, select the options to support Kerberos AES encryption.

    Select the This account supports Kerberos AES 128 bit encryption and This account supports Kerberos AES 256 bit encryption checkboxes.

13. Follow the same steps to create another user named polardbm. This user is for server-side authentication in PolarDB for MySQL, while polardbmtestuser is for client access.

Step 2 (Optional): Configure security group rules

1. Log on to the ECS console.

2. In the left-side navigation pane, choose Instances & Images > Instance.

3. In the top-left corner of the console, select a Region.

4. On the Instances page, click the ID of the target ECS instance.

5. Click the Security group tab. Then, find the target security group and click Manage Rules in the Actions column.

   Note

   The domain controller requires many open ports. We recommend creating a dedicated security group for the domain controller and not sharing it with other ECS instances.

6. On the Inbound tab, click Quick Add to allow access to the ECS instance through the following ports.

   Type	Port range	Description
   TCP	88	Kerberos authentication protocol port.
   TCP	135	Remote Procedure Call (RPC) port.
   TCP/UDP	389	Lightweight Directory Access Protocol (LDAP) port.
   TCP	445	Common Internet File System (CIFS) protocol port.
   TCP	3268	Global Catalog port.
   TCP/UDP	53	DNS port.
   TCP	49152-65535	Default dynamic port range for connections. Enter in the format: 49152/65535.

Step 3: Configure AD domain server information for PolarDB for MySQL

1. Navigate to the PolarDB cluster list, select the region at the top, and then click the target cluster ID.

2. In the left-side navigation pane, click Accounts, and then select the AD Domain Services tab. When you open the AD Domain Services tab for the first time, it includes two configuration sections: Configure Server Info in AD Domain andConfigure Client Info.

3. Click Configure Server Info in AD Domain.

   Server Service Name: Identifies the type of service that is requested for PolarDB for MySQL. The value is fixed as polardbm.

   Server Hostname: Identifies the service's hostname. The value is fixed at the cluster ID.

   Domain Name: The root domain name of your self-managed AD domain, such as POLARDB.DOMAIN.

   Note

   • If you use your enterprise's own AD domain, such as Azure Active Directory (now Microsoft Entra ID), you must enter the corresponding Domain name.

   • The Server Service Name, Server Hostname, and Domain Name combine to form the Kerberos Service Principal Name (SPN), which uniquely identifies a service instance on a network. The SPN is a core concept in the Kerberos authentication protocol that ensures clients can communicate securely with the correct services. The SPN format is typically: ServiceType/HostName@REALM.

   Server User in AD Domain: You must create a user account in the AD domain for server-side service authentication. After you enter the Server Hostname and Domain Name, a suggested command for generating a keytab file appears.

   ktpass -out xxx.keytab -princ polardbm/poxxx@POLARDB.DOMAIN -mapUser polardbm -pass yourPassword xxx xxx

   Note

   A Kerberos keytab file (or keytab) is a binary file that stores the mapping between Service Principal Names (SPNs) and their corresponding encryption keys within a Kerberos realm. This file is crucial for the Kerberos authentication process. If you use an AD domain as your Kerberos KDC, we recommend that you use the ktpass command to generate the keytab file. You need to replace -out xxx.keytab with the actual name for the generated keytab file, and replace -pass yourPassword with the password for the polardbm user you created in the AD domain.

4. Generate the keytab file.

   In the AD domain environment on your ECS instance, open Windows PowerShell to generate the keytab file.

   Execute the command to generate the keytab file.

   Windows PowerShell
   Copyright (C) Microsoft Corporation. All rights reserved.
   Install the latest PowerShell for new features and improvements! https://aka.ms/PSWindows
   PS C:\Users\Administrator> ktpass -out polardbmtest.keytab -princ polardbm/xxx xxx @POLARDB.DOMAIN -mapuser polardbm -pass xxx -crypto all -ptype KRB5_NT_PRINCIPAL
   Targeting domain controller: xxx.xxx.polardb.domain
   Using legacy password setting method
   Successfully mapped polardbm/xxx xxx to polardbm.
   Key created.
   Key created.
   Key created.
   Key created.
   Key created.
   Output keytab to polardbmtest.keytab:
   Keytab version: 0x502
   keysize 71 polardbm/xxx    xxx @POLARDB.DOMAIN ptype 1 (KRB5_NT_PRINCIPAL) vno 3 etype 0x1 (DES-CBC-CRC) keylength 8 (0xda2ab510c84fce73)
   keysize 71 polardbm/xxx    xxx @POLARDB.DOMAIN ptype 1 (KRB5_NT_PRINCIPAL) vno 3 etype 0x3 (DES-CBC-MD5) keylength 8 (0xda2ab510c84fce73)
   keysize 79 polardbm/xxx    xxx @POLARDB.DOMAIN ptype 1 (KRB5_NT_PRINCIPAL) vno 3 etype 0x17 (RC4-HMAC) keylength 16 (0x6017cf909fc53f720f1020652db561e6)
   keysize 95 polardbm/xxx    xxx @POLARDB.DOMAIN ptype 1 (KRB5_NT_PRINCIPAL) vno 3 etype 0x12 (AES256-SHA1) keylength 32 (0x7b780ab91260f36c06c153c02f357bc79c90256644db6523b33d0673219b07ea3)
   keysize 79 polardbm/xxx    xxx r8@POLARDB.DOMAIN ptype 1 (KRB5_NT_PRINCIPAL) vno 3 etype 0x11 (AES128-SHA1) keylength 16 (0x3da47d2f33d0827f0a56efcbbf0bfd06)

   Confirm that there are no errors in the output, and then use the setspn command to verify that the SPN is successfully mapped for the polardbm user.

5. Click Select File to upload the keytab file and complete the AD domain server configuration.

   Copy the keytab file from the AD domain to your local machine. Then, click Select File to upload it.

   In the Configure Server Info in AD Domain dialog box, Server Service Name defaults to polardbm. Enter the Server Hostname (the cluster endpoint), set Domain Name to POLARDB.DOMAIN, and set Server User in AD Domain to polardbm. The dialog box also provides a reference ktpass command for generating the keytab file.

   Click OK.

   Note

   After you submit the settings, the cluster status changes to Maintaining Instance.

Step 4: Configure PolarDB for MySQL client information

1. In the left-side navigation pane, click Accounts, select the AD Domain Services tab, and then click Configure Client Info.

2. In the Create Client User in AD Domain dialog box, enter the Client User and Domain Name.

   Client User: Create a Kerberos-authenticated access user in the PolarDB for MySQL cluster. This user corresponds to the client user, such as polardbmtestuser, created in the self-managed AD domain on the ECS instance.

   Domain Name: This corresponds to the root domain name of the self-managed AD domain on the ECS instance, such as POLARDB.DOMAIN.

   Click OK.

   Note

   After you submit the settings, the cluster status changes to Maintaining Instance.

3. In the left-side navigation pane, click Accounts and select the User Account tab.

   • The user created in the previous step is displayed. You can Modify Permissions for the user, but you cannot Change Password or Delete the user.

   • If you need to delete a user, you need to specify the user to Delete in the client information on the AD Domain Services tab.

Step 5 (Optional): Configure client authentication

You must use Kerberos to authenticate the MySQL client before you can access PolarDB for MySQL. You must perform the following operations on the client ECS instance from which you access PolarDB for MySQL.

1. Configure the /etc/krb5.conf file to access the self-managed AD domain on the ECS instance.

   # To opt out of the system crypto-policies configuration of krb5, remove the
   # symlink at /etc/krb5.conf.d/crypto-policies which will not be recreated.
   includedir /etc/krb5.conf.d/
   [logging]
       default = FILE:/var/log/krb5libs.log
       kdc = FILE:/var/log/krb5kdc.log
       admin_server = FILE:/var/log/kadmind.log
   # libdefaults configuration
   # Set default_realm in the [libdefaults] section to the root domain of the self-managed AD on the ECS instance (for example, POLARDB.DOMAIN).
   [libdefaults]
       dns_lookup_realm = false
       ticket_lifetime = 24h
       renew_lifetime = 7d
       forwardable = true
       rdns = false
       pkinit_anchors = FILE:/etc/pki/tls/certs/ca-bundle.crt
       spake_preauth_groups = edwards25519
       default_realm = POLARDB.DOMAIN
       default_ccache_name = KEYRING:persistent:%{uid}
   # realms configuration
   # Set kdc and admin_server in the [realms] section to the Domain Controller Service of the self-managed AD on the ECS instance.
   [realms]
   POLARDB.DOMAIN = {
   # ad.polardb.domain is configured in /etc/hosts and mapped to the IP address of the self-managed AD on the ECS instance. The default port is 88.
        kdc = ad.polardb.domain:88
        admin_server = ad.polardb.domain:88
   }
   # domain_realm configuration
   # The [domain_realm] section also needs to be configured with the root domain of the self-managed AD on the ECS instance.
   [domain_realm]
   .polardb.domain = POLARDB.DOMAIN
   polardb.domain = POLARDB.DOMAIN

   kinit username

   Replace username with the client user created in the AD domain, such as polardbmtestuser. When prompted, enter the user's AD domain password.

2. Use the following MySQL command to log in to the PolarDB for MySQL database cluster that is integrated with the AD domain. Use the primary address.

   Note

   Ensure your MySQL client is version 8.0.26 or later, which supports the authentication_kerberos_client plug-in. The --plugin-dir parameter specifies the directory containing authentication_kerberos_client.so and its dependency, authentication_oci_client.so.

   ./mysql -h [primary address] -P 3306 --default-auth=authentication_kerberos_client --plugin-dir=/root/mysql-client/plugin --user=polardbmtestuser

   [root@iZ2zee0l2f5hdwxs75m0urZ mysql-client]# ./mysql -h xxx rds.aliyuncs.com -P 3306 --default-auth=authentication_kerberos_client --plugin-dir=/root/mysql-client/plugin --user=polardbmtestuser
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 4374
   Server version: 8.0.13 Source distribution
   Copyright (c) 2000, 2024, Oracle and/or its affiliates.
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   mysql>