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

Last updated: 2021-02-10

I have AWS Directory Service for Microsoft Active Directory (Microsoft AD) configured for my AWS account. But when I try to create an Amazon Relational Database Service (Amazon RDS) DB instance that runs SQL Server, 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 Directory Status on the RDS Console shows “Failed”.
  • I can't log in to the DB instance using Windows Authentication

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

Short description

Windows Authentication for Amazon RDS DB instances that run SQL Server is now supported across multiple AWS accounts and 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 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 Directory Service is in a different AWS Region than the DB instance, then you don't see the directory 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 if the RDS DB instance and Directory Services 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 to your DB instance.
  4. Confirm that the Directory Service is in the same AWS Region as the DB instance by checking the Directory Service console.

If your AWS Managed Directory Service is in a different AWS Account than the DB instance, share the AWS Directory Service 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 the 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 may appear as Failed.

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

If your AWS Managed Directory Service is in same account and different VPC, or different AWS Account than the DB instance, then enable cross-VPC traffic between the AWS Managed Directory Service VPC and the DB instance VPC. To do this, use VPC-peering or AWS Transit Gateway. Make sure that the VPC route tables have the required destination routes to successfully enable cross-VPC traffic flow.

Confirm that the inbound and outbound rules on the security group are configured so that the DB instance can communicate with the Active Directory. Then, rejoin the DB instance to the domain by following these steps:

  1. Open the Amazon RDS console, and choose Databases from the navigation pane.
  2. Select the DB instance that failed to join the domain, and 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 are using a custom IAM Role, then attach the default policy AmazonRDSDirectoryServiceAccess to your custom IAM Role to resolve the “IAM role provided is not valid” error.

Unable to log in to the DB instance using Windows Authentication

To log in using Windows Authentication, create a SQL login on the DB instance for the Active Directory user or group using the DB instance primary user credentials. If you use groups or users in your on-premises Active Directory, you must create a trust relationship.

  1. Log in to your DB instance using the primary user by 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 SQL Server is supported by using T-SQL, only. You can't use the GUI to create a login on SQL Server Management studio.

  • Connect to the DB instance using Windows Authentication.