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

Short description

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

• Microsoft Managed AD is unavailable.
• You receive the Failed to join a host to a domain error message, or the directory status on the Amazon RDS console shows Failed.
• You can't use Windows Authentication to log in to the DB instance.

You can use Windows Authentication for Amazon RDS for SQL Server DB instances across multiple AWS accounts and Amazon Virtual Private Clouds (Amazon VPCs). You can also share an AWS Managed Microsoft AD directory across multiple accounts and VPCs to manage directory-aware database workloads. However, the RDS for SQL Server DB instances must be in the same AWS Region as the AWS Managed Microsoft AD directory.

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

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

Important: To list the AWS Managed Microsoft AD on the Amazon RDS console, the managed domain type must be AWS Managed active directory.

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

Complete the following steps:

1. Open the Amazon RDS console.
2. In the navigation pane, choose Databases.
3. Select your DB instance.
4. In the Summary section, note the Region that your DB instance is in.
5. Use the AWS Directory Service console to confirm that the directory service is in the same Region as the DB instance.

If your AWS Managed Microsoft AD is in a different account than the DB instance, then share the Microsoft Managed AD with the AWS account. Then, list the directory service when you create or modify the DB instance.

Complete the following steps:

1. Share 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. Use the account for the DB instance to open the AWS Directory Service console.
3. Verify that the domain is in the SHARED status.
4. Use the Directory ID value to join the DB instance to the domain.

You receive an error or the Directory Status shows "Failed" when you join a DB instance to a domain

When you join 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.

To troubleshoot the domain join failure, complete the following steps:

1. Confirm that you configured the RDS for SQL Server instance security group to allow the following 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.
   Note: AWS Directory service creates a security group when you create an AWS Managed Microsoft AD. For the list of inbound and outbound rules that's added to the security group, see What gets created with your AWS Managed Microsoft AD.
3. Check whether your DB instance and the AWS Managed Microsoft AD are in different VPCs or accounts.
   Note: If so, then make sure that there's a correct route to connect the DB instance to the AWS Managed Microsoft AD. Also, make sure that there's 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 join.

After you identify and address potential causes for the domain join failure, complete the following steps to rejoin the domain to the DB instance:

1. Open the Amazon RDS console.
2. In the navigation pane, choose Databases.
3. Select the DB instance that failed to join the domain, and then choose Modify.
4. In the Microsoft SQL Server Windows Authentication section, for Directory, choose None.
5. Choose Apply immediately.
   Note: After the modification is complete, the DB instance automatically reboots.
6. In the navigation pane, choose Databases.
7. Select the DB instance, and then choose Modify.
8. In the Microsoft SQL Server Windows Authentication section, for Directory, select your directory.
9. Choose Apply immediately.
   Note: After the modification is complete, the DB instance reboots again.

An InvalidParameterCombination error occurs when you call the ModifyDBInstance operation

If you receive the following error message: "IAM role provided is not valid, check that the role exists and has the correct policies", then take the following actions:

• Use the default rds-directoryservice-access-role AWS Identity and Access Management (IAM) role when you use the AWS CLI to attach a directory service to your DB instance.
• If you use a custom role, then attach the AmazonRDSDirectoryServiceAccess default policy to the custom role.

Couldn't use Windows Authentication to log in to the DB instance

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, then you must create a trust relationship.

To create a trust relationship, complete the following steps:

1. Use SQL Server Management Studio (SSMS) to log in to your DB instance as the primary user.
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: When you create a Windows Authentication login on an RDS for SQL Server instance, you must use T-SQL. You can't use the GUI to create a login in SQL SSMS.
3. Use Windows Authentication to connect to the DB instance.

Related information

Working with AWS Managed Active Directory with RDS for SQL Server

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