Customer-managed Active Directory (CMAD) overview

This page contains information to review before you start an integration. After reviewing the following information, including the limitations, see Use customer-managed Active Directory.

You can integrate Cloud SQL for SQL Server with customer-managed Microsoft Active Directory (also referred to as customer-managed AD (CMAD)).

Authentication, authorization, and more are available through CMAD. For example, joining an instance to a CMAD domain lets you sign in using Windows Authentication with an AD-based identity. Integrating Cloud SQL for SQL Server with an AD domain has the additional advantage of Google Cloud integration with your on-premises AD domains.

Before you begin

You can integrate with CMAD, adding support for Windows Authentication to an instance. However, before integrating, the following are required for your Google Cloud project:

  • For authentication to work, your Cloud SQL instances must have network connectivity to all relevant Active Directory domains. This includes the primary domain the instance joins, as well as any trusted or child domains containing users who need to access Cloud SQL for SQL Server. To enable this, ensure the following ports (both TCP and UDP) are open between your Cloud SQL instances and all of your domain controllers: 53, 88, 135, 389, 445, 464, 3268, 3269, and 49152 through 65535.
  • You must create an organizational unit (OU) to store all related integration objects.
    • Within this OU, you also need an administrator account that has the following permissions:
      • Create Computer objects
      • Delete Computer objects
      • Create User objects
      • Delete User objects
      • Write all properties
      • Reset password
      • Read lockout time, write lockout time
    • We also recommend granting this administrator account permissions to manage DNS records, for example, by adding it to the DnsAdmins group.

      If you don't grant these permissions, the integration will still succeed. However, connecting to your instance will require you to manually create the necessary DNS records, as described in, Connect to an instance with a user.

      The alternative is to connect using the instance's IP address, which has limitations and won't work for connections from trusted domains.

  • You must store your administrator credentials in a Secret Manager secret, using the following JSON format: 
        {
      "credentials": [
        {
          "validAfterUTC": "VALID_AFTER_UTC_VALUE",
          "administratorLogin": "ADMINISTRATOR_LOGIN_VALUE_1",
          "administratorPassword": "ADMINISTRATOR_PASSWORD_VALUE_1"
        },
        {
          "validAfterUTC": "VALID_AFTER_UTC_VALUE_2",
          "administratorLogin": "ADMINISTRATOR_LOGIN_VALUE_2",
          "administratorPassword": "ADMINISTRATOR_PASSWORD_VALUE_2"
        }
      ]
    }

    Replace the following:

    • VALID_AFTER_UTC_VALUE_1: the first UTC value you want to use, provided in the format YYYY-MM-DDThh:mm:ssZ. An example might be 2099-07-01T10:30:00Z.
    • ADMINISTRATOR_LOGIN_VALUE_1: the first administrator login you want to use, such as myadmin. Don't include the domain name in the value. Entries similar to myadmin@my-domain-name.com aren't supported.
    • ADMINISTRATOR_PASSWORD_VALUE_1: the administrator password.
    • VALID_AFTER_UTC_VALUE_2: the second UTC value you want to use, provided in the format YYYY-MM-DDThh:mm:ssZ. An example might be 2099-07-01T10:30:00Z.
    • ADMINISTRATOR_LOGIN_VALUE_2: the second administrator's login you want to use, such as myadmin2. Similarly, don't include the domain name in the value. Entries similar to myadmin2@my-domain-name.com aren't supported.
    • ADMINISTRATOR_PASSWORD_VALUE_2: the second administrator's password.

    This file can contain multiple credential entries to mitigate issues with slow propagation or to accommodate planned, upfront rotations.

    The validAfterUTC field is optional, if it is not specified, then it will be assumed the credentials are always valid. We recommend keeping these credentials permanently available in Secret Manager and using automation to update the password if you rotate the credentials.

    While you can delete the secret after instance creation, be aware that future operations like cloning or adding a read replica will result in the new instance not being joined to the domain.

    Furthermore, deleting the original instance will leave orphaned objects, such as the computer account, in your CMAD.

  • Have a list of DNS server IP addresses for your customer-managed Active Directory, which are typically your domain controllers. We recommend using static IP addresses for these servers.
  • Assign Per-Product, Per-Project service accounts.

Create and configure a service account

To create a service account with the required permissions, verify the following:

To create a service account with gcloud CLI, run the following command:

  gcloud beta services identity create --service=sqladmin.googleapis.com 

      --project=PROJECT_ID

That command returns a service account name in the following format:

  service-PROJECT_ID@gcp-sa-cloud-sql.iam.gserviceaccount.com

Here is an example of a service account name:

  service-333445@gcp-sa-cloud-sql.iam.gserviceaccount.com

To grant the necessary permission for integration, run the following command:

  gcloud iam roles create secretIamPolicyManager --project=PROJECT_ID 

    --permissions="secretmanager.secrets.getIamPolicy,secretmanager.secrets.setIamPolicy"

Then, run the following command:

  gcloud secrets add-iam-policy-binding ADCredentials --project="722300452883" 

    --member="serviceAccount:service-SQL_PROJECT_NUMBER@gcp-sa-cloud-sql.iam.gserviceaccount.com" 

    --role="projects/PROJECT-ID/roles/secretIamPolicyManager"

For more information, see gcloud beta services identity create.

Best practices for integrating with CMAD

When integrating with CMAD, we recommend completing the following steps.

Prerequisites for integration

Use the Active Directory Diagnosis tool to troubleshoot AD setup issues with your on-premises domain and Cloud SQL for SQL Server instances in Google Cloud console. Skip steps related to Managed Service for Microsoft Active Directory.

Topologies for integrating with CMAD

Cloud SQL for SQL Server doesn't support domain local groups. However, the following alternatives are available:

  • Add global groups or individual user logins directly in SQL Server.
  • Use universal groups if all groups and users belong to the same forest.

Cloud SQL for SQL Server doesn't support domain local groups as logins. To grant permissions to domain users, you must use global or universal groups instead, as described in this section.

Option 1: Add user accounts and groups as logins to SQL Server

If you have multiple domains, in multiple forests, and you have multiple global groups, you can add all of the individual user accounts, and the global and universal groups, directly as logins to SQL Server. As an example of Option 1, see the following diagram:

CMAD topology, Option 1.

Option 2: Define a universal group in one of your domains

If your domains are in the same forest, you can define a universal group in one of your domains. Then you can add all of the individual user accounts, and the global and universal groups, as children of that defined universal group, and add the defined universal group as a SQL Server login. As an example of Option 2, see the following diagram:

CMAD topology, Option 2.

Limitations and alternatives

The following limitations apply when integrating with CMAD:

  • Domain local groups are not supported, but you can add global groups or individual user logins directly in SQL Server. Alternatively, you can use universal groups when all groups and users belong to the same forest.
  • If there are any additional trusted domains and you plan to access SQL Server instances with user names from there, then they need to be connected through a two-way trust. One-way and external trusts are not supported.
  • In general, new users created through the Google Cloud console are assigned the CustomerDbRootRole role, which has this SQL Server Agent fixed database role: SQLAgentUserRole. However, users created through SQL Server directly, such as CMAD users, can't be granted this role, or use SQL Server Agent, because the MSDB database where this role must be granted is protected.
  • Fully qualified domain names (FQDNs) aren't supported by SQL Server. Therefore, use domain names (short names), rather than FQDNs, when you create SQL Server logins. For example, if your domain name is ad.mydomain.com, then create SQL Server logins for ad\user, rather than for ad.mydomain.com\user.
  • To access SQL Server instances, always use FQDNs. For example, you could use an FQDN similar to private.myinstance.us-central1.myproject.cloudsql.mydomain.com. Netbios names aren't supported, nor are any short names if DNS suffixes are omitted.
  • SQL Server logins based on Active Directory users and groups can't be managed from the Google Cloud console.
  • Windows Authentication won't work with an external trust. The returned error might be the following: 
      "The target principal name is incorrect. Cannot generate SSPI context."
    Additionally, as related to Microsoft's recommendations, use a forest trust instead of an external trust for Kerberos authentication.
  • The latest version of the secret is always used. The secret must be active and can't be destroyed.

Active Directory endpoints and TLS connections

If you're using Windows Authentication and you want to establish a TLS connection without trusting the server certificate, you must rotate the certificates after Windows Authentication is enabled on the instance.

If the connection fails and one of your certificates was created before March 15, 2025, you must rotate the server certificate again and try the connection again.

Unsupported for integration

The following features are unsupported when integrating with CMAD:

  • Domain local groups.
  • NTLM authentication.
  • Login with an IP address from domains connected through a trust relationship.
  • Instances with long names (more than 63 characters).

Monitoring

You can use the following metric to monitor CMAD status and health:

cloudsql.googleapis.com/database/active_directory/domain_reachable

This metric reports if the CMAD is reachable from the Cloud SQL instance. It's a useful tool to help troubleshoot network issues:

cloudsql.googleapis.com/database/active_directory/instance_available

What's next