Join SQL Server on AWS to Microsoft Entra Domain Services

Windows Authentication offers a secure and efficient mechanism for authentication management in Microsoft SQL Server. Many customers use Microsoft Entra ID (previously Azure AD) as their identity provider for Windows authentication. With the launch of support to join Amazon RDS for SQL Server to self-managed Active Directory, you can now join your Amazon Relational Database Service (Amazon RDS) for SQL Server instances on AWS directly to Microsoft Entra Domain Services. This new feature allows you to use your logins in Microsoft Entra ID and Microsoft Entra Domain Services, for Windows Authentication on Amazon RDS. For Amazon RDS Custom and SQL Server on Amazon EC2, the domain join steps are the same as how you would do on a self-managed instance.

In this post, we show you how to domain join Amazon RDS for SQL Server, Amazon RDS Custom for SQL Server, and self-managed SQL Server on Amazon Elastic Compute Cloud (Amazon EC2) to Microsoft Entra ID via Microsoft Entra Domain Services.

Solution overview

The following diagram shows the high-level architecture pattern for joining SQL Server in AWS to Microsoft Entra ID via Microsoft Entra Domain Services.

Microsoft Entra Domain Services (managed domain) integrates with an existing Microsoft Entra ID tenant. This integration allows users to sign in to services and applications connected to Microsoft Entra Domain Services using existing credentials. The managed domain is configured to perform a one-way synchronization, from Microsoft Entra ID to Microsoft Entra Domain Services, which provides access to users, groups, and credentials located in Microsoft Entra ID.

The initial objects synchronization may take some time depending on the number of objects in Microsoft Entra ID, and once the initial synchronization completes, changes made like password change will automatically sync to Microsoft Entra Domain Services. When a new user is created in Microsoft Entra ID, it will not be synchronized to Microsoft Entra Domain Services until the user changes its password in Microsoft Entra ID. For more information, refer to Synchronization from Microsoft Entra ID to Domain Services.

To deploy SQL Server on AWS with Microsoft Entra Domain Services, you first need to create a service account for SQL Server and an organizational unit (OU) in Microsoft Entra ID. You also need to delegate the service account with limited permissions to the OU.

Although you can reuse the same service account or OU for multiple SQL Server deployments in AWS, we recommend creating a new OU and service account for each deployment. Doing so will increase your security boundary and reduce the potential blast radius from any configuration issues.

To simplify setup of the AD objects, you can use the PowerShell script to create the objects and set the appropriate permissions for you.

Prerequisites

Make sure you have the following services and solutions preconfigured for this post:

• An active Azure account.
• Micosoft Entra Domain Services deployed.
• Make sure you have met the requirements to join your RDS for SQL Server to self-managed Active Directory
• Private network connectivity between Azure and AWS, such as a site-to-site VPN connecting your VPC to Azure Virtual Network.
• To join RDS Custom for SQL Server, create an RDS Custom for SQL Server instance. For instructions, refer to Creating and connecting to a DB instance for Amazon RDS Custom for SQL Server.
• To join SQL Server on EC2, create AWS EC2 SQL Server instance.
• DNS resolution for Amazon EC2 and RDS Custom for SQL Server using the methods mentioned in Integrating your Directory Service’s DNS resolution with Amazon Route 53 Resolvers. Use the DNS IP address of Microsoft Entra Domain Services for configuration.
• An EC2 Windows Server instance (MGMT in this post) joined to Microsoft Entra Domain Services. Use the domain administrator to join the EC2 instance to Microsoft Entra Domain Services using the Microsoft Entra ID. You will need to reset the user password first. For more information, refer to Tutorial: Join a Windows Server virtual machine to an Azure Active Directory Domain Services managed domain. Ensure Active Directory administration and SQL Server Management Studio tools are installed on this EC2 instance.

Create the Microsoft Entra ID Organizational Unit (OU) and service account

In this step, you create the following AD objects required to deploy SQL Server in AWS with Microsoft Entra Domain Services using the PowerShell script provided in Join Amazon RDS for SQL Server to your self-managed Active Directory, specifically under the section “Create the Active Directory OU and service account”. For this post, we created the OU and service account using the following naming conventions:

• An OU for the SQL Server deployment in AWS, named RDS-MSSQL (or any name of your choosing)
• A service account for SQL Server in AWS, named AWSMSSQLServiceAccount (or any name of your choosing) with least privileges

Now that you’ve created the target OU and service account with the proper delegation, you need to store the service account information in an AWS Secrets Manager secret.

Joining Amazon RDS for SQL Server to Microsoft Entra Domain Services

In this section, you will join Amazon RDS for SQL Server to Microsoft Entra Domain Services completing the requirements listed previously.

Create an AWS KMS key and Secrets Manager secret

As a best practice, we recommend storing the SQL Server service account information in AWS Secrets Manager, encrypted with AWS KMS key. To join RDS for SQL Server to self-managed Active Directory, it is mandatory to store the SQL Server service account information in AWS Secrets Manager, encrypted by AWS Key Management Service (AWS KMS) key.

1. Create an AWS KMS key using the AWS Command Line Interface (AWS CLI) or AWS Management Console to encrypt the secret that we will store in Secrets Manager.
2. Create a Secrets Manager secret to store your SQL Server service account information.
3. Grant GetSecretValue permissions for AWS Secrets Manager secret resource to Amazon RDS service principal.

You can use the following code in the AWS CLI.

4. Create an AWS KMS key with appropriate permissions to the key. Sample policy can be downloaded here. Modify the policy with the information from your AWS account and make note of the key ID from the output.

   aws kms create-key --policy <a href="file://kms_policy.json" target="_blank" rel="noopener">file://kms_policy.json</a>

5. Create Secrets Manager secret for storing SQL Server service account ID and password, and encrypting with the KMS key created using the previous steps.

   aws secretsmanager create-secret --name sqlcmadsec --kms-key-id <kms key id> --secret-string "{\"CUSTOMER_MANAGED_ACTIVE_DIRECTORY_USERNAME\":\" <username>\",\"CUSTOMER_MANAGED_ACTIVE_DIRECTORY_PASSWORD\":\"<password>"}"

6. Attach a policy with appropriate permissions to the secret. Sample policy can be downloaded here.

   aws secretsmanager put-resource-policy --secret-id sqlcmadsec --resource-policy file://secrets_policy.json

Take note of the secret ARN, used later, to deploy the RDS for SQL Server instance.

Deploy Amazon RDS for SQL Server domain joined with Microsoft Entra Domain Services

Now you can enable Windows Authentication with Microsoft Entra Domain Services for Amazon RDS for SQL Server. Refer to Creating a DB instance for instructions on creating an RDS for SQL Server instance.

1. On the Amazon RDS console, in the navigation pane, choose Databases.
2. Choose Create database.
3. In the Microsoft SQL Server Windows Authentication section, set the following:
   1. Select Enable Microsoft SQL Server Windows authentication.
   2. For Windows authentication type, select External Active Directory Domain.
   3. For Fully qualified domain name, enter the domain name of the Microsoft Entra Domain Services deployment. For this post, we use corp.example.com.
   4. For Domain organizational unit, enter the location for your RDS AD objects. For this post, we use OU=RDS-MSSQL,DC=corp,DC=example,DC=com.
   5. For Authorization secret ARN, enter the ARN of the secret containing the service accounts credentials.
   6. For Primary DNS, enter the primary IP address of the Microsoft Entra Domain Services DNS.
   7. For Secondary DNS, enter the secondary IP address of the Microsoft Entra Domain Services DNS.
4. Provide the other required settings and create your database instance.
   

When deployment is complete, Amazon RDS for SQL Server will be created domain joined to Microsoft Entra ID via Microsoft Entra Domain Services.

Joining Amazon RDS Custom for SQL Server and SQL Server on Amazon EC2 to Microsoft Entra Domain Services

Do the following to join Amazon RDS Custom and SQL Server on EC2 to Microsoft Entra DS after completing the requirements mentioned previously.

Configure DNS resolution

You must configure DNS resolution within your VPC to resolve Microsoft Entra Domain Services’ fully qualified domain name (FQDN), for example, corp.example.com. If you modify DNS resolver settings of the network interface on the RDS Custom instance, it will cause DNS-enabled VPC endpoints to no longer work correctly, and these are required for instances within private subnets without internet access. There are multiple ways to configure DNS resolution. We recommend that you configure the VPC DNS resolver to forward queries for certain domains with an Amazon Route 53 outbound endpoint and resolver rule.

To join an Amazon RDS Custom for SQL Server or SQL Server on Amazon EC2 to Microsoft Entra Domain Services, complete the following steps:

1. Connect to the EC2 instance using RDP with SQL Server or the RDS Custom for SQL Server DB instance. Provide the username and password used during instance creation. For more information, refer to Connecting to your RDS Custom DB instance using RDP.
2. Join the instance to the Microsoft Entra Domain Services domain. For instructions, refer to Join a Computer to a Domain. When prompted for the user name and password, enter an Microsoft Entra Domain Services domain account that has permission to domain join (for this post, AWSSQLServiceAccount).

When you are done, Amazon RDS Custom for SQL Server or SQL Server on EC2 will be domain joined to Microsoft Entra ID via Microsoft Entra Domain Services.

During failover or system restores of RDS Custom, you will need to perform domain join again. Consider automating the Active Directory configuration following the steps in Automate the configuration of Amazon RDS Custom for SQL Server using AWS Systems Manager.

Validate deployment

When your RDS for SQL Server instance is active, you can connect to the instances from the MGMT EC2 instance node using SQL Server Management Studio (SSMS) with the admin credentials you set when you deployed the RDS instance. After you’re logged in, give the Microsoft Entra Domain Services’ domain user account permission to authenticate into the RDS for SQL Server instance.

1. RDP into the MGMT EC2 instance and connect using the Microsoft Entra Domain Services user name and password created previously.
2. Launch SSMS.
3. Enter following information in Connect dialog box:
   1. For Server name, enter the name of the RDS for SQL Server instance.
   2. For Authentication, choose SQL Server Authentication.
   3. For Login, enter the username you set when you created the RDS instance.
   4. For Password, enter the password you set when you created the RDS instance.

4. In SSMS, under Security in the navigation pane, choose (right-click) Logins and choose New Login.
5. Enter the Microsoft Entra ID admin user for the login name, grant the necessary permission and choose OK.
6. Choose Start, enter SSMS, and choose SQL Server Management Studio to open a new SSMS window.
7. In the dialog box, provide the following information, then choose Connect:
   1. For Server name, enter the name of the RDS for SQL Server instance endpoint.
   2. For Authentication, choose Windows Authentication.

You can follow the same instructions to validate the connection for Amazon RDS Custom for SQL Server and SQL Server on Amazon EC2 by providing the RDS Custom for SQL Server endpoint or EC2 endpoint for connection, respectively.

You have now successfully signed in to your RDS for SQL Server instance with a user from your Microsoft Entra Domain Services.

Clean up

To avoid incurring charges, clean up the resources that you created as part of this post. This will delete the instances, make sure to keep snapshots as part of recovery if required by your business.

• Terminate Amazon EC2 instance
• Delete Amazon RDS for SQL Server
• Delete Amazon RDS Custom for SQL Server
• Delete Microsoft Entra Domain Services

Conclusion

In this post, we showed you how to domain join RDS for SQL Server, RDS Custom for SQL Server and SQL Server on Amazon EC2 to Microsoft Entra Domain Services and authenticating using Microsoft Entra ID user. We created a new AWS MSSQL AD OU and service account with proper AD delegations and stored the MSSQL Service account in AWS Secrets Manager, encrypted by newly created AWS KMS key. Then we deployed a new RDS for SQL Server instance integrated with Microsoft Entra Domain Services. We also walked through process of joining Amazon RDS Custom for SQL Server and SQL Server on Amazon EC2 to Microsoft Entra Domain Services. Finally, we validated a Microsoft Entra ID user authentication into Amazon RDS for SQL Server, Amazon RDS Custom for SQL Server, and SQL Server on Amazon EC2.

If you have any comments or questions, leave them in the comments section.

About the Authors

Rita Ladda is Microsoft Specialist Senior Solution Architect at Amazon Web Services with over 20 years of experience in many Microsoft Technologies. She specializes in designing database solutions in SQL Server and other databases. She provides architectural guidance to customers in migration and modernization of their Microsoft workloads to AWS.

Barry Ooi is a Senior Database Specialist Solutions Architect at AWS. His expertise is in designing, building, and implementing data platforms using cloud-native services for customers as part of their journey on AWS. His areas of interest include data analytics and visualization. In his spare time, he loves music and outdoor activities

Siva Thang is a Senior Solutions Architect, Partners with AWS. His specialty is in databases and analytics, and he also holds a master’s degree in Engineering. Siva is deeply passionate about helping customers build a modern data platform in the cloud that includes migrating to modern relational databases and building data lakes and data pipelines at scale for analytics and machine learning. Siva also likes to present in various conferences and summits on the topic of modern databases and analytics.