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

Short description

When creating an Amazon RDS for SQL Server DB instance, you might encounter one of the following issues:

• The Microsoft Managed AD 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 in to the DB instance using Windows Authentication.

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 Active Directory 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.

The AWS Managed Microsoft AD isn't listed or is unavailable when creating a DB instance

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

When the AWS Managed Microsoft AD is in a different Region than the instance, then that directory isn't listed when creating or modifying 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 Microsoft Managed 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. Verify 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:

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

Or, the Directory status might appear as 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
• TCP and UDP Port 464
• TCP Port 636
• TCP Port 3268
• TCP Port 3269
• TCP Port 9389
• TCP Ports 49152-65535
• UDP Port 123
• UDP Port 138

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 AWS Managed Microsoft AD. Also, make sure that there is a correct route for the Microsoft Managed 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 then 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 in to the DB instance using Windows Authentication

Logging in using Windows Authentication requires a SQL login on the instance for the AWS Managed Microsoft AD user or group. The SQL login uses the DB instance's primary user credentials. If you use groups or users in your on-premises Microsoft Active Directory, 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 only when using T-SQL. You can't use the GUI to create a login in SQL Server Management studio.

3.    Connect to the DB instance using Windows Authentication.

Related information

Using Windows Authentication with an Amazon RDS for SQL Server DB instance

Controlling access with security groups

Can't connect to an Amazon RDS DB instance

Joining your Amazon RDS DB Instances across accounts to a single shared domain

Migrating Microsoft SQL Server databases to the AWS Cloud