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:

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

You can use Windows Authentication for RDS for SQL Server DB instances across multiple AWS accounts and virtual private clouds (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.

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 Aurora and 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 Amazon RDS won't list the directory 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.

To check that your DB instance is in the same Region as your directory, complete the following steps:

1. Open the Aurora and 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 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 complete the following steps:

1. Share the directory with the AWS account that you want to create the DB instance in.
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 a "Failed to join a host to a domain. Domain membership status for instance XXXXXXX has been set to Failed" error message. Or, the Directory status might show as Failed.

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

1. Verify 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. Verify that you configured the AWS Managed Microsoft AD security group 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.

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 Aurora and 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: When the modification completes, 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: When the modification completes, the DB instance reboots again.

An InvalidParameterCombination error occurs when you call the ModifyDBInstance operation

When you call the ModifyDBInstance operation, you might receive the "IAM role provided is not valid, check that the role exists and has the correct policies" error message.

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. Run the following command to create the Windows Authentication login:

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

   Note: Replace Domain Name\user or group with your domain name and the user or group you want to use. When you create a Windows Authentication login on an RDS for SQL Server instance, you must use T-SQL. You can't use the graphic user interface (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

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