Use a self-hosted Active Directory with Amazon RDS Custom for SQL Server

Applications utilizing SQL Server can take advantage of integration with Active Directory (AD) and use Windows Authentication. This allows database access to be controlled at the domain level and can simplify account administration. Amazon Relational Database Service (Amazon RDS) for SQL Server supports using AWS Directory Service for Microsoft Active Directory for Windows Authentication, and supports using a self-hosted AD when used with a trust relationship. However, you may not want to establish trust relationships with your self-hosted AD, or may not want to use AWS Managed Microsoft AD, and therefore can’t use Windows Authentication with Amazon RDS for SQL Server.

With Amazon RDS Custom for SQL Server, you can now use a self-hosted AD. Amazon RDS Custom for SQL Server lets you access and customize your database environment and underlying operating system so you can join your SQL Server to a self-hosted AD and configure Windows Authentication for your database access without using AWS Managed Microsoft AD.

When using Amazon RDS Custom for SQL Server, it’s ideal to architect your solution such that customizations are done with repeatable, idempotent scripts. While RDS Custom for SQL Server allows full administrative access, it’s still important to follow cloud principles and architect the solution to keep it as stateless as possible, aside from the SQL Server data itself. Although this is not necessary, it provides a faster way to re-apply your customizations.  These scripts can be ran using Amazon Systems Manager (SSM).

On RDS Custom for SQL Server, be cautious before scaling compute. Scaling compute replaces the OS host disk (C: drive) with a fresh image containing the latest patches for Windows and SQL Server. If your changes are scripted, it’s more efficient to re-apply them after scaling compute. Note that RDS Custom for Oracle behaves differently when scaling.

In this post, I guide you through the prerequisites for using a self-hosted AD within your VPC, how to deploy Amazon RDS Custom for SQL Server, and how to join your RDS DB instance to your self-hosted AD to enable Windows Authentication.

Solution overview

The following figure illustrates the solution architecture.

This post guides you through the following high-level steps:

Prepare your Amazon Virtual Private Cloud (Amazon VPC).
Configure DNS resolution.
Provision an RDS Custom for SQL Server DB instance.
Optionally, secure AD join credentials.
Enable Windows Authentication.
Test logging in to the database.

Prerequisites

These following prerequisites are required to implement the solution:

A self-hosted AD already deployed and configured. This doesn’t need to be hosted within AWS, but you should consider how latency could affect authentication performance over long distances. See  Scenario 1 from the Active Directory Domain Services on AWS Quick Start for an example of self-hosted AD deployment.
A user within the self-hosted AD with appropriate privileges to add computers to the domain.
An AWS account and the required privileges to perform the actions in this post.

Prepare the VPC

To join an RDS Custom for SQL Server DB instance to your self-hosted AD, you need a VPC with the following requirements:

Subnets with connectivity to your self-hosted AD domain controllers.
If using private subnets with no internet access, AWS PrivateLink VPC endpoints are required for Amazon RDS Custom to access the necessary AWS services. For more information, refer to Configure endpoints for dependent AWS services.
An appropriate security group for your RDS DB instance. This allows SQL Server TCP protocol (default 1433) access from your application to the DB instance. You need outbound connectivity to the self-hosted domain controllers to join the domain and perform Windows Authentication.

Configure DNS resolution

You need to configure DNS resolution within your VPC to resolve your self-hosted Active Directory’s fully qualified domain name (FQDN), for example, example.local. There are multiple ways to achieve this; one straightforward way is to configure the VPC DNS resolver to forward queries for certain domains with an Amazon Route 53 outbound endpoint and resolver rule.

Note that modifying the DNS resolver settings of the network interface on the RDS Custom instance will cause DNS-enabled VPC endpoints to no longer work correctly, and these are required for instances within private subnets without internet access.

Provision an RDS Custom for SQL Server DB instance

To set up Amazon RDS Custom, you need an AWS Identity and Access Management (IAM) role. For instructions, refer to Configure networking, instance profile, and encryption.

A customer managed symmetric AWS Key Management Service (AWS KMS) key is also required to set up Amazon RDS Custom. See Make sure that you have a symmetric encryption AWS KMS key for instructions.

You’re now ready to provision your DB instance. For instructions, refer to Creating an RDS Custom for SQL Server DB instance. The credentials you supply for the primary user name and password are needed for initial connection using SQL Server Management Studio (SSMS).

Secure AD join credentials (Optional)

To join your RDS instance to your self-hosted domain, you need to provide domain credentials that are authorized to add computers to the domain. Adding this credential to AWS Secrets Manager allows you to protect the credential use and utilize automation for the domain join activities.

To create a secret, complete the following steps:

On the Secrets Manager console, choose Secrets in the navigation pane.
Choose Store a new secret.
For Secret type, choose Other type of secret.
For Key/value pairs, in the first field, enter username. In the second field, enter the Active Directory user name.
Choose Add row.
In the first field, enter password. In the second field, enter the password for the Active Directory user.
For Encryption key, keep DefaultEncryptionKey to use the AWS managed key for Secrets Manager.
Choose Next.
For Secret name, enter ADJoinSecret.
Choose Next.
Select Disable automatic rotation, then choose Next.
On the Review page, review the secret details, then choose Store.

The Secrets Manager console returns to the list of secrets in your account, and the new secret is now in the list.
You now need to allow your RDS Custom instance to access this secret. Add the following IAM policy to the IAM role you created:

Join the RDS DB instance to self-hosted Active Directory

It’s a best practice to pause RDS Custom automation before doing any customization of the DB instance. For more information, see Pausing and resuming RDS Custom automation.

In this section, I provide two methods to update the instance: using Remote Desktop Protocol (RDP), or using PowerShell.

Note that changes to the Microsoft Windows operating system or C: drive don’t persist if you replace this RDS DB instance. However, you can redo those changes using automation.

Additionally, when deleting your RDS DB instance, you should remove the computer object from your self-hosted AD. You can automate this task using Amazon EventBridge.

Option 1: Use Remote Desktop Protocol

Connect to the RDS DB instance using RDP. For instructions, see Connecting to your RDS Custom DB instance using RDP. After logging in, complete the following steps:

On the Start menu, choose Control Panel.
Navigate to System and Security, then choose System.
Under Computer name, domain, and workgroup settings, choose Change settings.
On the Computer Name tab, choose Change.
Under Member of, choose Domain.
Enter the name of the domain that you want this computer to join, then choose OK.
Enter the credentials for the user authorized to add computers to the domain.
Choose OK.
Restart the DB instance.

Option 2: Use PowerShell

You can use the following PowerShell script in conjunction with domain credentials in Secrets Manager:

After the instance has restarted, you can resume RDS Custom automation.

Enable Windows Authentication

In this section, I provide two methods to enable Windows Authentication: using SSMS, or using PowerShell.

Option 1: Use SQL Server Management Studio

To enable Windows Authentication with SSMS, complete the following steps:

Open SSMS and connect using the primary credentials you set during instance creation.

Expand Security.
Choose (right-click) Logins and choose New Login.

Choose Search and choose Locations to select your self-hosted domain.
Enter the valid credentials authorized to read domain objects (for this post, I added dbadmin).

Select a domain user or group to authorize appropriate access to SQL Server admin tasks or access to specific databases (for this post, I assigned the sysadmin role).

Option 2: Use PowerShell

To enable Windows Authentication using PowerShell, complete the following steps:

Install the PowerShell SqlServer module (requires internet access or download)
Create a credential object for the primary database user
Add a SQL login

This code requires the following values:

ServerInstance – The name of the SQL Server instance (the Windows server name).
loginName – The ID for the user or group within the directory. Enter this in the format shortdomainnameid.
loginType – The type of object, either WindowsUser or WindowsGroup.

Test database login

You can now log in to the database with Windows Authentication. Log in to a machine with SSMS installed as the user you granted permission to and choose Windows Authentication as the authentication type.

Clean up

To prevent unwanted charges to your AWS account, delete the AWS resources that you have created which you no longer need.

Conclusion

With Amazon RDS Custom for SQL Server, you can use a self-hosted Active Directory for Windows Authentication. If you don’t want to use AWS Managed Microsoft AD but need to secure your SQL Server using Windows Authentication, you can take advantage of the time-saving, durability, and scalability benefits of a managed database service. Amazon RDS Custom for SQL Server is available now. Refer to Amazon RDS Custom pricing for pricing, Regional availability, and instance class availability.

To learn more about Amazon RDS Custom, refer to Working with Amazon RDS Custom.

About the Author

Peter Giuliano is a Senior Migration Solution Architect at AWS with 18 years experience working with OnPremise and Cloud infrastructure. At AWS he helps enterprise customers plan and execute large scale migrations to the Cloud making sure customer experiences and lessons are shared. Outside of work Peter is a keen runner and enjoys traveling to new places.

Read MoreAWS Database Blog