Active Directory Diagnosis Tool For Cloud SQL

Do you run a SQL Server instance in Google Cloud, and struggle with Windows authentication? This can be especially tough when integrating Cloud SQL for SQL Server with on-premises Active Directory domains through Managed Active Directory. Hybrid configurations can be complex to set up and hard to troubleshoot, with potential issues such as missing an AD trust between on-prem and Managed AD domain, missing name suffix routing, or DNS and port related misconfigurations.

Debugging these setup issues can be tricky and time-consuming so I’d like to show you a new tool which automates this debugging process.

This PowerShell script, which runs on your on-prem AD domain controller (DC) or a domain-joined Windows Virtual Machine (VM), is available on GitHub and performs the following checks for the on-prem DCs. It outputs a status for each (more details in the GitHub README):

• Unreachable on-prem DCs.
• Open TCP and UDP ports.
• Primary and secondary DNS server.
• Resolve Managed AD fully qualified domain name (FQDN).
• Resolve SQL Server fully qualified domain name (FQDN).
• Replication issues between DCs.
• DNS forwarding.
• AD trust setup between on-prem domain and Managed AD.
• Name suffix routing.
• Kerberos authentication for on-prem domain.
• Kerberos authentication for SQL Server SPNs and IP addresses.

Prerequisites

Prerequisites for running the script –

1. AD enabled on-prem domain controllers – https://cloud.google.com/architecture/deploy-fault-tolerant-active-directory-environment

2. Managed AD domain in Google Cloud – https://cloud.google.com/managed-microsoft-ad/docs/create-domain.

3. AD-enabled Cloud SQL for SQL Server instance joined to the Managed AD domain – https://cloud.google.com/sql/docs/sqlserver/configure-ad

Example Walkthrough

A valid AD trust between on-prem and Managed AD domain as well as name suffix routing between the two domains are required for successfully using an on-prem AD domain to login to a Cloud SQL for SQL Server instance. First, we’ll show how the tool identifies when a valid trust is not set up:

1. Create a Managed AD domain in Google Cloud.

2. RDP into an on-prem domain controller.

3. Open Active Directory Domains and Trusts.

4. Right-click the on-prem domain and select Properties. Validate that a trust does not exist for the Managed AD domain.

5. Right-click Powershell and Run as Administrator:

6. Copy the Powershell script from GitHub and run the script:

7. Status for the trust setup check will be a failure as expected:

8. Create a trust with your Managed AD domain, following documentation for creating trusts with an on-prem domain (https://cloud.google.com/managed-microsoft-ad/docs/create-trust):

9. Re-run the script and the trust check should return a passed status:

Likewise, see below steps to validate that name suffix routing is set up, which is also required on the on-prem AD domain:

1. Verify that name suffix routing does not exist for the Managed AD domain.

2. The script will return a failed status for this check.

3. Refresh the name suffix routing after creating the trust from the above walkthrough. You should see an entry for the Managed AD domain:

4. The name suffix routing check should now return a passed status.

To validate that the AD setup is complete:

1. RDP to a Windows VM in on-prem and join it to the on-prem domain.

2. Install and launch SQL Server Management Studio.

3. Connect to your Cloud SQL instance using Windows Authentication with an on-prem user:
User name: <your-on-prem-domain>\<on-prem-user>.
Password: <password>.

4. Run “SELECT 1” query to validate the database can be queried.

What’s next

Details on using the tool are available in the public documentation. For community contributions and sharing your feedback on the tool, please visit the GitHub contributions page. For more information on configuring AD with Cloud SQL, please visit the overview and guide.

By: Priyam Patel (Software Engineer, Cloud SQL)
Originally published at: Google Cloud Blog

Source: Cyberpogo