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 supportsWindows Authenticationin 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 initializethe gcloud CLI.
4. Make sure that you have theCloud SQL Admin role on your user account.Go to the IAM page.
5. Review theprerequisites for integration.

Create an instance with Windows Authentication

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

In preparation for creating an instance with Windows Authentication, review thetips and thelimitations and alternatives.

gcloudbetasqlinstancescreateINSTANCE_NAME\--database-version=EDITION\--root-password=PASSWORD\--active-directory-domain=DOMAIN\--cpu=CPU\--memory=MEMORY\--network=NETWORK

resource "google_sql_database_instance" "instance_with_ad" {  name             = "database-instance-name"  region           = "us-central1"  database_version = "SQLSERVER_2019_STANDARD"  root_password    = "INSERT-PASSWORD-HERE"  depends_on = [google_service_networking_connection.private_vpc_connection]  settings {    tier = "db-custom-2-7680"    active_directory_config {      domain = "ad.domain.com"    }    ip_configuration {      ipv4_enabled    = "false"      private_network = google_compute_network.private_network.id    }  }  # set `deletion_protection` to true, will ensure that one cannot accidentally delete this instance by  # use of Terraform whereas `deletion_protection_enabled` flag protects this instance at the GCP level.  deletion_protection = false}

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

Update 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, seeEditing instances.

If an instance is currently joined with a Managed AD domain, theinstance is initiallyunjoined from that domain, before it is joined to the new domain. If the updatefails, the instance may no longer be joined to any domain.

gcloudbetasqlinstancespatchINSTANCE_NAME\--active-directory-domain=DOMAIN

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

Integrate with a managed AD domain in a different project

You can integrate your instance with a Managed Microsoft AD domain that is in adifferent project.

While planning your integration, review theconstraints.

Enable domain peering

Before proceeding with the steps in the sections below,enable domain peeringso your domain is available to all necessary projects with Cloud SQL for SQL Server instances.

For a list of domains from other projects that already are available, you canspecify the following:

`gcloudactive-directorypeeringslist`

For more information, seeList domain peerings.

Thegcloud active-directory peerings list command requires themanagedidentities.peerings.list permission. The following roles have thispermission:

• roles/managedidentities.peeringViewer
• roles/managedidentities.viewer

For more information, seeAccess control with IAM.

Create a service account

Repeat these steps for each project that contains a Cloud SQL for SQL Server instance that youintend to integrate.

1. Review thebackground information for creating service accounts.

2. Use a command similar to the following to create a service account. Specifythe ID of the project containing Cloud SQL for SQL Server instances:

   gcloudbetaservicesidentitycreate--service=sqladmin.googleapis.com\--project=[PROJECT_ID]

3. Grant themanagedidentities.sqlintegrator role in the projectwith the Managed Microsoft AD instance. Specify the ID of the projectcontaining the Managed Microsoft AD instance:

   gcloudprojectsadd-iam-policy-binding[PROJECT_ID]\--member=serviceAccount:SERVICE_ACCOUNT--role=roles/managedidentities.sqlintegrator

Enable cross-project Windows authentication

You can integrate with an AD domain in a different project usinggcloudcommands or the Cloud SQL REST API. In either case, you must specify the fulldomain resource name.

Specify the full domain resource name when a Cloud SQL for SQL Server instance is created orupdated. Two formats are supported:

• projects/PROJECT_ID/locations/global/domains/DOMAIN_NAME
• projects/PROJECT_NUMBER/locations/global/domains/DOMAIN_NAME

Here is an example usinggcloud:

gcloudbetasqlinstancespatchINSTANCE_NAME\--active-directory-domain=projects/PROJECT_ID/locations/global/domains/DOMAIN_NAME

If you use a short domain resource name (such asDOMAIN_NAME), thesystem assumes that your Managed Microsoft AD domain is in the same project asyour Cloud SQL for SQL Server instances.

Constraints for integrating with different projects

If you are integrating with a managed AD domain in a different project, thefollowing constraints apply:

• Up to 10 networks with Cloud SQL for SQL Server instances can share one ManagedMicrosoft AD instance located in a different project.
• The Google Cloud console only supports Managed Microsoft AD instances located inthe same project. Instead of using the Google Cloud console, you can integrateusinggcloud commands or the Cloud SQL REST API.
• If VPC Service Controls are used, Cloud SQL for SQL Server instances and a ManagedMicrosoft AD instance must be located in the same perimeter.

Additionally, if an instance is integrated with a managed AD domain in adifferent project, the fully qualified domain name (FQDN) shown in theGoogle Cloud console might be inaccurate for that instance. Specifically, on theOverview page for that instance, underConnect to this instance, theFQDNs might contain strings separated by slashes, which you can ignore.For example, an inaccurate FQDN might be shown as:

private.myinstance.myregion.myproject.projects/mydirectory/locations/global/domains/mydomain.com

In that case, the accurate FQDN is:

private.myinstance.myregion.myproject.cloudsql.mydomain.com

Remove Windows Authentication from an instance

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

gcloudbetasqlinstancespatchINSTANCE_NAME\--active-directory-domain=

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

Connect to an instance with a user

For Cloud SQL for SQL Server, the defaultuser issqlserver.

After you integrate an instance with Managed Microsoft AD, you can connect tothe instance with thesqlserver user, as follows:

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

   CREATELOGIN[domain\user_or_group]FROMWINDOWS

2. Log in to the instance, using Windows Authentication, with the instance DNSname. Examples of instance DNS names to specify are the following:

   • To connect through 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 seebelow):

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

   If you use the instance IP address, you mustconfigurethe Kerberos clients.to support IP hostnames. Cloud SQL doesn't support logins from domainsconnected through a trust relationship.

Use 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:

• About the Cloud SQL Auth Proxy

• Connecting using the Cloud SQL Auth Proxy

Steps for Windows Authentication

For background on starting the Cloud SQL Auth Proxy, seeStart the Cloud SQL Auth Proxy.

For Windows Authentication, you must run the Cloud SQL Auth Proxy on port 1433.To map a predefined Service Principal Name (SPN) entry to a Cloud SQL Auth Proxy address, use:

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

Run the Cloud SQL Auth Proxy locally

If you run the Cloud SQL Auth Proxy locally, use your hosts file to map the following to127.0.0.1:

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

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

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

In that example, you could run the Cloud SQL Auth Proxy using this command, and make itavailable on127.0.0.1:1433:

cloud-sql-proxy.exe--credentials-filecredential.jsonproject:name

Run the Cloud SQL Auth Proxy non-locally

To run the Cloud SQL Auth Proxy non-locally, follow the instructions inRunning the Cloud SQL Auth Proxy locally, but use a different entryin 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.1MyOtherHostproxy.[instance].[location].[project].cloudsql.[domain]

Troubleshoot for NTLM fallback in clients

If you use Windows Authentication and an instance IPaddress to log in to an instance, you mustconfigure a Kerberos clientto support IP hostnames.

Cloud SQL doesn't support NTLM authentication, but some Kerberos clients might try tofall back to it. As discussed in this section, if you try to connect with SQLServer Management Studio (SSMS), and the following error message occurs, alikely cause is NTLM fallback:

Loginfailed.TheloginisfromanuntrusteddomainandcannotbeusedwithIntegratedauthentication.(MicrosoftSQLServer,Error:18452)

NTLM is a set of Microsoft security protocols for authentication. Also seeReasons for NTLM fallback.

Verification of an NTLM fallback for a Windows client

From Windows, to verify that an NTLM fallback caused an error::

1. Log in with the desired on-premises credentials (do not use "Run as...").
2. Open a command prompt.
3. Runklist purge.
4. From SSMS, try to connect to SQL Server with Windows Authentication.
5. Runklist and check if there's a ticket issued for"MSSQLSvc/<address>:1433 @ domain".
6. If there's no such ticket, NTLM fallback is the likely cause for the error.
7. If there is such a ticket, check that your SQL Server driver doesn't enforceNTLM authentication. Also check if NTLM authentication is enforced via GroupPolicy.

Verification of an NTLM fallback for a Linux client

From Ubuntu 16.04, to verify that an NTLM fallback caused an error,use the steps in this section. The steps are similar as those for other Linuxdistributions.

Set up Kerberos authentication

1. Set up a Kerberos client:

   sudoapt-getinstallkrb5-user

2. When you are prompted for the default realm, type an on-premises domain namein uppercase letters.

3. Run the following to install SQL Server command-line tools:

   curlhttps://packages.microsoft.com/keys/microsoft.asc|sudoapt-keyadd-curlhttps://packages.microsoft.com/config/ubuntu/16.04/prod.list|sudotee/etc/apt/sources.list.d/msprod.listsudoapt-getupdatesudoapt-getinstallmssql-toolsunixodbc-dev

Connect with Windows Authentication

1. Run the kinit tool as follows:kinit <user_account>
2. To connect with Windows Authentication, run:/opt/mssql-tools/bin/sqlcmd -S <address >>
3. Run theklist command and check whether a ticket was issuedspecifically for:"MSSQLSvc/<address>:1433 @ domain"
4. If the ticket wasn't issued, the above error likely indicates an issuethat causes NTLM fallback.

Reasons for NTLM fallback

Fallback onto NTLM is a client misconfiguration that can be associated withthe following conditions:

• By default, Windows doesn't attempt Kerberos authentication for a host if thehostname is an IP address. To enable Kerberos authentication from the manageddomain, try the method described in theMicrosoft documentation.This method doesn't work with on-premises credentials when you must use FQDNS.
• Kerberos authentication over external trusts doesn't work. Use forest trustsinstead, as describedhere.
• Kerberos authentication requires name suffix routing to enable the finding ofservices in another forest. Try the method describedhere.
• Kerberos authentication doesn't work if there's no SPN registered for theservice. Use only FQDNs or IP addresses you obtain from the Google Cloud consoleto connect with Windows Authentication.

On-premises AD users: Creating a Windows login

Initially, decide whether to use aone-way or two-way trust relationship.Note:We strongly recommend that you use a two-way trust relationship between a Managed AD domainand an on-premises domain because one-way trust instances might experiencelong-running connection drops and can't run certain privileged operations, such as create and drop login. For more information, seeUnderstand Active Directory authentication for SQL Server on Linux and containers.

Then, follow theinstructions for establishing trustbetween 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 theManaged Microsoft AD domain, complete the following steps. For examplepurposes,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 VMwith aversion of Windows supported by Managed Microsoft AD.
   • Create the VM in the project that hosts your Managed Microsoft ADdomain. If there is a Shared VPC that is an authorized network, you canalso create the VM in any of its service projects.
   • Create the VM on a VPC network that is an authorized network of theManaged Microsoft AD domain and has configured private serviceaccess for Cloud SQL.
2. Jointhe Windows VM to the Managed Microsoft AD domain.
3. Install SSMSon the Windows VM.
4. Resolve the on-premises domain in the VPC network.
   • From the authorized network on which the Windows VM is running, enableon-premises DNS resolution using the steps on theResolve queries for non-Managed Microsoft AD objectspage. The steps on that page are prerequisites for Kerberos-basedWindows Authentication to work for on-premises users.

5. Create a Windows login for an on-premises user.

   • Follow theCREATE LOGIN instructionsfor creating a Windows login for an on-premises user. For example,specify a command similar to the following:

   CREATELOGIN[DOMAIN_NAME\USER_NAME]FROMWINDOWS

6. Log in to your Cloud SQL for SQL Server instance using theapplication-specific instructions for logging in an on-premises user. Forexample, if you are using SQL Server Management Studio, refer tothese instructions.

Resolve problems related to an on-premises AD user

If a problem occurs during a login to a Cloud SQL for SQL Server instance, perform theseverifications:

• Verify the firewall configurations of the on-premises network andproject-authorized VPC, using the instructions forcreating a trust with an on-premises domain.
• Verify theName Suffix Routingfor the on-premises trust relationship.
• Verify that you can perform these DNS resolution operations from the WindowsVM 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 aswell; private IP must be enabled forthe instance. Then you can choose to use public IP or private IP to connectto the instance, as long as both are available.
• Before creating an instance, including as a replacement instance, reviewthe following as needed:
  • Learn about using private IP
  • Configure public IP
• Terraform issupported.
• If you receive one of the following errors, confirm that you have met all oftheprerequisites for integration:
  • "Per-Product Per-Project Service Account is not found"
  • "Insufficient permission to integrate with Managed Service for MicrosoftActive Directory domain"
• If you receive the error "Domain not found", verify that the case-sensitivedomain name is correct.
• If Windows Authentication fails from a domain that is connected through atrust relationship, verify that Windows Authentication works for a user froma managed domain. If it does, then:
  1. Verify that you used a DNS name. IPaddresses aren't supported from domains connected using a trustrelationship.
  2. Ensure that you followedall of the steps for creating a trust with an on-premises domain,including the opening of all firewall ports.
  3. Validatethe trust.
  4. Verify that the direction of the trust allows users from thedomain (connected through a trust relationship) to authenticate in themanaged domain.
  5. Verifythat the name suffix routing is set on the domain that is connected througha 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 domainthat 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 followingerror: "Windows NT user or group domain\name not found. Check the nameagain". This may have occurred because domain local groups are notsupported; 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 obtaininformation about Windows NT group/user". This error can occur, for example,if you are creating logins from domains connected through a trustrelationship. The error also can occur if you are granting privileges tologins from domains connected through a trust relationship. In these cases,retrying the operation is often successful. If retrying fails, close theconnection and open a new connection.

• If you get an error "Could not obtaininformation about Windows NT group/user", check the network connectivity to on-premises domains using the log fileactive_directory.log available in Cloud Logging for the Cloud SQL for SQL Server instance.This file contains the following diagnostics regarding connectivity changes to the on-premises domain:

  Note:The timestamps in the file are in local time.
  • Trusted on-premises domains by the Cloud SQL for SQL Server instance.For example, the following log shows the change from no trusted domains to two new trusted domains as their NetBIOS names,ONPREM andCHILD:

    2023-06-1220:55:09.975Detectedchangeintrustedonpremdomains:Previouslytrustedonpremdomains:[].Currenttrustedonpremdomains:[ONPREMCHILD]

    If an on-premises domain is not listed or is logged as not trusted, then check if the trust exists with the Managed AD domain and is validated. If there is a one-way trust between the Managed AD domain and on-premises domain, other on-premises domains trusted by the on-premises domain might not be visible.
  • Reachable and unreachable on-premises domains using a regular ping from the Cloud SQL for SQL Server instance.For example, the following log shows the change from no reachable domains to two new reachable domains,onprem.com andchild.onprem.com:

    2023-06-1220:55:10.664Detectedchangeinreachableonpremdomains:Previouslyreachableonpremdomains:[].Currentreachableonpremdomains:[onprem.comchild.onprem.com]

    If a domain is not listed in reachability logs, then ensure that it is logged as a trusted domain first. Otherwise, it is not checked for reachability. We always have a VPC peering between a project with on-premises instances and Google Cloud projects. Having even one more VPC peering introduces transitive peering connection, which Cloud SQL doesn't support. Instead, we recommend that you use a VPN tunnel to connect an on-premises domain to Cloud SQL. There should be at most one peering connection between the on-premises project and the Google Cloud project with the Cloud SQL for SQL Server instances.
  • Successful and unsuccessfulMicrosoft remote procedure call (MSRPC) pings to on-premises domains from the Cloud SQL for SQL Server instance.For example, the following log shows the change from having no MSRPC pingable domains to two new MSRPC pingable domains,ONPREM andCHILD:

    2023-06-1220:55:10.664DetectedchangeinMSRPCpingabledomains:Previouslypingableonpremdomains:[].Currentpingableonpremdomains:[ONPREMCHILD]

    MSRPC pings are included as additional diagnostics and they might not work on some configurations. You can still verify on-premises domain connectivity through the first two diagnostics.

• If SQL Server queries result in the error, "The login is from an untrusteddomain", note that IP addresses aren't supported for users from domainsconnected through a trust relationship. Additionally, the following actionsmay resolve this issue:

  • If an IP address is used to connect users from a managed domain, followthese instructions.
  • Avoid using any proxies, and always use the same DNS name to connect toCloud 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 youhad a client that recently was connected to a Cloud SQL for SQL Server instance andthe instance was stopped and started. Alternatively, the error mightoccur if Windows Authentication was disabled and then re-enabled for theCloud SQL for SQL Server instance. If the client uses the Windows credentials cache,then lock and unlock the client workstation, or runklist purge.

• An attempt to enable Windows Authentication may result in the error, "Thisinstance would need a more recent creation date to support Managed Servicefor Microsoft Active Directory." Note the following about this error:

  • In Cloud SQL, if a Cloud SQL for SQL Server instance was created on or beforeMarch 12, 2021, it cannot be integrated with Managed Microsoft AD.
  • In certain cases, if you create a Cloud SQL for SQL Server instance and do not enableManaged Microsoft AD at creation, you may receive the same error.After you review the othertips in this section, create a newinstance, enabling Managed Microsoft AD at the time you create theinstance.

• An attempt to create a Cloud SQL for SQL Server instance may result in the error, "Thisinstance does not support Managed Service for Microsoft Active Directory."If you receive this error, the project may be unsupported; try using adifferent project.

• If an instance has ongoing problems with Windows Authentication(whether or not the instance was recently updated), try unjoining themanaged Active Directory domain and then rejoining it. To do so, use theupdate procedureto unjoin and then to rejoin the domain. Doing so doesn't remove anyexisting Windows-authenticated users or logins that exist in your databases.However, removing Windows Authentication causes an instanceto restart.

• Use the AD diagnosis toolto troubleshoot AD setup issues with your on-premises domain and Cloud SQL for SQL Serverinstances in Google Cloud console.

Troubleshoot

Click the links in the table for details:

What's next

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