How do I troubleshoot RDS for SQL Server Windows Authentication issues with AWS Managed Microsoft AD?

Last updated: 2022-10-14

I have AWS Directory Service for Microsoft Active Directory configured for my AWS account. When creating an Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server DB instance, I encounter one of the following issues:

  • The Active Directory is unavailable.
  • I receive an error that says "Failed to join a host to a domain" or the Directory Status on the RDS console shows "Failed".
  • I can't log into the DB instance using Windows Authentication.

How can I troubleshoot these issues with AWS Managed Microsoft AD?

Short description

Windows Authentication for RDS for SQL Server DB instances is supported across multiple AWS accounts and Amazon Virtual Private Clouds (Amazon VPCs). A single AWS Managed Microsoft AD can be shared across multiple AWS accounts and VPCs to easily manage directory aware database workloads. However, this is true only if the RDS for SQL Server DB instances are in the same AWS Region as the AWS Managed Microsoft AD.

Resolution

Note: If you receive errors when running AWS Command Line Interface (AWS CLI) commands, make sure that you’re using the most recent AWS CLI version.

Active Directory isn't listed or is unavailable when creating a DB instance

Important: The managed domain type must be AWS Managed active directory for the Active Directory to be listed in the Amazon RDS console.

If the AWS Managed Microsoft AD is in a different Region than the instance, the directory isn't listed when you create or modify a DB instance. To resolve this issue, be sure that the DB instance is in same AWS Region as your Directory Service.

Confirm that the RDS DB instance and the Directory Service are in the same Region:

1.    Open the Amazon RDS console, and choose Databases from the navigation pane.

2.    Choose the DB instance that you want to connect to the directory.

3.    From the Summary section, review the Region associated with your DB instance.

4.    Confirm that the Directory Service is in the same AWS Region as the DB instance by checking the AWS Directory Service console.

If your AWS Managed Microsoft AD is in a different AWS account than the DB instance, share the AD with the AWS account. You can then list the Directory Service while creating or modifying the DB instance.

1.    Start sharing the directory with the AWS account that the DB instance will be created in. Follow the steps in Sharing your AWS Managed Microsoft AD directory for seamless EC2 domain-join in the AWS Directory Service Administration Guide.

2.    Sign in to the AWS Directory Service console using the account for the DB instance. Check that the domain has the SHARED status before continuing.

3.    Sign in to the AWS Directory Service console using the account for the DB instance, not the Directory ID value. Use this directory ID to join the DB instance to the domain.

Error received when joining a DB instance to a domain or Directory Status on the RDS Console shows "Failed"

When joining a DB instance to a domain, you might receive the following error message or the Directory status might appear as Failed:

"Failed to join a host to a domain. Domain membership status for instance XXXXXXX has been set to Failed."

1.    Confirm that the RDS for SQL Server instance security group is configured to allow the correct outbound traffic.

  • TCP and UDP Port 53
  • TCP and UDP Port 88
  • TCP and UDP Port 135
  • TCP and UDP Port 389
  • TCP and UDP Port 445

2.    Confirm that the AWS Managed Microsoft AD security group is configured to allow the correct inbound traffic. A security group is created when you create an AWS Managed Microsoft AD. For the list of inbound and outbound rules added to this security group, see What gets created in the AWS Directory Service Administration Guide.

3.    You might have your DB instance and the AWS Managed Microsoft AD in different VPCs or in different accounts. If so, make sure that there is a correct route for the DB instance to reach the AD. Also, make sure that there is a correct route the AD to reach the DB instance. For more information. see RDS Support for cross-account and cross-VPC domain joins (video).

After identifying and addressing potential causes for the domain join failure, do the following to unjoin and join the domain to the DB instance:

1.    Open the Amazon RDS console, and then choose Databases from the navigation pane.

2.    Select the DB instance that failed to join the domain, and then choose Modify.

3.    From the Microsoft SQL Server Windows Authentication section, for Directory, choose None.

4.    Choose Apply immediately. After the modification is complete, the DB instance reboots automatically.

5.    To rejoin the directory, choose Databases from the navigation pane.

6.    Select the DB instance, and choose Modify.

7.    From the Microsoft SQL Server Windows Authentication section, for Directory, choose your directory from the list.

8.    Choose Apply immediately. After the modification is complete, the DB instance reboots again.

An error occurred (InvalidParameterCombination) when calling the ModifyDBInstance operation: IAM role provided is not valid, please check that the role exists and has the correct policies

When using the AWS CLI to attach a Directory Service to your DB instance, use the default IAM role rds-directoryservice-access-role. If you use a custom role, then attach the default policy AmazonRDSDirectoryServiceAccess to the custom role. Doing this resolves the IAM role provided is not valid error.

Unable to log into the DB instance using Windows Authentication

Logging in using Windows Authentication requires a SQL login on the instance for the AD user or group using the DB instance's primary user credentials. If you use groups or users in your on-premises AD, you must create a trust relationship.

1.    Log in to your DB instance as the primary user using SQL Server Management Studio (SSMS).

2.    Use T-SQL to create the Windows Authentication login:

CREATE LOGIN [Domain Name\user or group] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english];

Note: Creating a Windows Authentication login on an RDS for SQL Server instance is supported by using T-SQL, only. You can't use the GUI to create a login on SQL Server Management studio.

3.    Connect to the DB instance using Windows Authentication.