Windows Authentication with AWS Managed Microsoft AD

Sign in, start the SQL lab and set names:

cd C:\aws-lab
aws login --profile saat-admin
$env:AWS_PROFILE = 'saat-admin'
$v = Get-Content C:\aws-lab\saat-vpc.json | ConvertFrom-Json
.\Start-SqlLab.ps1

1Create the Managed AD domain

Paste this whole block. The directory usually takes 20 to 40 minutes to become Active:

$adPw = aws secretsmanager get-random-password --password-length 20 --exclude-punctuation --require-each-included-type --query RandomPassword --output text
$dir = aws ds create-microsoft-ad --name corp.saatvedha.lab --short-name SAAT --password $adPw --edition Standard --vpc-settings "VpcId=$($v.VpcId),SubnetIds=$($v.PrivateA),$($v.PrivateB)" --tags Key=course,Value=saatvedha-aws --query DirectoryId --output text
do { Start-Sleep 60; $stage = aws ds describe-directories --directory-ids $dir --query "DirectoryDescriptions[0].Stage" --output text; $stage } until ($stage -in 'Active', 'Failed')
$dns = (aws ds describe-directories --directory-ids $dir --query "DirectoryDescriptions[0].DnsIpAddrs" --output text) -split '\s+'
"$dir $($dns -join ' ')"

You should see something like this:

Creating
Creating
Active
d-9f67a1b2c3 10.20.11.207 10.20.12.84

Two domain controllers now run in saat-private-a and saat-private-b, with those IP addresses acting as the domain’s DNS servers. The Admin password is in $adPw for this session only; in production it goes straight into Secrets Manager.

2Add a user and a group with the CLI

Paste this whole block:

aws ds enable-directory-data-access --directory-id $dir
Start-Sleep 60
aws ds-data create-user --directory-id $dir --sam-account-name priya --given-name Priya --surname Shah | Out-Null
$userPw = aws secretsmanager get-random-password --password-length 20 --exclude-punctuation --require-each-included-type --query RandomPassword --output text
aws ds reset-user-password --directory-id $dir --user-name priya --new-password $userPw
aws ds-data create-group --directory-id $dir --sam-account-name SaatShopReaders | Out-Null
aws ds-data add-group-member --directory-id $dir --group-name SaatShopReaders --member-name priya
aws ds-data list-group-members --directory-id $dir --sam-account-name SaatShopReaders --query "Members[].SAMAccountName" --output text

You should see this:

priya

No Windows server was needed to manage the directory. Teams with existing AD usually add a trust instead, so people keep their on-premises accounts.

3Join saat-sql to the domain and create the login

Paste this whole block. The join restarts saat-sql and takes 10 to 20 minutes:

'{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "rds.amazonaws.com" }, "Action": "sts:AssumeRole" } ] }' | Set-Content rds-ds-trust.json
aws iam create-role --role-name saat-rds-ds-role --assume-role-policy-document file://rds-ds-trust.json --query Role.RoleName --output text
aws iam attach-role-policy --role-name saat-rds-ds-role --policy-arn arn:aws:iam::aws:policy/service-role/AmazonRDSDirectoryServiceAccess
Start-Sleep 15
aws rds modify-db-instance --db-instance-identifier saat-sql --domain $dir --domain-iam-role-name saat-rds-ds-role --apply-immediately --query "DBInstance.DBInstanceStatus" --output text
Start-Sleep 60
aws rds wait db-instance-available --db-instance-identifier saat-sql
aws rds describe-db-instances --db-instance-identifier saat-sql --query "DBInstances[0].DomainMemberships[0].[Domain,Status,FQDN]" --output text
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -Q "CREATE LOGIN [SAAT\SaatShopReaders] FROM WINDOWS"
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -d SaatShop -Q "CREATE USER [SAAT\SaatShopReaders] FOR LOGIN [SAAT\SaatShopReaders]; ALTER ROLE db_datareader ADD MEMBER [SAAT\SaatShopReaders];"

You should see something like this:

saat-rds-ds-role
modifying
d-9f67a1b2c3      joined  corp.saatvedha.lab

saat-sql is now a member of corp.saatvedha.lab, and anyone in SaatShopReaders can read SaatShop. You granted access to a group, not a person, so the business can manage who’s in it.

4Log in as priya from a domain-joined server

Paste this whole block. It launches winad01, joins it to the domain with Systems Manager, then tests the login (about 20 minutes):

aws iam attach-role-policy --role-name saat-ec2-ssm-role --policy-arn arn:aws:iam::aws:policy/AmazonSSMDirectoryServiceAccess
$dsEp = aws ec2 create-vpc-endpoint --vpc-id $v.VpcId --vpc-endpoint-type Interface --service-name com.amazonaws.ap-south-1.ds --subnet-ids $v.PrivateA --security-group-ids $v.EndpointSg --private-dns-enabled --query VpcEndpoint.VpcEndpointId --output text
$winAmi = aws ssm get-parameter --name /aws/service/ami-windows-latest/Windows_Server-2022-English-Full-Base --query Parameter.Value --output text
$win = aws ec2 run-instances --image-id $winAmi --instance-type t3.medium --subnet-id $v.PrivateA --security-group-ids $v.AppSg --iam-instance-profile Name=saat-ec2-ssm-profile --metadata-options HttpTokens=required --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=winad01},{Key=course,Value=saatvedha-aws},{Key=lesson,Value=45}]' --query "Instances[0].InstanceId" --output text
do { Start-Sleep 30; $p = aws ssm describe-instance-information --filters "Key=InstanceIds,Values=$win" --query "InstanceInformationList[0].PingStatus" --output text } until ($p -eq 'Online')
@{ directoryId = @($dir); directoryName = @('corp.saatvedha.lab'); dnsIpAddresses = $dns } | ConvertTo-Json | Set-Content join.json
aws ssm create-association --name AWS-JoinDirectoryServiceDomain --targets "Key=InstanceIds,Values=$win" --parameters file://join.json | Out-Null
do { Start-Sleep 30; $j = aws ssm describe-instance-associations-status --instance-id $win --query "InstanceAssociationStatusInfos[?Name=='AWS-JoinDirectoryServiceDomain'] | [0].Status" --output text } until ($j -in 'Success', 'Failed')
do { Start-Sleep 30; $p = aws ssm describe-instance-information --filters "Key=InstanceIds,Values=$win" --query "InstanceInformationList[0].PingStatus" --output text } until ($p -eq 'Online')
$test = @'
$pw = ConvertTo-SecureString 'PW_HERE' -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential('SAAT\priya', $pw)
$job = Start-Job -Credential $cred -ArgumentList 'SERVER_HERE' -ScriptBlock {
  param($srv)
  $cn = New-Object System.Data.SqlClient.SqlConnection("Server=$srv;Database=SaatShop;Integrated Security=SSPI;Encrypt=True;TrustServerCertificate=True")
  try { $cn.Open(); $cmd = $cn.CreateCommand(); $cmd.CommandText = "SELECT SUSER_NAME() + ' reads ' + CAST(COUNT(*) AS varchar(10)) + ' orders' FROM dbo.Orders"; $cmd.ExecuteScalar() }
  catch { $_.Exception.Message } finally { $cn.Close() }
}
$job | Wait-Job -Timeout 90 | Receive-Job
'@
function Test-WinLogin([string]$Server) {
  $script = $test -replace 'PW_HERE', $userPw -replace 'SERVER_HERE', $Server
  @{ commands = @($script -split "`r?`n") } | ConvertTo-Json | Set-Content win-test.json
  $cmd = aws ssm send-command --document-name AWS-RunPowerShellScript --instance-ids $win --parameters file://win-test.json --query Command.CommandId --output text
  do { Start-Sleep 10; $st = aws ssm get-command-invocation --command-id $cmd --instance-id $win --query Status --output text 2>$null } until ($st -in 'Success', 'Failed', 'TimedOut')
  aws ssm get-command-invocation --command-id $cmd --instance-id $win --query StandardOutputContent --output text
}
Test-WinLogin 'saat-sql.corp.saatvedha.lab'

You should see something like this:

SAAT\priya reads 3 orders

priya logged in with Kerberos and read the orders through her group. The test runs PowerShell on winad01 as priya using .NET’s built-in SQL client, so no extra tools are needed on the server. You can also connect with Fleet Manager’s Remote Desktop as SAAT\priya and use SSMS.

✅ Checkpoint: corp.saatvedha.lab is Active, saat-sql shows joined, SAAT\SaatShopReaders has a login with db_datareader, and SAAT\priya read 3 orders from winad01.