Step 1: Create an Organizational Unit in your AD Step 2: Create an AD domain user in your AD.Step 3: Delegate control to the AD user.Step 3: Create an AWS KMS key.Step 3: Create an AWS secret.Step 6: Create or modify a SQL Server DB instance Step 7: Create Windows Authentication SQL Server logins

Setting up Self Managed Active Directory

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

Topics

Step 1: Create an Organizational Unit in your AD
Step 2: Create an AD domain user in your AD
Step 3: Delegate control to the AD user
Step 4: Create an AWS KMS key
Step 5: Create an AWS secret
Step 6: Create or modify a SQL Server DB instance
Step 7: Create Windows Authentication SQL Server logins

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 AWS 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

Connect to your AD domain as a domain administrator.
Open Active Directory Users and Computers and select the domain where you want to create your OU.
Right-click the domain and choose New, then Organizational Unit.
Enter a name for the OU.
Keep the box selected for Protect container from accidental deletion.
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 AWS Secrets Manager.

To create an AD domain user in your AD

Open Active Directory Users and Computers and select the domain and OU where you want to create your user.
Right-click the Users object and choose New, then User.
Enter a first name, last name, and logon name for the user. Click Next.
Enter a password for the user. Don't select "User must change password at next login". Don't select "Account is disabled". Click Next.
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

Open Active Directory Users and Computers MMC snap-in and select the domain where you want to create your user.
Right-click the OU that you created earlier and choose Delegate Control.
On the Delegation of Control Wizard, click Next.
On the Users or Groups section, click Add.
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.
On the Users or Groups section, confirm your AD user was added and click Next.
On the Tasks to Delegate section, choose Create a custom task to delegate and click Next.
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.
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.
For Completing the Delegation of Control Wizard, review and confirm your settings and click Finish.

Step 4: Create an AWS KMS key

The KMS key is used to encrypt your AWS secret.

To create an AWS KMS key

Note

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

In the AWS KMS console, choose Create key.
For Key Type, choose Symmetric.
For Key Usage, choose Encrypt and decrypt.
For Advanced options:
1. For Key material origin, choose KMS.
2. For Regionality, choose Single-Region key and click Next.
For Alias, provide a name for the KMS key.
(Optional) For Description, provide a description of the KMS key.
(Optional) For Tags, provide a tag the KMS key and click Next.
For Key administrators, provide the name of an IAM user and select it.
For Key deletion, keep the box selected for Allow key administrators to delete this key and click Next.
For Key users, provide the same IAM user from the previous step and select it. Click Next.
Review the configuration.

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": "*"
}

Click Finish.

Step 5: Create an AWS secret

To create a secret

Note

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

In AWS Secrets Manager, choose Store a new secret.
For Secret type, choose Other type of secret.
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.
For Encryption key, enter the KMS key that you created in a previous step and click Next.
For Secret name, enter a descriptive name that helps you find your secret later.
(Optional) For Description, enter a description for the secret name.
For Resource permission, click Edit.

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 AWS 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:*"
                }
            }
        }
    ]
}

Click Save then click Next.
For Configure rotation settings, keep the default values and choose Next.
Review the settings for the secret and click Store.
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:

Create a new SQL Server DB instance using the console, the create-db-instance CLI command, or the CreateDBInstance RDS API operation.

For instructions, see Creating an Amazon RDS DB instance.
Modify an existing SQL Server DB instance using the console, the modify-db-instance CLI command, or the ModifyDBInstance RDS API operation.

For instructions, see Modifying an Amazon RDS DB instance.
Restore a SQL Server DB instance from a DB snapshot using the console, the restore-db-instance-from-db-snapshot CLI command, or the RestoreDBInstanceFromDBSnapshot RDS API operation.

For instructions, see Restoring to a DB instance.
Restore a SQL Server DB instance to a point-in-time using the console, the restore-db-instance-to-point-in-time CLI command, or the RestoreDBInstanceToPointInTime RDS API operation.

For instructions, see Restoring a DB instance to a specified time for Amazon RDS.

When you use the AWS 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 Using the 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.

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Requirements

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