# Working with Microsoft Active Directory with RDS Custom for SQL Server RDS Custom for SQL Server allows to join your instances to a Self-Managed Active Directory (AD) or AWS Managed Microsoft AD. This is regardless of where your AD is hosted, like an On-premises data center, Amazon EC2 or with any other cloud service providers. For authentication of users and services, you can use NTLM or Kerberos authentication on your RDS Custom for SQL Server DB instance without using intermediary domains and forest trusts. When a user tries to authenticate on your RDS Custom for SQL Server DB instance with a self joined Active Directory, requests for authentication are forwarded to a self-managed AD or AWS Managed Microsoft AD that you specify. In the following sections, you can find information about working with Self Managed Active Directory and AWS Managed Active Directory for RDS Custom for SQL Server. **Topics** + [ ## Region and version availability ](#custom-sqlserver-WinAuth.Regions) + [ # Configure Self-Managed or On-premise AD ](custom-sqlserver-WinAuth.config-Self-Managed.md) + [ # Configure Microsoft Active Directory using Directory Service ](custom-sqlserver-WinAuth.config-ADS.md) + [ # Network configuration port rules ](custom-sqlserver-WinAuth.NWConfigPorts.md) + [ # Network Validation ](custom-sqlserver-WinAuth.NWValidation.md) + [ # Setting up Windows Authentication for RDS Custom for SQL Server instances ](custom-sqlserver-WinAuth.settingUp.md) + [ # Managing a DB instance in a Domain ](custom-sqlserver-WinAuth.ManagingDBI.md) + [ # Understanding Domain membership ](custom-sqlserver-WinAuth.Understanding.md) + [ # Troubleshooting Active Directory ](custom-sqlserver-WinAuth.Troubleshoot.md) ## Region and version availability RDS Custom for SQL Server supports both Self Managed AD and AWS Managed Microsoft AD using NTLM or Kerberos in all Regions where RDS Custom for SQL Server is supported. For more information, see [Supported Regions and DB engines for RDS Custom](Concepts.RDS_Fea_Regions_DB-eng.Feature.RDSCustom.md). # Configure Self-Managed or On-premise AD To join your on-premise or self-managed Microsoft AD to your RDS Custom for SQL Server DB instance, your Active Domain must be configured as follows: + Define the subnets in the VPC associated with your RDS Custom for SQL Server DB instance in your self-managed or on-premises AD. Confirm there are no conflicts between the subnets in your VPC and the subnets in your AD sites. + Your AD domain controller has a domain functional level of Windows Server 2008 R2 or higher. + Your AD domain name can't be in Single Lable Domain (SLD) format. RDS Custom for SQL Server does not support SLD domains. + The fully qualified domain name (FQDN) for your AD can't exceed 47 characters. ## Configure your network connectivity Configure your self-managed or on-premise AD network connectivity in the following manner: + Set up connectivity between Amazon VPC where your RDS Custom for SQL Server instance is running, and your AD. Use Direct Connect, Site-to-Site VPN, AWS Transit Gateway, and VPC Peering. + Allow traffic on the ports your RDS Custom for SQL Server security groups and network ACLs to your self-managed or on-premise AD. For more information, see [Network configuration port rules](custom-sqlserver-WinAuth.NWConfigPorts.md). ![\[Microsoft SQL Server Windows Authentication directory\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/custom-sqs-SM-NC.png) ## Configure DNS resolution Set up the following requirements to configure DNS resolution with self-managed or on-premises AD's: + Configure DNS resolution within your VPC to resolve your self-hosted Active Directory's fully qualified domain name (FQDN). An example of an FQDN is `corp.example.local`. To configure DNS resolution, configure the VPC DNS resolver to forward queries for certain domains with an Amazon Route 53 outbound endpoint and resolver rule. For more information, see [ Configure a Route 53 Resolver outbound endpoint to resolve DNS records](https://repost.aws/knowledge-center/route53-resolve-with-outbound-endpoint). + For workloads that leverage both VPCs and on-premises resources, you must resolve DNS records hosted on-premises. On-premise resources might need to resolve names hosted on AWS. To create a hybrid cloud setup, use resolver endpoints and conditional forwarding riles to resolve DNS queries between your on-premise resources and custom VPC. For more information, see [ Resolving DNS queries between VPCs and your network](https://docs.aws.amazon.com/Route53/latest/DeveloperGuide/resolver-overview-DSN-queries-to-vpc.html) in the *Amazon Route 53 Developer Guide*. **Important** Modifying the DNS resolver settings of the network interface on the RDS Custom for SQL Server causes DNS-enabled VPC endpoints to no longer work correctly. DNS-enabled VPC endpoints are required for instances within private subnets without internet access. # Configure Microsoft Active Directory using Directory Service AWS Managed Microsoft AD creates a fully managed Microsoft Active Directory in AWS that is powered by Windows Server 2019 and operates at the 2012 R2 Forest and Domain functional levels. Directory Service creates the domain controllers in different subnets in an Amazon VPC, making your directory highly available even in the event of failure. To create a directory with AWS Managed Microsoft AD, see [Getting started with AWS Managed Microsoft AD](https://docs.aws.amazon.com/directoryservice/latest/admin-guide/ms_ad_getting_started.html) in the *AWS Directory Service Administration Guide*. ## Configure your network connectivity ### Enable cross-VPC traffic between the directory and the DB instance To locate the directory and the DB instance in the same VPC, skip this step and move on to next step in [Network configuration port rules](custom-sqlserver-WinAuth.NWConfigPorts.md). To locate the directory and the DB instance in different VPCs, configure cross-VPC traffic using VPC peering or AWS Transit Gateway. For more information about using VPC peering, see [What is VPC peering?](https://docs.aws.amazon.com/vpc/latest/peering/what-is-vpc-peering.html) in the *Amazon VPC Peering Guide* and [What is AWS Transit Gateway?](https://docs.aws.amazon.com/vpc/latest/tgw/what-is-transit-gateway.html) in the *Amazon VPC Transit Gateways*. **Enable cross-VPC traffic using VPC peering** 1. Set up appropriate VPC routing rules to ensure that network traffic can flow both ways. 1. Allow the DB instance's security group to recieve inbound traffic from the directory's security group. For more information, see [Network configuration port rules](custom-sqlserver-WinAuth.NWConfigPorts.md). 1. Network access control list (ACL) must not block traffic. If a different AWS account owns the directory, you must share the directory. To share the directory with AWS account within which the RDS Custom for SQL Server instance is by following the [ Tutorial: Sharing your AWS Managed Microsoft AD for seamless EC2 domain-join](https://docs.aws.amazon.com/directoryservice/latest/admin-guide/ms_ad_tutorial_directory_sharing.html) in the *AWS Directory Service Administration Guide*. **Sharing a directory betweens AWS accounts** 1. Sign in to the Directory Service console using the account for the DB instance and check if the domain has the `SHARED` status before proceeding. 1. After signing in to the Directory Service console using the account for the DB instance, note the **Directory ID** value. You use this ID to join the DB instance to the domain. ## Configure DNS resolution When you create a directory with AWS Managed Microsoft AD, Directory Service creates two domain controllers and adds the DNS service on your behalf. If you have an existing AWS Managed Microsoft AD or plan on launching one in a VPC other than your RDS Custom for SQL Server DB instance, configure the VPC DNS resolver to forward queries for certain domains with a Route 53 outbound and resolver rule, see [ Configure a Route 53 Resolver outbound endpoint to resolve DNS records](https://repost.aws/knowledge-center/route53-resolve-with-outbound-endpoint). # Network configuration port rules Make sure that you have met the following network configurations: + Connectivity configured between the Amazon VPC where you want to create the RDS Custom for SQL Server DB instance to either your self-managed Active Directory or AWS Managed Microsoft AD. For self-managed Active Directory, set up connectivity using AWS Direct Connect, AWS VPN, VPC peering, or AWS Transit Gateway. For AWS Managed Microsoft AD, set up connectivity using VPC peering. + Make sure that the security group and the VPC network ACLs for the subnet(s) where you're creating your RDS Custom for SQL Server DB instance allow traffic on the ports and in the directions shown in the following diagram. ![\[Microsoft Active Directory network configuration port rules.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/custom_sqlserver_ActiveDirectory_Requirements_NetworkConfig.png) The following table identifies the role of each port. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-sqlserver-WinAuth.NWConfigPorts.html) + Generally, the domain DNS servers are located in the AD domain controllers. You do not need to configure the VPC DHCP option set to use this feature. For more information, see [DHCP option sets](https://docs.aws.amazon.com/vpc/latest/userguide/VPC_DHCP_Options.html) in the *Amazon VPC User Guide*. **Important** If you're using VPC network ACLs, you must also allow outbound traffic on dynamic ports (49152-65535) from your RDS Custom for SQL Server DB instance. Ensure that these traffic rules are also mirrored on the firewalls that apply to each of the AD domain controllers, DNS servers, and RDS Custom for SQL Server DB instances. While VPC security groups require ports to be opened only in the direction that network traffic is initiated, most Windows firewalls and VPC network ACLs require ports to be open in both directions. # Network Validation Before joining your RDS Custom instance to either self-managed or AWS Managed Microsoft AD, check the following from a EC2 instance in the same VPC as where you plan to launch the RDS Custom for SQL Server instance. + Check if you are able to resolve the fully qualified domain name (FQDN) to domain controller IPs. ``` nslookup corp.example.com ``` The command must return a similar output: ``` Server: ip-10-0-0-2.us-west-2.compute.internal Address: 25.0.0.2 Non-authoritative answer: Name: corp.example.com Addresses: 40.0.9.25 (DC1 IP) 40.0.50.123 (DC2 IP) ``` + Resolve AWS services from an EC2 instance in the VPC where you are launching your RDS Custom instance: ``` $region='input-your-aws-region' $domainFQDN='input-your-domainFQDN' 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 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" } ``` The `TcpTestSucceeded` value must return `True` for `s3`, `ec2`, `secretsmanager`, `logs`, `events`, `monitoring`, `ssm`, `ec2messages`, and `ssmmessages`. # Setting up Windows Authentication for RDS Custom for SQL Server instances We recommend creating a dedicated OU and service credentials scoped to that OU for any AWS account that owns an RDS Custom for SQL Server DB instance joined to your AD domain. By dedicating an OU and service credentials, you avoid conflicting permissions and follow the principle of least privilege. Active directory level group policies might conflict with AWS automations and permissions. We recommend selecting GPO's that apply only to the OU that you create for RDS Custom for SQL Server. + To create OU and AD domain user in your self-managed or on-premise AD, you can connect the domain controller as a domain administrator. + To create users and groups in an Directory Service directory, you must be connected to a management instance and you must also be logged in as a user with privileges to create users and groups. For more information, see [User and group management in AWS Managed Microsoft AD](https://docs.aws.amazon.com/directoryservice/latest/admin-guide/ms_ad_manage_users_groups.html) in the *AWS Directory Service Administration Guide*. + To manage your Active Directory from Amazon EC2 Windows Server instance, you need to install the Active Directory domain services and Active Directory Lightweight Directory services tools on the EC2 instance. For more information, see [Installing Active Directory Administration Tools for AWS Managed Microsoft AD](https://docs.aws.amazon.com/directoryservice/latest/admin-guide/ms_ad_install_ad_tools.html) in the *AWS Directory Service Administration Guide*. + We recommend that you install these tools on a separate EC2 instance for administration, and not on your RDS Custom for SQL Server DB instance for ease of administration. The following are the requirements for an AD domain service account: + You must have a service account in your AD domain with delegated permissions to join computers to the domain. A domain service account is a user account in your AD that has delegated permission to perform certain tasks. + Delegate the following permissions to your domain service account in the Organizational Unit that you're joining your RDS Custom for SQL Server instance to: + Validated ability to write to the DNS host name + Validated ability to write to the service principal name + Create and delete computer objects + For self-managed and on-premises AD, the domain service account must be a member of the "AWS Delegated Domain Name System Administrators" group. + For AWS Managed Microsoft AD, the domain service account should be member of "DnsAdmins" group. These are the minimum set of permissions required to join computer objects to your self-managed AD and AWS Managed Microsoft AD. For more information, see [ Error: Access is denied when non-administrator users who have been delegated control try to join computers to a domain controller](https://learn.microsoft.com/en-us/troubleshoot/windows-server/active-directory/access-denied-when-joining-computers) in the Microsoft Windows Server documentation. **Important** Do not move computer objects that RDS Custom for SQL Server creates in the Organizational Unit (OU) after your DB instance is created. Moving associated objects might cause your RDS Custom for SQL Server DB instance to become misconfigured. If you need to move the computer objects created by Amazon RDS, use the [ ModifyDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html) action to modify the domain parameters with the desired location of the computer objects. **Topics** + [ ## Step 1: Create an organizational unit (OU) in your AD ](#custom-sqlserver-WinAuth.settingUp.CreateOU) + [ ## Step 2: Create an AD domain user ](#custom-sqlserver-WinAuth.settingUp.ADuser) + [ ## Step 3: Delegate control to the AD user in self-managed or AWS Managed Microsoft AD ](#custom-sqlserver-WinAuth.settingUp.Delegate) + [ ## Step 4: Create a secret ](#custom-sqlserver-WinAuth.settingUp.ASM) + [ ## Step 5: Create or modify a RDS Custom for SQL Server DB instance ](#custom-sqlserver-WinAuth.settingUp.CreateDBInstance) + [ ## Step 6: Create Windows Authentication SQL Server Login ](#custom-sqlserver-WinAuth.settingUp.CreateLogins) + [ ## Step 7: Using Kerberos or NTLM Authentication ](#custom-sqlserver-WinAuth.settingUp.KerbNTLM) ## Step 1: Create an organizational unit (OU) in your AD Use the following steps to create an organization unit in your AD: **Create an OU in your AD** 1. Connect to your domain AD as a domain administrator. 1. Open **Active Directory Users and Computers** and select the domain where you want to create your OU. 1. Right-click the domain and choose **New**, then **Organization Unit**. 1. Enter a name for the OU. Enable **Protect container from accidental deletion**. 1. Choose **OK**. Your new OU appears under your domain. For AWS Managed Microsoft AD, the name of this OU is based off the NetBIOS name you typed when you created your directory. This OU is owned by AWS and contains all of your AWS-related directory objects, which you are granted full control over. By default, two child OUs exist under this OU, namely **Computers and Users**. New OUs that RDS Custom creates are a child of the OU that is based off of the NetBIOS. ## Step 2: Create an AD domain user The domain user credentials are used for the secret in Secrets Manager. **Create an AD domain user in your AD** 1. Open **Active Directory Users and Computers** and select the domain and OU where you want to create the user. 1. Right-click the **Users** object and choose **New**, then **User**. 1. Enter a first name, last name, and login name for the user. Click **Next**. 1. Enter a password for the user. Don't select **User must change password at next login** or **Account is disabled.**. Click **Next**. 1. Click **OK**. You new user appears under your domain. ## Step 3: Delegate control to the AD user in self-managed or AWS Managed Microsoft AD **To delegate control to the AD domain user in your domain** 1. Open **Active Directory Users and Computers** MMC snap-in and select your domain. 1. Right-click on the OU you created earlier and choose **Delegate Control**. 1. In the **Delegation Control Wizard**, click **Next**. 1. In **Users or Groups** section, click **Add**. 1. In **Select Users, Computers, or Groups**, enter the AD user you created and click **Check Names**. If your AD user check is successful, click **OK**. 1. In the **Users or Groups** section, confirm your AD user was added and click **Next**. 1. In the **Tasks to Delegate** section, choose **Create a custom task to delegate** and click **Next**. 1. In the **Active Directory Object Type** section: Choose **ONly the following objects in the folder**. Select **Computer Objects** Select **Create selected objects in this folder** Select **Delete selected objects in this folder** and click **Next**. 1. In the **Permissions** section: Keep **General** selected. Select **Validated write to DNS host name**. Select **Validated write to service principal name** and click **Next**. 1. In **Completing the Delegation of Control Wizard**, confirm your settings and click **Finish**. ## Step 4: Create a secret Create the secret in the same AWS account and Region that contains the RDS Custom for SQL Server DB instance that you want to include in your active directory. Store credentials of the AD domain user created in [Step 2: Create an AD domain user](#custom-sqlserver-WinAuth.settingUp.ADuser). ------ #### [ Console ] + In AWS Secrets Manager, choose **Store a new secret**. + For **Secret type**, choose **Other type of secret**. + For **Key/value pairs**, add two keys: + The first key, `SELF_MANAGED_ACTIVE_DIRECTORY_USERNAME` and enter the name of your AD user (without the domain prefix) for the value. + For the second key, enter `SELF_MANAGED_ACTIVE_DIRECTORY_PASSWORD` and enter the password for your AD user on your domain. + For **Encryption key**, enter the same AWS KMS key you used to create RDS Custom for SQL Server instance. + For **Secret name**, choose the secret name starting with `do-not-delete-rds-custom-` to allow your instance profile to access this secret. IF you want to choose a different name for the secret, update `RDSCustomInstanceProfile` to access **Secret name**. + (Optional) For **Description**, enter a description for the secret name. + Add the tags `Key="AWSRDSCustom",Value="custom-sqlserver"` + Click **Save**, then **Next**. + For **Configure rotation settings**, keep the default values and choose **Next**. + Review the settings for the secret and click **Store**. + Choose the new secret and copy the value for **Secret ARN**. We use this in the next step to set up you Active Directory. ------ #### [ CLI ] Run the following command in your CLI to create a secret: ``` # Linux based aws secretsmanager create-secret \ --name do-not-delete-rds-custom-DomainUserCredentails \ --description "Active directory user credentials for managing RDS Custom" \ --secret-string "{\"SELF_MANAGED_ACTIVE_DIRECTORY_USERNAME\":\"tester\",\"SELF_MANAGED_ACTIVE_DIRECTORY_PASSWORD\":\"xxxxxxxx\"}" \ --kms-key-id \ --tags Key="AWSRDSCustom",Value="custom-sqlserver" # Windows based aws secretsmanager create-secret ^ --name do-not-delete-rds-custom-DomainUserCredentails ^ --description "Active directory user credentials for managing RDS Custom" ^ --secret-string "{\"SELF_MANAGED_ACTIVE_DIRECTORY_USERNAME\":\"tester\",\"SELF_MANAGED_ACTIVE_DIRECTORY_PASSWORD\":\"xxxxxxxx\"}" ^ --kms-key-id ^ --tags Key="AWSRDSCustom",Value="custom-sqlserver" ``` ------ ## Step 5: Create or modify a RDS Custom for SQL Server DB instance Create or modify a RDS Custom for SQL Server DB instance for use with your directory. You can use the console, CLI, or RDS API to associate a DB instance with a directory. You can do this in one of the following ways: + Create a new SQL Server DB instance using the console, the [create-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html) CLI command, or the [CreateDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBInstance.html) RDS API operation. For instructions, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + Modify an existing SQL Server DB instance using the console, the [modify-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html) CLI command, or the [ModifyDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html) RDS API operation. For instructions, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). + Restore a SQL Server DB instance from a DB snapshot using the console, the [restore-db-instance-from-db-snapshot](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html) CLI command, or the [RestoreDBInstanceFromDBSnapshot](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromDBSnapshot.html) RDS API operation. For instructions, see [Restoring to a DB instance](USER_RestoreFromSnapshot.md). + Restore a SQL Server DB instance to a point-in-time using the console, the [restore-db-instance-to-point-in-time](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html) CLI command, or the [RestoreDBInstanceToPointInTime](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceToPointInTime.html) RDS API operation. For instructions, see [Restoring a DB instance to a specified time for Amazon RDS](USER_PIT.md). **Note** If your RDS Custom for SQL Server instance is already joined to an AD manually, check the settings for [Network configuration port rules](custom-sqlserver-WinAuth.NWConfigPorts.md), [Network Validation](custom-sqlserver-WinAuth.NWValidation.md), and complete steps 1 though Step 4. Update the `--domain-fqdn`, `--domain-ou`, and `--domain-auth-secret-arn` to your AD, so that domain join credentials and configurations are registered with RDS Custom to monitor, register CNAME, and take recovery actions. When you use the AWS CLI, the following parameters are required for the DB instance to be able to use the directory that you created: + For the `--domain-fqdn` parameter, use the fully qualified domain name of your self-managed AD. + For the `--domain-ou` parameter, use the OU that you created in your self-managed AD. + For the `--domain-auth-secret-arn` parameter, use the value of the **Secret ARN** that you created. **Important** If you modify a DB instance to join or remove from a self-managed AD domain or AWS Managed Microsoft AD, a reboot of the DB instance is required for the modification to take effect. You can choose to apply the changes immediately or wait until the next maintenance window. Choosing the **Apply Immediately** option causes downtime for a single-AZ DB instance. A Multi-AZ DB cluster performs a failover before completing a reboot. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). The following CLI command creates a new RDS Custom for SQL Server DB instance and joins it to self-managed or AWS Managed Microsoft AD domain. For Linux, macOS, or Unix: ``` aws rds create-db-instance \ --engine custom-sqlserver-se \ --engine-version 15.00.4312.2.v1 \ --db-instance-identifier my-custom-instance \ --db-instance-class db.m5.large \ --allocated-storage 100 --storage-type io1 --iops 1000 \ --master-username my-master-username \ --master-user-password my-master-password \ --kms-key-id my-RDSCustom-key-id \ --custom-iam-instance-profile AWSRDSCustomInstanceProfileForRdsCustomInstance \ --domain-fqdn "corp.example.com" \ --domain-ou "OU=RDSCustomOU,DC=corp,DC=example,DC=com" \ --domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:do-not-delete-rds-custom-my-AD-test-secret-123456" \ --db-subnet-group-name my-DB-subnet-grp \ --vpc-security-group-ids my-securitygroup-id \ --no-publicly-accessible \ --backup-retention-period 3 \ --port 8200 \ --region us-west-2 \ --no-multi-az ``` For Windows: ``` aws rds create-db-instance ^ --engine custom-sqlserver-se ^ --engine-version 15.00.4312.2.v1 ^ --db-instance-identifier my-custom-instance ^ --db-instance-class db.m5.large ^ --allocated-storage 100 --storage-type io1 --iops 1000 ^ --master-usernamemy-master-username ^ --master-user-password my-master-password ^ --kms-key-id my-RDSCustom-key-id ^ --custom-iam-instance-profile AWSRDSCustomInstanceProfileForRdsCustomInstance ^ --domain-fqdn "corp.example.com" ^ --domain-ou "OU=RDSCustomOU,DC=corp,DC=example,DC=com" ^ --domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:do-not-delete-rds-custom-my-AD-test-secret-123456" ^ --db-subnet-group-name my-DB-subnet-grp ^ --vpc-security-group-ids my-securitygroup-id ^ --no-publicly-accessible ^ --backup-retention-period 3 ^ --port 8200 ^ --region us-west-2 ^ --no-multi-az ``` **Important** If your NetBIOS for AWS Managed Microsoft AD is **corpexample**, then it appears as an OU itself. Any new OU created earlier will appear as a nested OU. For AWS Managed Microsoft AD, set `--domain-ou` to `"OU=RDSCustomOU,OU=corpexample,DC=corp,DC=example,DC=com"`. The following command modifies an existing RDS Custom for SQL Server DB instance to use an Active Directory domain. For Linux, macOS, or Unix: ``` aws rds modify-db-instance \ --db-instance-identifier my-custom-instance \ --domain-fqdn "corp.example.com" \ --domain-ou "OU=RDSCustomOU,DC=corp,DC=example,DC=com" \ --domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:do-not-delete-rds-custom-my-AD-test-secret-123456" \ ``` For Windows: ``` aws rds modify-db-instance ^ --db-instance-identifier my-custom-instance ^ --domain-fqdn "corp.example.com" ^ --domain-ou "OU=RDSCustomOU,DC=corp,DC=example,DC=com" ^ --domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:do-not-delete-rds-custom-my-AD-test-secret-123456" ^ ``` The following CLI command removes and RDS Custom for SQL Server DB instance from a Active Directory domain. For Linux, macOS, or Unix: ``` aws rds modify-db-instance \ --db-instance-identifier my-custom-instance \ --disable-domain ``` For Windows: ``` aws rds modify-db-instance ^ --db-instance-identifier my-custom-instance ^ --disable-domain ``` When using the console to create or modify your instance, click on **Enable Microsoft SQL Server Windows Authentication** to see the following options. ![\[Microsoft SQL Server Windows Authentication directory\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/custom-sqs-WinAuth.png) You are responsible to make sure your domain FQDN is resolving to the domain controller IP addresses. If domain controller IPs are not resolving, domain join operations fail but RDS Custom for SQL Server instance creation succeeds. For troubleshooting information, see [Troubleshooting Active Directory](custom-sqlserver-WinAuth.Troubleshoot.md). ## Step 6: Create Windows Authentication SQL Server Login Use the Amazon RDS master user credentials to connect to the SQL Server DB instance as you do for any other DB instance. Because the DB instance is joined to the AD domain, you can provision SQL Server logins and users. You do this from the AD users and groups utility in your AD domain. Database permissions are managed through standard SQL Server permissions granted and revoked to these Windows logins. For an AD user to authenticate with SQL Server, a SQL Server Windows login must exist for the AD user or an Active Directory group that the user is a member of. Fine-grained access control is handled through granting and revoking permissions on these SQL Server logins. An AD user that doesn't have a SQL Server login or belong to an AD group with such a login can't access the SQL Server DB instance. The `ALTER ANY LOGIN` permission is required to create an AD SQL Server login. If you haven't created any logins with this permission, connect as the DB instance's master user using SQL Server Authentication and create your AD SQL Server logins under the context of the master user. You can run a data definition language (DDL) command such as the following to create a SQL Server login for an AD user or group. ``` USE [master] GO CREATE LOGIN [mydomain\myuser] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english]; GO ``` Users (both humans and applications) from your domain can now connect to the RDS Custom for SQL Server instance from a domain-joined client machine using Windows authentication. ## Step 7: Using Kerberos or NTLM Authentication ### NTLM authentication using RDS endpoint Each Amazon RDS DB instance has an endpoint and each endpoint has a DNS name and port number for the DB instance. To connect to your DB instance using a SQL client application, you need the DNS name and port number for your DB instance. To authenticate using NTLM authentication, you must connect to the RDS endpoint. During planned database maintenance or unplanned service disruption, Amazon RDS automatically fails over to the up-to-date secondary database so operations can resume quickly without manual intervention. The primary and secondary instances use the same endpoint, whose physical network address transitions to the secondary as part of the failover process. You don't have to reconfigure your application when a failover occurs. ### Kerberos authentication Kerberos-based authentication for RDS Custom for SQL Server requires connections be made to a specific Service Principal Name (SPN). However, after a failover event, the application might not be aware of the new SPN. To address this, RDS Custom for SQL Server offers a Kerberos-based endpoint. The Kerberos-based endpoint follows a specific format. If your RDS endpoint is `rds-instance-name.account-region-hash.aws-region.rds.amazonaws.com`, the corresponding Kerberos-based endpoint would be `rds-instance-name.account-region-hash.aws-region.awsrds.fully qualified domain name (FQDN)`. For example, if the RDS endpoint is `ad-test.cocv6zwtircu.us-east-1.rds.amazonaws.com` and the domain name is `corp-ad.company.com`, the Kerberos-based endpoint would be `ad-test.cocv6zwtircu.us-east-1.awsrds.corp-ad.company.com`. This Kerberos-based endpoint can be used to authenticate with the SQL Server instance using Kerberos, even after a failover event, as the endpoint is automatically updated to point to the new SPN of the primary SQL Server instance. ### Finding your CNAME To find your CNAME, connect to your domain controller and open **DNS Manager**. Navigate to **Forward Lookup Zones** and your FQDN. Navigate through **awsrds**, **aws-region**, and **account and region specific hash**. If you are connecting the RDS Custom EC2 instance and trying to connect to the database locally using CNAME, your connection will use NTLM authentication instead of Kerberos. If after connecting CNAME from remote client, an NTLM connection is returned, check if required ports are allowlisted. To check if your connection is using Kerberos, run the following query: ``` SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SSPID; ``` # Managing a DB instance in a Domain You can use the console, AWS CLI, or the Amazon RDS API to manage your DB instance and its relationship with your domain. For example, you can move the DB instance into, out of, or between domains. For example, using the Amazon RDS API, you can do the following: + To reattempt a domain join for a failed membership, use the [ModifyDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html) API operation and specify the current membership's directory ID. + To update the IAM role name for membership, use the `ModifyDBInstance` API operation and specify the current membership's directory ID and the new IAM role. + To remove a DB instance from a domain, use the `ModifyDBInstance` API operation and specify `none` as the domain parameter. + To move a DB instance from one domain to another, use the `ModifyDBInstance` API operation and specify the domain identifier of the new domain as the domain parameter. + To list membership for each DB instance, use the [DescribeDBInstances](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/DescribeDBInstances.html) API operation. ## Restoring a RDS Custom for SQL Server DB instance and adding it to an Active Directory domain You can restore a DB snapshot or do point-in-time recovery (PITR) for a SQL Server DB instance and then add it to an Active Directory domain. Once the DB instance is restored, modify the instance using the process explained in [Step 5: Create or modify a RDS Custom for SQL Server DB instance](custom-sqlserver-WinAuth.settingUp.md#custom-sqlserver-WinAuth.settingUp.CreateDBInstance) to add the DB instance to an AD domain. # Understanding Domain membership After you create or modify your DB instance, the instance becomes a member of the domain. The AWS console indicates the status of the domain membership for the DB instance. The status of the DB instance can be one of the following: + **joined** – The instance is a member of the domain. + **joining** – The instance is in the process of becoming a member of the domain. + **pending-join** – The instance membership is pending. + **pending-maintenance-join** – AWS will attempt to make the instance a member of the domain during the next scheduled maintenance window. + **pending-removal** – The removal of the instance from the domain is pending. + **pending-maintenance-removal** – AWS will attempt to remove the instance from the domain during the next scheduled maintenance window. + **failed** – A configuration problem has prevented the instance from joining the domain. Check and fix your configuration before reissuing the instance modify command. + **removing** – The instance is being removed from the domain. A request to become a member of a domain can fail because of a network connectivity issue or an incorrect IAM role. For example, you might create a DB instance or modify an existing instance and have the attempt fail for the DB instance to become a member of a domain. In this case, either reissue the command to create or modify the DB instance or modify the newly created instance to join the domain. # Troubleshooting Active Directory The following are issues you might encounter when you set up or modify an AD. | Error Code | Description | Common causes | Troubleshooting suggestions | | --- | --- | --- | --- | | Error 2 / 0x2 | The system cannot find the file specified. | The format or location for the Organizational Unit (OU) specified with the `—domain-ou` parameter is invalid. The domain service account specified via AWS Secrets Manager lack the permissions required to join the OU. | Review the `—domain-ou` parameter. Ensure the domain service account has the correct permissions to the OU. | | Error 5 / 0x5 | Access is denied. | Misconfigured permissions for the domain service account, or the computer account already exists in the domain. | Review the domain service account permissions in the domain, and verify that the RDS computer account is not duplicated in the domain. You can verify the name of the RDS computer account by running `SELECT @@SERVERNAME` on your RDS Custom for SQL Server DB instance. If you are using Multi-AZ, try rebooting with failover and then verify that the RDS computer account again. For more information, see [Rebooting a DB instance](USER_RebootInstance.md). | | Error 87 / 0x57 | The parameter is incorrect. | The domain service account specified via AWS Secrets Manager doesn't have the correct permissions. The user profile may also be corrupted. | Review the requirements for the domain service account. | | Error 234 / 0xEA | Specified Organizational Unit (OU) does not exist. | The OU specified with the `—domain-ou` parameter doesn't exist in your AD. | Review the `—domain-ou` parameter and ensure the specified OU exists in your AD. | | Error 1326 / 0x52E | The user name or password is incorrect. | The domain service account credentials provided in AWS Secrets Manager contains an unknown username or bad password. The domain account may also be disabled in your AD. | Ensure the credentials provided in AWS Secrets Manager are correct and the domain account is enabled in your Active Directory. | | Error 1355 / 0x54B | The specified domain either does not exist or could not be contacted. | The domain is down, the specified set of DNS IPs are unreachable, or the specified FQDN is unreachable. | Review the `—domain-dns-ips` and `—domain-fqdn` parameters to ensure they're correct. Review the networking configuration of your RDS Custom for SQL Server DB instance and ensure your AD is reachable. | | Error 1722 / 0x6BA | The RPC server is unavailable. | There was an issue reaching the RPC service of your AD domain. This might be a service or network issue. | Validate that the RPC service is running on your domain controllers and that the TCP ports `135` and `49152-65535` are reachable on your domain from your RDS Custom for SQL Server DB instance. | | Error 2224 / 0x8B0 | The user account already exists. | The computer account that's attempting to be added to your AD already exists. | Identify the computer account by running `SELECT @@SERVERNAME` on your RDS Custom for SQL Server DB instance and then carefully remove it from your AD. | | Error 2242 / 0x8c2 | The password of this user has expired. | The password for the domain service account specified via AWS Secrets Manager has expired. | Update the password for the domain service account used to join your RDS Custom for SQL Server DB instance to your AD. |