Amazon RDS Custom for SQL Server now supports Windows Authentication for DB instances

Amazon RDS Custom for SQL Server now allows you to directly join your DB instances to the domains of Microsoft Active Directory (AD). Your AD can be AWS Managed Microsoft AD or self-managed either on premises or in the AWS Cloud. This eliminates the need to manually join the DB instance to the domain, streamlining the process and saving time.

In this post, we show how to join RDS Custom DB instances to an AD for Windows Authentication. This applies to AD domains running in a self-managed environment either on premises or Amazon Elastic Compute Cloud (Amazon EC2) and AWS Managed Microsoft AD.

Solution overview

You can use the AWS Management Console to implement the solution, automate the process using the AWS Command Line Interface (AWS CLI), or use an infrastructure as code (IaC) solution such as AWS CloudFormation or Terraform to simplify the deployment of domain-joined DB instances.

In this post, we demonstrate how use the console to create a new DB instance or modify an existing DB instance and join it to a domain.

Prerequisites

You can use AWS CloudFormation deploy the necessary resources required to provision RDS Custom DB instances in your VPC. For more information about the template and deployment instructions, refer to Launch an Amazon RDS Custom for SQL Server Instance using AWS CloudFormation

Additionally, complete the following prerequisite steps:

Configure your network connectivity to your AD environment. This applies to both a self-managed AD as well as an AWS managed AD.

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

Prepare your AD to manage the RDS Custom DB instances domain membership:
Create an AWS secret.

Prerequisites Validation

You can use the nslookup command from an EC2 instance within the same VPC as your RDS Custom DB instance to test the domain FQDN resolution:

nslookup <your-ad-domain-fqdn>

In addition to the AD domain FQDN, your RDS Custom DB instance will need to connect to various AWS service endpoints. Refer to Configure endpoints for dependent AWS services for more information. You should validate network connectivity before configuring Windows Authentication. It’s also important to make sure that your RDS Custom DB instance can communicate on required ports.

Run the network validation script on a EC2 instance that is created in same VPC subnets where you plan to launch custom instance and joined to AD.

Validate if instance is joined to AD
Validate if domain FQDN is reachable
Validate domain reachability on required ports
Verify connectivity to the following endpoints: S3, secrets manager, logs, events, monitoring, ssm, ec2messages, ssmmessages.

region='<input-your-aws-region>'
$domainFQDN='<input-your-domain-fqdn>'

function Test-DomainPorts {
    param (
        [string]$Domain,
        [array]$Ports
    )

    foreach ($portInfo in $Ports) {
        try {
            $conn = New-Object System.Net.Sockets.TcpClient
            $connectionResult = $conn.BeginConnect($Domain, $portInfo.Port, $null, $null)
            $success = $connectionResult.AsyncWaitHandle.WaitOne(1000) # 1 second timeout
            if ($success) {
                $conn.EndConnect($connectionResult)
                $result = $true
            } else {
                $result = $false
            }
        }
        catch {
            $result = $false
        }
        finally {
            if ($null -ne $conn) {
                $conn.Close()
            }
        }
        Write-Host "$($portInfo.Description) port open: $result"
    }
}

# Check if ports can be reached 
$ports = @(
    @{Port = 53;   Description = "DNS"},
    @{Port = 88;   Description = "Kerberos"},
    @{Port = 389;  Description = "LDAP"},
    @{Port = 445;  Description = "SMB"},
    @{Port = 5985; Description = "WinRM"},
    @{Port = 636;  Description = "LDAPS"},
    @{Port = 3268; Description = "Global Catalog"},
    @{Port = 3269; Description = "Global Catalog over SSL"},
    @{Port = 9389; Description = "AD DS"}
)

function Test-DomainReachability {
    param (
        [string]$DomainName
    )
    
    try {
        $dnsResults = Resolve-DnsName -Name $DomainName -ErrorAction Stop
        Write-Host "Domain $DomainName is successfully resolving to following IP addresses: $($dnsResults.IpAddress)"
        Write-Host ""
        return $true
    } 
    catch {
        Write-Host ""
        Write-Host "Error Message: $($_.Exception.Message)"
        Write-Host "Domain $DomainName reachability check failed, please Configure DNS resolution"
        return $false
    }
}

$domain = (Get-WmiObject Win32_ComputerSystem).Domain
if ($domain -eq 'WORKGROUP') {
    Write-Host ""    
    Write-Host "Host $env:computername is still part of WORKGROUP and not part of any domain"
    }
else {
    Write-Host ""
    Write-Host "Host $env:computername is joined to $domain domain"
    Write-Host ""
    }

$isReachable = Test-DomainReachability -DomainName $domainFQDN  
if ($isReachable) {
    write-Host "Checking if domain $domainFQDN is reachable on required ports  "
    Test-DomainPorts -Domain $domainFQDN -Ports $ports
}
else {
    Write-Host "Port check skipped. Domain not reachable"
}   


# Get network adapter configuration
$networkConfig = Get-WmiObject Win32_NetworkAdapterConfiguration | 
                 Where-Object { $_.IPEnabled -eq $true } |
                 Select-Object -First 1

# Check DNS server settings
$dnsServers = $networkConfig.DNSServerSearchOrder

if ($dnsServers) {
    Write-Host "`nDNS Server settings:"
    foreach ($server in $dnsServers) {
        Write-Host "  - $server"
    }
} else {
    Write-Host "`nNo DNS servers configured or unable to retrieve DNS server information."
}

write-host ""

# Checks Reachability to AWS dependent Services
$services = "s3", "ec2", "secretsmanager", "logs", "events", "monitoring", "ssm", "ec2messages", "ssmmessages"

function Get-TcpConnectionAsync {
    param (
        $ServicePrefix,
        $region
    )
    $endpoint = "${ServicePrefix}.${region}.amazonaws.com"
    $tcp = New-Object Net.Sockets.TcpClient
    $result = $false

    try {
        $connectTask = $tcp.ConnectAsync($endpoint, 443)
        $timedOut = $connectTask.Wait(3000)
        $result = $tcp.Connected
    } 
    catch {
        $result = $false
    } 
    return $result
}

foreach ($service in $services) {
    $validationResult = Get-TcpConnectionAsync -ServicePrefix $service -Region $region
    Write-Host "Reachability to $service is $validationResult"
}

Create a new RDS Custom DB instance and join it to the AD domain

To create a DB instance and join it to the domain using the console, complete the following steps:

On the Amazon RDS console, choose the AWS Region in which you want to create the DB instance.
In the navigation pane, choose Databases.
Choose Create database, then choose Standard create.
For Engine type, select Microsoft SQL Server.
For Database management type, choose Amazon RDS Custom.
For Edition, choose the DB engine edition that you want to use.
For Engine version, choose the engine version.
For Templates, choose the template that matches your use case (Production or Dev/Test).
In the Settings section, enter a unique name for DB instance identifier.
To enter your primary password, do the following:
1. In the Settings section, open Credential Settings.
2. Deselect the Auto generate a password check box.
3. Change the Master username value and enter the same password for Master password and Confirm password.
By default, the new RDS Custom DB instance uses an automatically generated password for the primary user.
In the DB instance size section, choose a value for DB instance class.
For supported classes, see DB instance class support for RDS Custom for SQL Server.
In the Storage section, specify your storage settings.
In the Availability & durability section, specify your availability settings.
In the Connectivity section, specify your connectivity settings.
In the Microsoft SQL Server Windows Authentication section, enter the following details:
1. For Fully qualified domain name, enter your FQDN (for this post, example.com).
2. For Domain organizational unit, enter organizational unit created in step 2a, for eg: OU=RDSCustom,OU=example,DC=example,DC=com.
3. For Authorization secret ARN, enter arn:aws:secretsmanager:us-east-2:7xxxxxxxxx13:secret:do-not-delete-rds-custom-domain-join-secret-mycredentials-axxxx created in step 3.
In the RDS Custom security section, do the following:
1. For IAM instance profile, choose the instance profile that has required IAM role
2. For Encryption, choose Enter a key ARN to list the available AWS Key Management Service (AWS KMS) keys, then choose your key from the list.
An AWS KMS key is required for RDS Custom. For more information, see Make sure that you have a symmetric encryption AWS KMS key.
Under Additional configuration, configure settings for Backup, Maintenance, and Deletion protection.
Choose Create database.

Modify an existing RDS Custom DB instance to join it to an AD domain

To modify an existing RDS Custom DB instance to join it to an AD domain, complete the following steps:

Note: If your RDS custom instance is already joined to AD manually, please complete all prerequisite and run modify RDS custom instance to join AD, so that domain join credentials and configurations are registered with our automation and AWS automation can monitor your instance, register CNAME and take recovery actions when there is failover or EC2 replacement.

On the Amazon RDS console, in the navigation pane, choose Databases, and then choose the DB instance that you want to modify.
Choose Modify.
Under Microsoft SQL Server Windows Authentication, enter the following details:
1. For Fully qualified domain name, enter your FQDN (for this post, example.com).
2. For Domain organizational unit, enter organizational unit created in step 2a, for example: OU=RDSCustom,OU=example,DC=example,DC=com.
3. For Authorization secret ARN, enter arn:aws:secretsmanager:us-east-2:7xxxxxxxxx13:secret:do-not-delete-rds-custom-domain-join-secret-mycredentials-axxxx created in step 3.
4. Choose Continue.
Optionally, select Apply immediately to apply the changes immediately.
Choosing this option can cause downtime in some cases. For more information, see Using the schedule modifications setting.
Review your changes and choose Modify DB instance to save your changes.

Test the solution

Complete the following steps to test the solution:

Use DNS manager and connect to your DNS server to validate the CNAME registration for the RDS Custom DB instance.
You can locate the CNAME record for your RDS Custom DB instance as shown in the following screenshot. If access to DNS server is restricted try to resolve for alias.

The Kerberos-based endpoint follows a specific format. If your RDS endpoint is:
```
<rds-instance-name>.<account-region-hash>.<aws-region>.rds.amazonaws.com
```
Then the corresponding Kerberos-based endpoint would be:
```
<rds-instance-name>.<account-region-hash>.<aws-region>.awsrds.<fully qualified domain name (FQDN)>
```
Note: Only NTLM (and not Kerberos) will be supported if RDS can’t register CNAMES or DNS entries for Computer Objects in the Domain Controllers.
You can also verify that Kerberos authentication is being used for connections to the database. For more information, see How to determine if the authentication type is Kerberos.
Open SQL Server Management Studio (SSMS).
Connect to the RDS Custom DB instance using Windows Authentication.
To connect to SQL Server with Windows Authentication, you must be logged in to a domain-joined computer as a domain user.
After you have connected to SSMS, choose New Query.

Enter the following query and choose Execute to determine the authentication type:

SELECT net_transport,auth_scheme FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Clean up

It’s a best practice to delete resources that you’re no longer using so you don’t incur unintended charges. Complete the following steps to clean up the resources you created for this post:

On the Amazon RDS console, choose Databases in the navigation pane.
Select the DB instance to delete.
On the Actions menu, choose Delete.
Enter delete me to confirm deletion, then choose Delete.
When prompted to create a final snapshot and retain automated backup, choose the option appropriate for your needs.

Conclusion

In this post, we outlined the steps to configure and validate Windows Authentication on RDS Custom DB instances. Kerberos authentication can simplify the management of user access and improve overall security. By following the steps outlined in this post, you can use Windows Authentication with Kerberos to enhance the security and manageability of your RDS Custom DB environment. We hope this information proves valuable as you work to set up and configure Windows Authentication for your RDS Custom DB instance.

About the authors

Sudhir Amin is a Sr. Solutions Architect at Amazon Web Services based in New York. In his role, he provides architectural guidance and technical assistance to enterprise customers across different industry verticals, accelerating their cloud adoption. He’s a big fan of snooker, combat sports such as boxing and UFC, and loves traveling to countries with rich wildlife reserves where he gets to see world’s most majestic animals up close.

Santhosh Reddy Talla is an accomplished Database Engineer , works with RDS SQL Server team at AWS, with expertise in relational databases such as SQL Server, Oracle, Postgres, and MySQL. He has a strong ability to solve complex problems, optimize database performance, and automate tasks using Python, Shell, and PowerShell. Outside of work he is avid outdoor enthusiast, with a love for skiing and hiking.

AWS Database Blog