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

Last updated: 2019-06-18

I have AWS Directory Service for Microsoft Active Directory configured for my AWS account. But when I try to create an Amazon Relational Database Service (Amazon RDS) 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"
  • 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 instances that run SQL Server is supported only in an Amazon Virtual Private Cloud (Amazon VPC). For this reason, the directory must be in the same AWS Region and in the same VPC as the DB instances. Even if there is VPC peering between two VPCs in different AWS Regions, the directory isn't be listed in the Amazon RDS console.

Resolution

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

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

If the VPC and the directory are in different AWS Regions than the DB instance is in, you won'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 and the same VPC as your directory.

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

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

3.    From the Connectivity & security tab, review the VPC associated to your DB instance.

4.    Confirm that the directory is in the same AWS Region and in the same VPC as the DB instance.

5.    Open the Directory Service console.

6.    Choose Directories from the navigation pane, and then choose the directory that you created.

7.    From the Directory details tab, review the VPC information. Confirm that the information matches the DB instance.

Then, the directory is listed in the Microsoft SQL Server Windows Authentication list when you create a DB instance.

If your directory is in the same AWS Region and VPC as your DB instance, and you still don't see the option to add the directory, your instance might not be in a supported Region. For more information, see Using Windows Authentication with a Microsoft SQL Server DB Instance.

Error received when joining a DB instance to a domain

When joining an 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."

To resolve this error, 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.

Unable to log in to the DB instance using Windows Authentication

To log in using Windows Authentication, you must create a SQL login on the DB instance for the Active Directory User or Group by using the RDS DB instance master 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 Amazon RDS SQL Server DB instance using the master 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 RDS SQL Server is supported by using T-SQL only. Using the graphical interface to create a login on SQL Server Management studio or similar isn't supported.

3.    Connect to the DB instance using Windows Authentication.


Did this article help you?

Anything we could improve?


Need more help?