Working with Self Managed Active Directory with an Amazon RDS for SQL Server DB instance - Amazon Relational Database Service
Region and version availabilityRequirementsLimitationsOverview of setting up Self Managed Active DirectorySetting up Self Managed Active DirectoryManaging a DB instance in a self-managed Active Directory DomainUnderstanding self-managed Active Directory Domain membershipTroubleshooting self-managed Active DirectoryRestoring DB instance and adding it to a self-managed Active Directory domain
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Working with Self Managed Active Directory with an Amazon RDS for SQL Server DB instance

You can join your RDS for SQL Server DB instances directly to your self-managed Active Directory (AD) domain, regardless of where your AD is hosted: in corporate data centers, on Amazon EC2, or with other cloud providers. With self-managed AD, you use NTLM authentication to directly control authentication of users and services on your RDS for SQL Server DB instances without using intermediary domains and forest trusts. When users authenticate with an RDS for SQL Server DB instance joined to your self-managed AD domain, authentication requests are forwarded to a self-managed AD domain that you specify.

Region and version availability

Amazon RDS supports Self Managed AD for SQL Server using NTLM in all Amazon Web Services Regions.

Requirements

Make sure you've met the following requirements before joining an RDS for SQL Server DB instance to your self-managed AD domain.

Configure your on-premises AD

Make sure that you have an on-premises or other self-managed Microsoft AD that you can join the Amazon RDS for SQL Server instance to. Your on-premises AD should have the following configuration:

  • If you have Active Directory sites defined, make sure the subnets in the VPC associated with your RDS for SQL Server DB instance are defined in your Active Directory site. Confirm there aren't any conflicts between the subnets in your VPC and the subnets in your other AD sites.

  • Your AD domain controller has a domain functional level of Windows Server 2008 R2 or higher.

  • Your AD domain name can't be in Single Label Domain (SLD) format. RDS for SQL Server does not support SLD domains.

  • The fully qualified domain name (FQDN) for your AD can't exceed 64 characters.

Configure your network connectivity

Make sure that you have met the following network configurations:

  • Connectivity configured between the Amazon VPC where you want to create the RDS for SQL Server DB instance and your self-managed Active Directory. You can set up connectivity using Amazon Direct Connect, Amazon VPN, VPC peering, or Amazon Transit Gateway.

  • For VPC security groups, the default security group for your default Amazon VPC is already added to your RDS for SQL Server DB instance in the console. Ensure that the security group and the VPC network ACLs for the subnet(s) where you're creating your RDS for SQL Server DB instance allow traffic on the ports and in the directions shown in the following diagram.

    Self Managed Active Directory network configuration port rules.

    The following table identifies the role of each port.

    Protocol Ports Role
    TCP/UDP 53 Domain Name System (DNS)
    TCP/UDP 88 Kerberos authentication
    TCP/UDP 464 Change/Set password
    TCP/UDP 389 Lightweight Directory Access Protocol (LDAP)
    TCP 135 Distributed Computing Environment / End Point Mapper (DCE / EPMAP)
    TCP 445 Directory Services SMB file sharing
    TCP 636 Lightweight Directory Access Protocol over TLS/SSL (LDAPS)
    TCP 49152 - 65535 Ephemeral ports for RPC

  • Generally, the domain DNS servers are located in the AD domain controllers. You do not need to configure the VPC DHCP option set to use this feature. For more information, see DHCP option sets in the Amazon VPC User Guide.

Important

If you're using VPC network ACLs, you must also allow outbound traffic on dynamic ports (49152-65535) from your RDS for SQL Server DB instance. Ensure that these traffic rules are also mirrored on the firewalls that apply to each of the AD domain controllers, DNS servers, and RDS for SQL Server DB instances.

While VPC security groups require ports to be opened only in the direction that network traffic is initiated, most Windows firewalls and VPC network ACLs require ports to be open in both directions.

Configure your AD domain service account

Make sure that you have met the following requirements for an AD domain service account:

  • Make sure that you have a service account in your self-managed AD domain with delegated permissions to join computers to the domain. A domain service account is a user account in your self-managed AD that has been delegated permission to perform certain tasks.

  • The domain service account needs to be delegated the following permissions in the Organizational Unit (OU) that you're joining your RDS for SQL Server DB instance to:

    • Validated ability to write to the DNS host name

    • Validated ability to write to the service principal name

    • Create and delete computer objects

    These represent the minimum set of permissions that are required to join computer objects to your self-managed Active Directory. For more information, see Errors when attempting to join computers to a domain in the Microsoft Windows Server documentation.

Important

Do not move computer objects that RDS for SQL Server creates in the Organizational Unit after your DB instance is created. Moving the associated objects will cause your RDS for SQL Server DB instance to become misconfigured. If you need to move the computer objects created by Amazon RDS, use the ModifyDBInstance RDS API operation to modify the domain parameters with the desired location of the computer objects.

Limitations

The following limitations apply for Self Managed AD for SQL Server.

  • NTLM is the only supported authentication type. Kerberos authentication is not supported. If you need to use kerberos authentication, you can use Amazon Managed AD instead of self-managed AD.

  • The Microsoft Distributed Transaction Coordinator (MSDTC) service isn't supported, as it requires Kerberos authentication.

  • Your RDS for SQL Server DB instances do not use the Network Time Protocol (NTP) server of your self-managed AD domain. They use an Amazon NTP service instead.

  • SQL Server linked servers must use SQL authentication to connect to other RDS for SQL Server DB instances joined to your self-managed AD domain.

  • Microsoft Group Policy Object (GPO) settings from your self-managed AD domain are not applied to RDS for SQL Server DB instances.

Overview of setting up Self Managed Active Directory

To set up self-managed AD for an RDS for SQL Server DB instance, take the following steps, explained in greater detail in Setting up Self Managed Active Directory:

In your AD domain:

  • Create an Organizational Unit (OU).

  • Create an AD domain user.

  • Delegate control to the AD domain user.

From the Amazon Web Services Management Console or API:

  • Create a Amazon KMS key.

  • Create a secret using Amazon Secrets Manager.

  • Create or modify an RDS for SQL Server DB instance and join it to your self-managed AD domain.

Setting up Self Managed Active Directory

To set up Self Managed AD, take the following steps.

Step 1: Create an Organizational Unit in your AD

Important

We recommend creating a dedicated OU and service credential scoped to that OU for any Amazon account that owns an RDS for SQL Server DB instance joined your self-managed AD domain. By dedicating an OU and service credential, you can avoid conflicting permissions and follow the principal of least privilege.

To create an OU in your AD

  1. Connect to your AD domain as a domain administrator.

  2. Open Active Directory Users and Computers and select the domain where you want to create your OU.

  3. Right-click the domain and choose New, then Organizational Unit.

  4. Enter a name for the OU.

  5. Keep the box selected for Protect container from accidental deletion.

  6. Click OK. Your new OU will appear under your domain.

Step 2: Create an AD domain user in your AD

The domain user credentials will be used for the secret in Amazon Secrets Manager.

To create an AD domain user in your AD

  1. Open Active Directory Users and Computers and select the domain and OU where you want to create your user.

  2. Right-click the Users object and choose New, then User.

  3. Enter a first name, last name, and logon name for the user. Click Next.

  4. Enter a password for the user. Don't select "User must change password at next login". Don't select "Account is disabled". Click Next.

  5. Click OK. Your new user will appear under your domain.

Step 3: Delegate control to the AD user

To delegate control to the AD domain user in your domain

  1. Open Active Directory Users and Computers MMC snap-in and select the domain where you want to create your user.

  2. Right-click the OU that you created earlier and choose Delegate Control.

  3. On the Delegation of Control Wizard, click Next.

  4. On the Users or Groups section, click Add.

  5. On the Select Users, Computers, or Groups section, enter the AD user you created and click Check Names. If your AD user check is successful, click OK.

  6. On the Users or Groups section, confirm your AD user was added and click Next.

  7. On the Tasks to Delegate section, choose Create a custom task to delegate and click Next.

  8. On the Active Directory Object Type section:

    1. Choose Only the following objects in the folder.

    2. Select Computer Objects.

    3. Select Create selected objects in this folder.

    4. Select Delete selected objects in this folder and click Next.

  9. On the Permissions section:

    1. Keep General selected.

    2. Select Validated write to DNS host name.

    3. Select Validated write to service principal name and click Next.

  10. For Completing the Delegation of Control Wizard, review and confirm your settings and click Finish.

Step 4: Create an Amazon KMS key

The KMS key is used to encrypt your Amazon secret.

To create an Amazon KMS key
Note

For Encryption Key, don't use the Amazon default KMS key. Be sure to create the Amazon KMS key in the same Amazon account that contains the RDS for SQL Server DB instance that you want to join to your self-managed AD.

  1. In the Amazon KMS console, choose Create key.

  2. For Key Type, choose Symmetric.

  3. For Key Usage, choose Encrypt and decrypt.

  4. For Advanced options:

    1. For Key material origin, choose KMS.

    2. For Regionality, choose Single-Region key and click Next.

  5. For Alias, provide a name for the KMS key.

  6. (Optional) For Description, provide a description of the KMS key.

  7. (Optional) For Tags, provide a tag the KMS key and click Next.

  8. For Key administrators, provide the name of an IAM user and select it.

  9. For Key deletion, keep the box selected for Allow key administrators to delete this key and click Next.

  10. For Key users, provide the same IAM user from the previous step and select it. Click Next.

  11. Review the configuration.

  12. For Key policy, include the following to the policy Statement:

    {
    "Sid": "Allow use of the KMS key on behalf of RDS",
    "Effect": "Allow",
    "Principal": {
        "Service": [
            "rds.amazonaws.com"
        ]
    },
    "Action": "kms:Decrypt",
    "Resource": "*"
}

  13. Click Finish.

Step 5: Create an Amazon secret

To create a secret
Note

Be sure to create the secret in the same Amazon account that contains the RDS for SQL Server DB instance that you want to join to your self-managed AD.

  1. In Amazon Secrets Manager, choose Store a new secret.

  2. For Secret type, choose Other type of secret.

  3. For Key/value pairs, add your two keys:

    1. For the first key, enter CUSTOMER_MANAGED_ACTIVE_DIRECTORY_USERNAME.

    2. For the value of the first key, enter the name of the AD user that you created on your domain in a previous step.

    3. For the second key, enter CUSTOMER_MANAGED_ACTIVE_DIRECTORY_PASSWORD.

    4. For the value of the second key, enter the password that you created for the AD user on your domain.

  4. For Encryption key, enter the KMS key that you created in a previous step and click Next.

  5. For Secret name, enter a descriptive name that helps you find your secret later.

  6. (Optional) For Description, enter a description for the secret name.

  7. For Resource permission, click Edit.

  8. Add the following policy to the permission policy:

    Note

    We recommend that you use the aws:sourceAccount and aws:sourceArn conditions in the policy to avoid the confused deputy problem. Use your Amazon Web Services account for aws:sourceAccount and the RDS for SQL Server DB instance ARN for aws:sourceArn. For more information, see Preventing cross-service confused deputy problems.

    {
    "Version": "2012-10-17",
    "Statement":
    [
        {
            "Effect": "Allow",
            "Principal":
            {
                "Service": "rds.amazonaws.com"
            },
            "Action": "secretsmanager:GetSecretValue",
            "Resource": "*",
            "Condition":
            {
                "StringEquals":
                {
                    "aws:sourceAccount": "123456789012"
                },
                "ArnLike":
                {
                    "aws:sourceArn": "arn:aws:rds:us-west-2:123456789012:db:*"
                }
            }
        }
    ]
}

  9. Click Save then click Next.

  10. For Configure rotation settings, keep the default values and choose Next.

  11. Review the settings for the secret and click Store.

  12. Choose the secret you created and copy the value for the Secret ARN. This will be used in the next step to set up self-managed Active Directory.

Step 6: Create or modify a SQL Server DB instance

You can use the console, CLI, or RDS API to associate an RDS for SQL Server DB instance with a self-managed AD domain. You can do this in one of the following ways:

When you use the Amazon CLI, the following parameters are required for the DB instance to be able to use the self-managed Active Directory domain that you created:

  • For the --domain-fqdn parameter, use the fully qualified domain name (FQDN) of your self-managed Active Directory.

  • For the --domain-ou parameter, use the OU that you created in your self-managed AD.

  • For the --domain-auth-secret-arn parameter, use the value of the Secret ARN that you created in a previous step.

  • For the --domain-dns-ips parameter, use the primary and secondary IPv4 addresses of the DNS servers for your self-managed AD. If you don't have a secondary DNS server IP address, enter the primary IP address twice.

The following example CLI commands show how to create, modify, and remove an RDS for SQL Server DB instance with a self-managed AD domain.

Important

If you modify a DB instance to join it to or remove it from a self-managed AD domain, a reboot of the DB instance is required for the modification to take effect. You can choose to apply the changes immediately or wait until the next maintenance window. Choosing the Apply Immediately option will cause downtime for a single-AZ DB instance. A multi-AZ DB instance will perform a failover before completing a reboot. For more information, see Schedule modifications setting.

The following CLI command creates a new RDS for SQL Server DB instance and joins it to a self-managed AD domain.

For Linux, macOS, or Unix:

aws rds create-db-instance \
    --db-instance-identifier my-DB-instance \
    --db-instance-class db.m5.xlarge \
    --allocated-storage 50 \
    --engine sqlserver-se \
    --engine-version 15.00.4043.16.v1 \
    --license-model license-included \
    --master-username my-master-username \
    --master-user-password my-master-password \
    --domain-fqdn my_AD_domain.my_AD.my_domain \
    --domain-ou OU=my-AD-test-OU,DC=my-AD-test,DC=my-AD,DC=my-domain \
    --domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:my-AD-test-secret-123456" \
    --domain-dns-ips "10.11.12.13" "10.11.12.14"

For Windows:

aws rds create-db-instance ^
    --db-instance-identifier my-DB-instance ^
    --db-instance-class db.m5.xlarge ^
    --allocated-storage 50 ^
    --engine sqlserver-se ^
    --engine-version 15.00.4043.16.v1 ^
    --license-model license-included ^
    --master-username my-master-username ^
    --master-user-password my-master-password ^
    --domain-fqdn my-AD-test.my-AD.mydomain ^
    --domain-ou OU=my-AD-test-OU,DC=my-AD-test,DC=my-AD,DC=my-domain ^
    --domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:my-AD-test-secret-123456" \ ^
    --domain-dns-ips "10.11.12.13" "10.11.12.14"

The following CLI command modifies an existing RDS for SQL Server DB instance to use a self-managed Active Directory domain.

For Linux, macOS, or Unix:

aws rds modify-db-instance \
    --db-instance-identifier my-DB-instance \
    --domain-fqdn my_AD_domain.my_AD.my_domain \
    --domain-ou OU=my-AD-test-OU,DC=my-AD-test,DC=my-AD,DC=my-domain \
    --domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:my-AD-test-secret-123456" \ 
    --domain-dns-ips "10.11.12.13" "10.11.12.14"

For Windows:

aws rds modify-db-instance ^
    --db-instance-identifier my-DBinstance ^
    --domain-fqdn my_AD_domain.my_AD.my_domain ^
    --domain-ou OU=my-AD-test-OU,DC=my-AD-test,DC=my-AD,DC=my-domain ^
    --domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:my-AD-test-secret-123456" ^ 
    --domain-dns-ips "10.11.12.13" "10.11.12.14"

The following CLI command removes an RDS for SQL Server DB instance from a self-managed Active Directory domain.

For Linux, macOS, or Unix:

aws rds modify-db-instance \
    --db-instance-identifier my-DB-instance \
    --disable-domain

For Windows:

aws rds modify-db-instance ^
    --db-instance-identifier my-DB-instance ^
    --disable-domain

Step 7: Create Windows Authentication SQL Server logins

Use the Amazon RDS master user credentials to connect to the SQL Server DB instance as you do for any other DB instance. Because the DB instance is joined to the self-managed AD domain, you can provision SQL Server logins and users. You do this from the AD users and groups utility in your self-managed AD domain. Database permissions are managed through standard SQL Server permissions granted and revoked to these Windows logins.

In order for a self-managed AD user to authenticate with SQL Server, a SQL Server Windows login must exist for the self-managed AD user or a self-managed Active Directory group that the user is a member of. Fine-grained access control is handled through granting and revoking permissions on these SQL Server logins. A self-managed AD user that doesn't have a SQL Server login or belong to a self-managed AD group with such a login can't access the SQL Server DB instance.

The ALTER ANY LOGIN permission is required to create a self-managed AD SQL Server login. If you haven't created any logins with this permission, connect as the DB instance's master user using SQL Server Authentication and create your self-managed AD SQL Server logins under the context of the master user.

You can run a data definition language (DDL) command such as the following to create a SQL Server login for an self-managed AD user or group.

Note

Specify users and groups using the pre-Windows 2000 login name in the format my_AD_domain\my_AD_domain_user. You can't use a user principal name (UPN) in the format my_AD_domain_user@my_AD_domain.

USE [master]
GO
CREATE LOGIN [my_AD_domain\my_AD_domain_user] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english];
GO

For more information, see CREATE LOGIN (Transact-SQL) in the Microsoft Developer Network documentation.

Users (both humans and applications) from your domain can now connect to the RDS for SQL Server instance from a self-managed AD domain-joined client machine using Windows authentication.

Managing a DB instance in a self-managed Active Directory Domain

You can use the console, Amazon CLI, or the Amazon RDS API to manage your DB instance and its relationship with your self-managed AD domain. For example, you can move the DB instance into, out of, or between domains.

For example, using the Amazon RDS API, you can do the following:

  • To reattempt a self-managed domain join for a failed membership, use the ModifyDBInstance API operation and specify the same set of parameters:

    • --domain-fqdn

    • --domain-dns-ips

    • --domain-ou

    • --domain-auth-secret-arn

  • To remove a DB instance from a self-managed domain, use the ModifyDBInstance API operation and specify --disable-domain for the domain parameter.

  • To move a DB instance from one self-managed domain to another, use the ModifyDBInstance API operation and specify the domain parameters for the new domain:

    • --domain-fqdn

    • --domain-dns-ips

    • --domain-ou

    • --domain-auth-secret-arn

  • To list self-managed AD domain membership for each DB instance, use the DescribeDBInstances API operation.

Understanding self-managed Active Directory Domain membership

After you create or modify your DB instance, the instance becomes a member of the self-managed AD domain. The Amazon console indicates the status of the self-managed Active Directory domain membership for the DB instance. The status of the DB instance can be one of the following:

  • joined – The instance is a member of the AD domain.

  • joining – The instance is in the process of becoming a member of the AD domain.

  • pending-join – The instance membership is pending.

  • pending-maintenance-join – Amazon will attempt to make the instance a member of the AD domain during the next scheduled maintenance window.

  • pending-removal – The removal of the instance from the AD domain is pending.

  • pending-maintenance-removal – Amazon will attempt to remove the instance from the AD domain during the next scheduled maintenance window.

  • failed – A configuration problem has prevented the instance from joining the AD domain. Check and fix your configuration before reissuing the instance modify command.

  • removing – The instance is being removed from the self-managed AD domain.

A request to become a member of a self-managed AD domain can fail because of a network connectivity issue. For example, you might create a DB instance or modify an existing instance and have the attempt fail for the DB instance to become a member of a self-managed AD domain. In this case, either reissue the command to create or modify the DB instance or modify the newly created instance to join the self-managed AD domain.

Troubleshooting self-managed Active Directory

The following are issues you might encounter when you set up or modify self-managed AD.

Error Code Description Common causes Troubleshooting suggestions

Error 2 / 0x2

The system cannot find the file specified.

The format or location for the Organizational Unit (OU) specified with the —domain-ou parameter is invalid. The domain service account specified via Amazon Secrets Manager lack the permissions required to join the OU.

Review the —domain-ou parameter. Ensure the domain service account has the correct permissions to the OU. For more information, see Configure your AD domain service account.

Error 5 / 0x5

Access is denied.

Misconfigured permissions for the domain service account, or the computer account already exists in the domain.

Review the domain service account permissions in the domain, and verify that the RDS computer account is not duplicated in the domain. You can verify the name of the RDS computer account by running SELECT @@SERVERNAME on your RDS for SQL Server DB instance. If you are using Multi-AZ, try rebooting with failover and then verify that the RDS computer account again. For more information, see Rebooting a DB instance.

Error 87 / 0x57

The parameter is incorrect.

The domain service account specified via Amazon Secrets Manager doesn't have the correct permissions. The user profile may also be corrupted.

Review the requirements for the domain service account. For more information, see Configure your AD domain service account.

Error 234 / 0xEA

Specified Organizational Unit (OU) does not exist.

The OU specified with the —domain-ou parameter doesn't exist in your self-managed AD.

Review the —domain-ou parameter and ensure the specified OU exists in your self-managed AD.

Error 1326 / 0x52E

The user name or password is incorrect.

The domain service account credentials provided in Amazon Secrets Manager contains an unknown username or bad password. The domain account may also be disabled in your self-managed AD.

Ensure the credentials provided in Amazon Secrets Manager are correct and the domain account is enabled in your self-managed Active Directory.

Error 1355 / 0x54B

The specified domain either does not exist or could not be contacted.

The domain is down, the specified set of DNS IPs are unreachable, or the specified FQDN is unreachable.

Review the —domain-dns-ips and —domain-fqdn parameters to ensure they're correct. Review the networking configuration of your RDS for SQL Server DB instance and ensure your self-managed AD is reachable. For more information, see Configure your network connectivity.

Error 1722 / 0x6BA

The RPC server is unavailable.

There was an issue reaching the RPC service of your AD domain. This might be a service or network issue.

Validate that the RPC service is running on your domain controllers and that the TCP ports 135 and 49152-65535 are reachable on your domain from your RDS for SQL Server DB instance.

Error 2224 / 0x8B0

The user account already exists.

The computer account that's attempting to be added to your self-managed AD already exists.

Identify the computer account by running SELECT @@SERVERNAME on your RDS for SQL Server DB instance and then carefully remove it from your self-managed AD.

Error 2242 / 0x8c2

The password of this user has expired.

The password for the domain service account specified via Amazon Secrets Manager has expired.

Update the password for the domain service account used to join your RDS for SQL Server DB instance to your self-managed AD.

Restoring a SQL Server DB instance and then adding it to a self-managed Active Directory domain

You can restore a DB snapshot or do point-in-time recovery (PITR) for a SQL Server DB instance and then add it to a self-managed Active Directory domain. Once the DB instance is restored, modify the instance using the process explained in Step 6: Create or modify a SQL Server DB instance to add the DB instance to a self-managed AD domain.