Using Managed Microsoft AD with Cloud SQL

This page describes the ways to use Cloud SQL to:

  • Integrate with Managed Service for Microsoft Active Directory (also called Managed Microsoft AD).
  • Connect to an instance with an AD user.

A Cloud SQL instance that is integrated with Managed Microsoft AD supports Windows Authentication in addition to SQL Authentication.

Before you begin

  1. In the Google Cloud Console, select your project name.
  2. Make sure that billing is enabled for your Google Cloud project. Learn how to confirm billing is enabled for your project.
  3. Install and initialize the Cloud SDK.
  4. Make sure that you have the Cloud SQL Admin role on your user account. Go to the IAM page.
  5. Review the prerequisites for integration.

Creating an instance with Windows Authentication

You can integrate with Managed Microsoft AD during instance creation, enabling Windows Authentication for the instance. To integrate, you choose a domain for the instance to join. If joining a domain fails, instance creation fails.

In preparation for creating an instance with Windows Authentication, review the tips and the limitations and alternatives.

The following are the options for creating an instance that is integrated with Managed Microsoft AD.

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.
  2. Click Create instance.
  3. Click Choose SQL Server.
  4. Enter a name for the instance. Do not include sensitive or personally identifiable information in your instance name; it is externally visible. You do not need to include the project ID in the instance name. This is created automatically where appropriate (for example, in the log files).
  5. Enter the password for the 'sqlserver' user.
  6. Set the region for your instance. See Best practices for integrating with Managed Microsoft AD.
  7. Under Configuration options, set your desired options (but wait until the next step for the authentication options).
  8. Click Authentication. The dropdown menu for joining a managed Active Directory domain lists any Managed Microsoft AD domains that previously were added in your project.
  9. From the dropdown menu for joining a managed Active Directory domain, select a domain.
  10. When you are finished selecting your configuration options, click Create. Cloud SQL automatically creates a Per-Product, Per-Project Service account for you. If the account doesn't have the appropriate role, you are prompted to grant the managedidentities.sqlintegrator role.

gcloud

The following command creates an instance that is integrated with Managed Microsoft AD and thus is enabled for Windows Authentication. For information about the basic command for creating an instance, see Creating instances.

Specify a parameter of--active-directory-domain=[DOMAIN] in the gcloud command. For example, specify the following: --active-directory-domain=ad.mydomain.com

Here is a prototype of the gcloud command:

gcloud beta sql instances create [INSTANCE_NAME] \
--database-version=[EDITION] \
--root-password=[PASSWORD] \
--active-directory-domain=[DOMAIN] \
--cpu=[CPU] \
--memory=[MEMORY]  \
--network=[NETWORK]

REST

Using the REST API, you can create an instance that is integrated with Managed Microsoft AD. Specify a domain, such as subdomain.mydomain.com, for the domain field, as shown in this prototype of a request:

{
   "databaseVersion":"database-version",
   "name":"instance-id",
   "region":"region",
   "rootPassword":"password",
   "settings":{
      "tier":"machine-type",
      "ipConfiguration":{
         "privateNetwork":"network"
      },
      "activeDirectoryConfig":{
         "domain":"domain"
      }
   }
}

Updating an instance with Windows Authentication

You can update the domain of an existing instance, changing or adding a domain.

For general information about updating an instance, see Editing instances.

If an instance is currently joined with a managed Active Directory domain, the instance is initially unjoined from that domain, before it is joined to the new domain. If the update fails, the instance may no longer be joined to any domain.

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.
  2. Select an instance to edit by clicking the instance name.
  3. Click Edit.
  4. Click Authentication. The Join an Active Directory domain dropdown menu lists the Managed Microsoft AD domains that previously were added in your project.
  5. From the dropdown menu for joining a managed Active Directory domain, select a new (replacement) domain for your instance.
  6. Click Save to apply your changes.

gcloud

The following is a prototype of a command to update an existing instance. The command either adds or replaces a domain. Pass--active-directory-domain=[DOMAIN] to the command, as follows:

gcloud beta sql instances patch [INSTANCE_NAME] \
--active-directory-domain=[DOMAIN]

REST

Using the REST API, you can update an existing instance. Specify a domain, such as subdomain.mydomain.com, in the domain field. The following is a prototype of a request:

{
   "settings":{
      "activeDirectoryConfig":{
         "domain":"domain"
      }
   }
}

Removing Windows Authentication from an instance

You can remove Windows Authentication, and thus a Managed Microsoft AD integration, from an existing instance.

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.
  2. Select an instance to edit by clicking the instance name.
  3. Click Edit.
  4. Click Authentication. The dropdown menu for joining a managed Active Directory domain lists the Managed Microsoft AD domains that previously were added in your project.
  5. From the dropdown menu, select No domain/Join later for your instance.
  6. Read the message about instance restart, and click Close.
  7. Click Save to apply your changes.

gcloud

To remove an instance from a domain, thus removing Windows Authentication, use a blank value for the domain. That is, in your command, use a blank value for the--active-directory-domain parameter, as follows:

    gcloud beta sql instances patch [INSTANCE_NAME] \
    --active-directory-domain=

REST

Using the REST API, you can remove an instance from a domain. Specify a blank value in the domain field, as follows:

{
   "settings":{
      "activeDirectoryConfig":{
         "domain":""
      }
   }
}

Connecting to an instance with a user

For Cloud SQL for SQL Server, the default user is sqlserver.

After you integrate an instance with Managed Microsoft AD, you can connect to the instance with the sqlserver user, as follows:

  1. Create a SQL Server login based on a Windows user or group, as follows:

    CREATE LOGIN [domain\user_or_group] FROM WINDOWS
    
  2. Log in to the instance, using Windows Authentication, with the instance DNS name. Examples of instance DNS names to specify are the following:

    • To connect via private IP:

      private.myinstance.us-central1.myproject.cloudsql.mydomain.com
      

    • To connect via public IP:

      public.myinstance.us-central1.myproject.cloudsql.mydomain.com
      

    • To connect via the Cloud SQL Auth proxy (also see below):

      proxy.myinstance.us-central1.myproject.cloudsql.mydomain.com
      

    If you use the instance IP address, Kerberos clients must be configured to support IP hostnames. From domains connected through a trust relationship, login with an IP address isn't supported.

Using the Cloud SQL Auth proxy with Windows Authentication

You can use the Cloud SQL Auth proxy with your Managed Microsoft AD integration.

Before you begin, review:

Steps for Windows Authentication

For background on starting the Cloud SQL Auth proxy, see Start the Cloud SQL Auth proxy.

For Windows Authentication, the Cloud SQL Auth proxy must be run on port 1433. You map a predefined Service Principal Name (SPN) entry to a Cloud SQL Auth proxy address:

proxy.[instance].[location].[project].cloudsql.[domain]

Running the Cloud SQL Auth proxy locally

If you run the Cloud SQL Auth proxy locally, use your hosts file to map the following to 127.0.0.1:

proxy.[instance].[location].[project].cloudsql.[domain]

As an example, you could add the following to the hosts file (for example, to c:\windows\system32\drivers\etc\hosts):

127.0.0.1 proxy.[instance].[location].[project].cloudsql.[domain]

In that example, you could run the Cloud SQL Auth proxy using this command, and make it available on 127.0.0.1:1433:

cloud_sql_proxy_x64.exe -credential_file credential.json  -instances=project:name=tcp:1433

Running the Cloud SQL Auth proxy non-locally

To run the Cloud SQL Auth proxy non-locally, follow the instructions in Running the Cloud SQL Auth proxy locally, but use a different entry in the hosts file.

Specifically, if a non-local host is, for example, MyOtherHost, you could add the following to the hosts file:

127.0.0.1 MyOtherHost proxy.[instance].[location].[project].cloudsql.[domain]

On-premises AD users: Creating a Windows login

You can use an on-premises AD user to create a Windows login to Cloud SQL for SQL Server.

As an example, you can connect using SQL Server Management Studio (SMSS) running on a Windows VM hosted in your Google Cloud project's Virtual Private Cloud (VPC).

For Windows Authentication in this context, Cloud SQL for SQL Server only supports the Kerberos protocol. For Kerberos-based Windows Authentication, the client must resolve the DNS name of the on-premises AD and the Managed Microsoft AD.

Configure one-way or two-way trust

Initially, decide whether to use a one-way or two-way trust relationship.

Then, follow the instructions for establishing trust between the on-premises AD domain and the Managed Microsoft AD domain.

Set up a Windows VM and create a Windows login

After you establish trust between the on-premises AD domain and the Managed Microsoft AD domain, complete the following steps. For example purposes, these steps use SQL Server Management Studio (SSMS), running on a Windows VM, hosted in your Google Cloud project's VPC:

  1. Create a Windows VM.
    • Create a VM with a version of Windows supported by Managed Microsoft AD.
    • Create the VM in the project that hosts your Managed Microsoft AD domain. If there is a Shared VPC that is an authorized network, you can also create the VM in any of its service projects.
    • Create the VM on a VPC network that is an authorized network of the Managed Microsoft AD domain and has configured private service access for Cloud SQL.
  2. Join the Windows VM to the Managed Microsoft AD domain.
  3. Install SSMS on the Windows VM.
  4. Resolve the on-premises domain in the VPC network.
    • From the authorized network on which the Windows VM is running, enable on-premises DNS resolution using the steps on the Resolving queries for non-Managed AD objects page. The steps on that page are prerequisites for Kerberos-based Windows Authentication to work for on-premises users.
  5. Create a Windows login for an on-premises user.

    • Follow the CREATE LOGIN instructions for creating a Windows login for an on-premises user. For example, specify a command similar to the following:
    CREATE LOGIN [DOMAIN_NAME\USER_NAME] FROM WINDOWS
    
  6. Log in to your Cloud SQL for SQL Server instance using the application-specific instructions for logging in an on-premises user. For example, if you are using SQL Server Management Studio, refer to these instructions.

If a problem occurs during a login to a SQL Server instance, perform these verifications:

  • Verify the firewall configurations of the on-premises network and project-authorized VPC, using the instructions for creating a trust with an on-premises domain.
  • Verify the Name Suffix Routing for the on-premises trust relationship.
  • Verify that you can perform these DNS resolution operations from the Windows VM that is running SSMS:
    • nslookup fqdn-for-managed-ad-domain
    • nslookup fqdn-for-on-premises-ad-domain
    • nslookup fqdn-for-cloud-sql-server-instance

Tips

  • An instance with public IP is supported, as long as it has private IP as well; private IP must be enabled for the instance. Then you can choose to use public IP or private IP to connect to the instance, as long as both are available.
  • If you receive one of the following errors, confirm that you have met all of the prerequisites for integration:
    • "Per-Product Per-Project Service Account is not found"
    • "Insufficient permission to integrate with Managed Service for Microsoft Active Directory domain"
  • If you receive the error "Domain not found", verify that the case-sensitive domain name is correct.
  • If Windows Authentication fails from a domain that is connected through a trust relationship, verify that Windows Authentication works for a user from a managed domain. If it does, then:
    1. Verify that you used a DNS name. IP addresses aren't supported from domains connected using a trust relationship.
    2. Ensure that you followed all of the steps for creating a trust with an on-premises domain, including the opening of all firewall ports.
    3. Validate the trust.
    4. Verify that the direction of the trust allows users from the domain (connected through a trust relationship) to authenticate in the managed domain.
    5. Verify that the name suffix routing is set on the domain that is connected through a trust relationship.
    6. Verify that the trust works without using Cloud SQL for SQL Server:
      1. Create a Windows VM.
      2. Join it to the Managed Microsoft AD domain.
      3. Try to run, for example, Notepad as a user from the domain that is connected through a trust relationship.
    7. Restart the client VM and retest Windows Authentication.
  • You may try to create a SQL Server login, but then receive the following error: "Windows NT user or group domain\name not found. Check the name again". This may have occurred because domain local groups are not supported; if applicable, use global or universal groups instead.
  • When issued by a user from a domain connected through a trust relationship, SQL Server queries can result in the following error: "Could not obtain information about Windows NT group/user". This error can occur, for example, if you are creating logins from domains connected through a trust relationship. The error also can occur if you are granting privileges to logins from domains connected through a trust relationship. In these cases, retrying the operation is often successful. If retrying fails, close the connection and open a new connection.
  • If SQL Server queries result in the error, "The login is from an untrusted domain", note that IP addresses aren't supported for users from domains connected through a trust relationship. Additionally, the following actions may resolve this issue:
    • If an IP address is used to connect users from a managed domain, follow these instructions.
    • Avoid using any proxies, and always use the same DNS name to connect to Cloud SQL for SQL Server, as you see the name in the Google Cloud Console.
    • Purge the existing Kerberos tickets. The above error might occur if you had a client that recently was connected to a SQL Server instance and the instance was stopped and started. Alternatively, the error might occur if Windows Authentication was disabled and then re-enabled for the SQL Server instance. If the client uses the Windows credentials cache, then lock and unlock the client workstation, or run klist purge.
  • An attempt to enable Windows Authentication may result in the error, "This instance would need a more recent creation date to support Managed Service for Microsoft Active Directory." Note the following:
    • In Cloud SQL, if a SQL Server instance was created on or before March 12, 2021, it cannot be integrated with Managed Microsoft AD.
  • An attempt to create a SQL Server instance may result in the error, "This instance does not support Managed Service for Microsoft Active Directory." If you receive this error, the project may be unsupported; try using a different project.
  • If an instance has ongoing problems with Windows Authentication (whether or not the instance was recently updated), try unjoining the managed Active Directory domain and then rejoining it. To do so, use the update procedure to unjoin and then to rejoin the domain. Doing so doesn't remove any existing Windows-authenticated users or logins that exist in your databases. However, removing Windows Authentication causes an instance to restart.

Troubleshooting

Click the links in the table for details:

For this error... The issue might be... Try this...
Per-product, per-project service account not found. The service account name is incorrect. On the Service Accounts page, ensure that you created a service account for the correct user project.
Insufficient permission to integrate with Managed Service for Microsoft Active Directory domain. The managedidentities.sqlintegrator role is missing on the service account. From the IAM and Admin page, add the managedidentities.sqlintegrator role on your service account.
Domain not found. The domain does not exist or was mistyped. Ensure the domain name is correct and exists in the same user project. It is case sensitive.
The domain is busy with another operation. Please retry. Another Cloud SQL instance is running an operation on the same Managed Active Directory domain. Retry the operation. If you are performing a batch of updates to Cloud SQL instances connected to the same domain, limit how many are performed in parallel.
The operation completed but an update to Active Directory failed. You may experience issues with Windows Authentication on this instance, please see https://cloud.google.com/sql/docs/sqlserver/configure-ad for tips. The required updates could not be performed on the Managed Active Directory domain. If you experience issues with Windows Authentication, you can try unjoining the managed Active Directory domain and then rejoining it. To do so, use the update procedure to unjoin and then to rejoin the domain. Doing so doesn't remove any existing Windows-authenticated users or logins that exist in your databases. However, removing Windows Authentication causes an instance to restart.
This instance would need a more recent creation date to support Managed Service for Microsoft Active Directory. In Cloud SQL, if a SQL Server instance was created on or before March 12, 2021, it cannot be integrated with Managed Microsoft AD. Try your operation on an instance created after March 12, 2021.

What's next

  • Confirm that you have fully reviewed the overview page, which includes limitations and unsupported features. That page also includes links to additional documentation.