How do I troubleshoot using my on-premises Active Directory login to my RDS for SQL Server instance?

Short description

When setting up Windows Authentication with Amazon RDS, a forest trust must be created. This is created with the AWS Directory Service for Microsoft Active Directory (AWS Managed Microsoft AD). A forest trust is set up whether you are using an on-premises or self-hosted AWS Managed Microsoft AD. While using an on-premises login after configuring the trust relationship, the following login error might appear for multiple reasons:

"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 indicates the status of the domain membership for the DB instance. For more information on DB instance status', see Understanding Domain membership. If you receive a "Failed" error when joining a DB instance to a domain or directory status on the RDS console, see rejoining a DB instance. If you receive an AWS Identity and Access Management (IAM) error, this might be due to not using the default IAM role rds-directoryservice-access-role. If you're using a custom IAM role, then attach the default policy AmazonRDSDirectoryServiceAccess to resolve the error.

Trust relationship

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

Forest-wide and selective authentications

While creating a forest trust using AWS Directory Service console, you have an option to turn on "selective authentication". If this option isn't turned on, then the authentication is treated as "forest-wide authentication".

Forest-wide authentication

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

There is a risk in this approach. After the foreign user (from trusted forest) us 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 where the group "Authenticated user" has access.

Selective authentication

To have control of the authentication, you can opt for 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 is coming 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 is holding 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 AD. The "AWS Delegated Allowed to Authenticate Objects" is assigned the "Allowed to Authenticate" permission. All users that are part of this group can access the RDS instance. Users that are not part of this group can't access the Amazon RDS SQL Server.

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

Login and password status

On-premises Active Directory login passwords and statuses can't be expired or locked. If so, check the login status using the following command:

net user username/domain

You only need to change the username to the user you want to check the status of. Leave the domain as it is.

Duplicate Service Principal Names (SPN)

By default, Amazon RDS creates an SPN as required. Creating additional SPN's for the on-premises Active Directory login for other uses might cause a login failure. For more information, see Identify, remove, and verify an SPN.

Security patches

If you are seeing an on-premises Active Directory login error and have 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 there are any issues due to security patches or KBs, you might have to roll back the updates. If rolling back the updates doesn't fix the issue, then try to apply the fix from Microsoft, if available.

---

Related information

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