You can integrate Cloud SQL for SQL Server with Managed Service for Microsoft Active Directory (also called Managed Microsoft AD). This page contains information to review before your start an integration. After reviewing the information below, including the limitations, see Using Cloud SQL with Managed Microsoft AD.
Advantages of integrating with Managed Microsoft AD
Authentication, authorization, and more are available through Managed Microsoft AD. For example, joining an instance to a Managed Microsoft AD domain enables you to log in using Windows Authentication with an AD-based identity.
Integrating Cloud SQL for SQL Server with an AD domain has the additional advantage of Cloud integration with your on-premises AD domains.
Prerequisites for integration
You can integrate with Managed Microsoft AD, adding support for Windows Authentication to an instance. However, before integrating, the following are required for your Google Cloud project:
- A Managed Microsoft AD domain in the same project as the Cloud SQL for SQL
Server instance. For information about setting up a domain, see
Quickstart: Creating a domain.
- If you want to connect on-premises AD domains through a trust relationship, see Creating a one-way trust and Use an on-premises AD user to create a Windows login to Cloud SQL.
- A Per-Product, Per-Project Service account, as described below; see Creating a service account.
Creating and configuring a service account
You need a Per-Product, Per-Project Service account for each project that you
plan to integrate with Managed Microsoft AD. Use
gcloud or the Console to
create the account at the project level. The Per-Product, Per-Project Service
account should be granted the
managedidentities.sqlintegrator role on the
project. For additional information, see
gcloud projects set-iam-policy.
If you are using the Google Cloud Console, then Cloud SQL automatically creates a
service account for you, and prompts you to grant the
To create a service account with
gcloud, run the following command:
gcloud beta services identity create --service=sqladmin.googleapis.com \ --project=[PROJECT]
That command returns a service account name in the following format:
Here is an example of a service account name:
Granting the necessary permission for integration requires existing permissions. For the required permissions, see Required permissions.
To grant the necessary permission for integration, run the following command:
gcloud projects add-iam-policy-binding [PROJECT] \ --member=serviceAccount:service-[PROJECT_NUMBER]@gcp-sa-cloud-sql.iam.gserviceaccount.com \ --role=roles/managedidentities.sqlintegrator
Also see gcloud beta services identity create.
Best practices for integrating with Managed Microsoft AD
Having a SQL Server instance and a managed AD instance in the same region offers the lowest network latency and the best performance. Thus, when possible, set up a SQL Server instance and an AD instance in the same region. Additionally, whether or not you set them up in the same region, set up a primary and a backup region for higher availability.
Limitations and alternatives
The following limitations apply when integrating with Managed Microsoft AD:
- 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.
- Some restricted operations may result in the following error: "Could not obtain information about Windows NT group/user". One example of this type of restricted operation is creating logins by users from domains that are connected through a trust relationship. Another example is granting privileges to users from domains that are 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.
- Fully qualified domain names (FQDNs) aren't supported by SQL Server on
Windows. 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
- 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 cannot be managed from the Google Cloud Console.
- A Managed Microsoft AD domain and the corresponding SQL Server instances must be in the same Google Cloud project.
- Restoring an instance from a backup causes a previously-connected instance to disconnect from a Managed Microsoft AD domain.
- In Cloud SQL, if a SQL Server instance was created on or before March 12, 2021, it cannot be integrated with Managed Microsoft AD.
- Windows Authentication may not work with an external trust. The 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.
- Windows Authentication might not work with domain names whose total length
is less than eight characters, as in, for example,
Unsupported for integration
The following features currently are unsupported when integrating with Managed Microsoft AD:
- Domain local groups.
- Dropping SQL Server logins by users from domains that are connected through
a trust relationship. You can do this operation with a user from your
managed domain, or through the
- NTLM authentication.
- Login with an IP address from domains connected through a trust relationship.
- Instances with long names (more than 63 characters).
- Review the Quickstart for creating a Managed Microsoft AD domain.
- Prepare to create an integrated Cloud SQL instance.
- Learn how to create a trust relationship between on-premises domains and a Managed Microsoft AD domain.
- Review how to view integrated instances.