How do I troubleshoot the on-premises Active Directory "Login Failed" error for my Amazon RDS for SQL Server instance?

5 minute read
0

I'm unable to use my on-premises Active Directory login to access my Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server.

Short description

A forest trust must be created when you set up Windows Authentication with Amazon RDS. This forest trust is created with the AWS Directory Service for Microsoft Active Directory (AWS Managed Microsoft AD). A forest trust is set up whether you use on-premises or self-hosted AWS Managed Microsoft AD.

After you configure the trust relationship and try to use your on-premises credentials to log in, you might receive the following error:

"Login Failed. The Login is From an Untrusted Domain and Cannot be Used with Windows Authentication"

Resolution

To troubleshoot Active Directory login errors, check the following:

Amazon RDS domain status

After you create or modify your DB instance, the instance becomes a member of the domain. The RDS console shows the status of the domain membership for the DB instance. For more information on the DB instance status, see Understanding Domain membership.

If you receive a "Failed" error when you joined a DB instance to a domain, then additional troubleshooting might be required. For more information, see How do I troubleshoot RDS for SQL Server Windows Authentication issues with AWS Managed Microsoft AD?

If you use a custom AWS Identity and Access Management (IAM) role and not the default IAM role rds-directoryservice-access-role, then you might receive an IAM error. To resolve this error, you must attach the default policy AmazonRDSDirectoryServiceAccess to resolve the error.

Trust relationships

You can configure one-way and two-way external and forest trust relationships between your AWS Managed Microsoft AD and your on-premises directories. You can also configure one-way and two-way external and forest trust relationships between multiple AWS Managed Microsoft ADs in the AWS Cloud. AWS Managed Microsoft AD supports incoming, outgoing, and two-way trust relationship directions. To use an on-premises login to access the RDS console, make sure that the trust status is in the verified state. For more information, see Create, verify, or delete a trust relationship.

Forest-wide and selective authentications

You can turn on selective authentication when you use the AWS Directory Service console to create a forest trust. If this option isn't turned on, then authentication defaults to forest-wide authentication.

Forest-wide authentication

When forest-level authentication is turned on, the domain controllers of the forest authenticate all access requests that users of the trusted forest make. After authentication is successful, access to the resource is granted or rejected based on the resource Access Control List (ACL).

However, you take a risk when you use this approach. After the foreign user from the trusted forest is successfully authenticated, they become a member of the Authenticated User group. This group doesn't have any permanent members, and membership is computed dynamically based on authentication. After an account is a member of the Authenticated User group, that account can access all resources that the group Authenticated User has access to.

Selective authentication

To control authentication, you can use the selective authentication level. In this level, not all users are authenticated by domain controllers by default. Instead, when a domain controller detects that an authentication request comes from a trusted forest, the domain controller validates the user account. The domain controller validates that the user account was granted exclusive permission on the resource that holds the object.

When selective authentication is turned on, you must add the respective users and groups of the on-premises Active Directory. Users and groups must be added to the AWS Delegated Allowed to Authenticate Objects group of AWS Managed Microsoft AD. This group is assigned the Allowed to Authenticate permission. All users that are part of this group can access the RDS instance. Users that aren't a part of this group can't access the Amazon RDS for SQL Server.

Note: The AWS Delegated Allowed to Authenticate Objects group is created by default after AWS Managed Microsoft AD is configured. Members of this group are provided the ability to authenticate to resources in the AWS reserved organizational units (OU). This group is required only for on-premises objects with selective authentication trusts.

Login and password status

On-premises Active Directory login passwords and statuses can't expire or lock. To check the login status, use the following command:

net user username/domain

Note: Change username to the user that you want to check the login status for and leave domain unchanged.

Duplicate SPNs

By default, Amazon RDS creates a unique service principal name (SPN) as required. Login failures might occur for on-premises Active Directory users if duplicate SPNs are created. To identify and remove duplicate SPNs, see Remove the duplicate service principal name on the Microsoft website.

Security patches

If you get an on-premises Active Directory login error and you already verified the trust relationship, then check the latest security patches. Check the Distributed Control System (DCS) or Domain Name System (DNS) servers for known issues with Windows updates (KB). If you find issues that have security patches or KB entries, then you might have to roll back the updates. To search for known issues with Windows updates, see Microsoft Learn. Spark possibility. on the Microsoft website.

Related information

Everything you wanted to know about trusts with AWS Managed Microsoft AD