Configure Windows authentication for Amazon RDS for Microsoft SQL Server using AWS Managed Microsoft AD

From the AWS Management Console, navigate to AWS Directory Service.

Choose Set up directory.

Select AWS Managed Microsoft AD for the directory type.

Choose Create new AWS managed AD domain, and then choose Next.

Under Edition, select an edition that meets your requirements.

Under Directory DNS name, enter a fully qualified domain name (FQDN).

Under Admin password, set a password for the administrator account, and then choose Next.

Under Networking, select a target VPC (minimally, you must set up two subnets in separate AWS Availability Zones).

Under Network type, select IPv4 only.

Under Subnets, select two private subnets in separate AWS Availability Zones, and then choose Next.

Review the configuration values, and then choose Create directory.

Wait for the directory status to change to Active.

From the AWS Management Console, navigate to EC2.

Choose Launch instance.

Under Name and tags, enter a name and any applicable tags.

Choose an Amazon Machine Image (AMI) for Windows Server that meets your requirements.

Under Instance type, select an appropriately sized type.

Under Key pair (login), select an existing key pair or create a new one.

Under Network settings, select the same VPC that’s used for AWS Directory Service.

Choose a private subnet.

Under Firewall (security groups), create a group that allows RDP port 3389 and domain communications.

Expand the Advanced details section.

For Domain join directory, select the previously created AWS Managed Microsoft AD.

For IAM instance profile, select a role with the policies AmazonSSMManagedInstanceCore and AmazonSSMDirectoryServiceAccess.

Review all configuration values, and then choose Launch instance.

Navigate to the Aurora and RDS console, and choose Create a database.

Under Engine options, choose Microsoft SQL Server.

For Database management type, choose Amazon RDS.

For Edition, choose an SQL Server that meets your requirements.

For Engine version, choose the latest supported version.

In the Settings section under DB instance identifier, enter a unique name.

Under Master username, configure the administrator credentials.

Under Credentials management, choose Managed in AWS Secrets Manager or Self managed.

In the Storage section, under Storage type, choose a type that meets your requirements. We recommend either gp3, io1, or io2.

Set initial values as required for Allocated storage, Provisioned IOPS, and Storage throughput.

(Optional) Expand the Additional storage configuration section, and select Enable storage autoscaling.

In the Connectivity section, choose if you want to set up a connection to a compute resource for the database.

For VPC, choose the same VPC that has AWS Directory Service.

For DB subnet group, choose a group that spans multiple Availability Zones.

For public access, choose No.

For VPC security group (firewall), choose an existing group or create a new one that allows access through SQL Server port 1433.

Select your preferred Availability Zone.

Expand the Additional configuration section, and choose if you want to use a custom database port.

In the Microsoft SQL Server Windows authentication section, select the Enable Microsoft SQL Server Windows authentication checkbox.

For Windows authentication type, choose AWS Managed Microsoft AD.

For Directory, choose Browse directory, and select AWS Managed Microsoft AD.

In the Monitoring section, choose either standard or advance database insights.

Under Performance insights, select the Enable performance insights checkbox.

Select a retention period and AWS KMS key.

Under Additional monitoring settings, select the Enhanced monitoring checkbox.

(Optional) Under Log exports, select the Error log checkbox.

Expand the Additional configuration section.

For DB parameter group, and DB option group, choose default or custom values.

Set your preferred time zone.

For Collation, set a value. The default is SQL_Latin1_General_CP1_CI_AS.

Under Backup:

Under Maintenance window, select the Choose a window checkbox, and set a preferred time.

Select the Enable deletion protection checkbox.

Use RDP to connect to your Windows machine using AWS Managed Microsoft AD credentials

Launch SSMS by entering SSMS in the Start menu, and select SQL Server Management Studio.

When the Connect to server dialog box appears (or by navigating to Object explorer, Connect, Database engine), set Server type to Database engine.

Enter the RDS SQL Server endpoint (for example, your-rds-instance.region.rds.amazonaws.com)

Choose Windows authentication.

In the Connection properties tab, set Encryption to Mandatory.

Select the Trust server certificate checkbox.

Keep the Host name in certificate field blank.

(Optional) Set the database name and adjust connection timeout as needed.

Set up and test Windows authentication for domain users.

To establish an initial connection, choose Connect.

In the query window, run the following:

Log out of the Amazon EC2 instance.

Log back in to the EC2 instance using your domain credentials.

Launch SSMS.

Connect using Windows Authentication.

Verify that the connection is successful.