# Working with Active Directory with RDS for SQL Server You can join an RDS for SQL Server DB instance to a Microsoft Active Directory (AD) domain. Your AD domain can be hosted on AWS Managed AD within AWS, or on a self-managed AD in a location of your choice, including your corporate data centers, on AWS EC2, or with other cloud providers. You can authenticate domain users using NTLM authentication and Kerberos authentication with self-managed Active Directory and AWS Managed Microsoft AD. In the following sections, you can find information about working with self-managed Active Directory and AWS Managed Active Directory for Microsoft SQL Server on Amazon RDS. **Topics** + [ # Working with self-managed Active Directory with an Amazon RDS for SQL Server DB instance ](USER_SQLServer_SelfManagedActiveDirectory.md) + [ # Working with AWS Managed Active Directory with RDS for SQL Server ](USER_SQLServerWinAuth.md) # Working with self-managed Active Directory with an Amazon RDS for SQL Server DB instance Amazon RDS for SQL Server seamlessly integrates with your self-managed Active Directory (AD) domain, regardless of where your AD is hosted - whether in your data center, on Amazon EC2, or with other cloud providers. This integration enables direct user authentication through NTLM or Kerberos protocols, eliminating the need for complex intermediary domains or forest trusts. When you connect to your RDS SQL Server DB instance, authentication requests are securely forwarded to your designated AD domain, maintaining your existing identity management structure while leveraging Amazon RDS's managed database capabilities. **Topics** + [ ## Region and version availability ](#USER_SQLServer_SelfManagedActiveDirectory.RegionVersionAvailability) + [ # Requirements ](USER_SQLServer_SelfManagedActiveDirectory.Requirements.md) + [ ## Considerations ](#USER_SQLServer_SelfManagedActiveDirectory.Limitations) + [ # Setting up self-managed Active Directory ](USER_SQLServer_SelfManagedActiveDirectory.SettingUp.md) + [ # Joining your DB instance to self-managed Active Directory ](USER_SQLServer_SelfManagedActiveDirectory.Joining.md) + [ # Managing a DB instance in a self-managed Active Directory Domain ](USER_SQLServer_SelfManagedActiveDirectory.Managing.md) + [ ## Understanding self-managed Active Directory Domain membership ](#USER_SQLServer_SelfManagedActiveDirectory.Understanding) + [ # Troubleshooting self-managed Active Directory ](USER_SQLServer_SelfManagedActiveDirectory.TroubleshootingSelfManagedActiveDirectory.md) + [ ## Restoring a SQL Server DB instance and then adding it to a self-managed Active Directory domain ](#USER_SQLServer_SelfManagedActiveDirectory.Restore) ## Region and version availability Amazon RDS supports self-managed AD for SQL Server using NTLM and Kerberos in all commercial AWS Regions and AWS GovCloud (US) Regions. # Requirements Make sure you've met the following requirements before joining an RDS for SQL Server DB instance to your self-managed AD domain. **Topics** + [ ## Configure your on-premises AD ](#USER_SQLServer_SelfManagedActiveDirectory.Requirements.OnPremConfig) + [ ## Configure your network connectivity ](#USER_SQLServer_SelfManagedActiveDirectory.Requirements.NetworkConfig) + [ ## Configure your AD domain service account ](#USER_SQLServer_SelfManagedActiveDirectory.Requirements.DomainAccountConfig) + [ ## Configuring secure communication over LDAPS ](#USER_SQLServer_SelfManagedActiveDirectory.Requirements.LDAPS) ## Configure your on-premises AD Make sure that you have an on-premises or other self-managed Microsoft AD that you can join the Amazon RDS for SQL Server instance to. Your on-premises AD should have the following configuration: + If you have AD sites defined, make sure the subnets in the VPC associated with your RDS for SQL Server DB instance are defined in your AD site. Confirm there aren't any conflicts between the subnets in your VPC and the subnets in your other 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 Label Domain (SLD) format. RDS 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 Make sure that you have met the following network configurations: + Configure connectivity between the Amazon VPC where you want to create the RDS for SQL Server DB instance and your self-managed AD. You can set up connectivity using AWS Direct Connect, AWS VPN, VPC peering, or AWS Transit Gateway. + For VPC security groups, the default security group for your default Amazon VPC is already added to your RDS for SQL Server DB instance in the console. Ensure that the security group and the VPC network ACLs for the subnet(s) where you're creating your RDS for SQL Server DB instance allow traffic on the ports and in the directions shown in the following diagram. ![\[Self-managed AD network configuration port rules.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/SQLServer_SelfManagedActiveDirectory_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/USER_SQLServer_SelfManagedActiveDirectory.Requirements.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 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 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. ## Configure your AD domain service account Make sure that you have met the following requirements for an AD domain service account: + Make sure that you have a domain service account in your self-managed AD domain with delegated permissions to join computers to the domain. A domain service account is a user account in your self-managed AD that has been delegated permission to perform certain tasks. + The domain service account needs to be delegated the following permissions in the Organizational Unit (OU) that you're joining your RDS for SQL Server DB 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 These represent the minimum set of permissions that are required to join computer objects to your self-managed AD. For more information, see [Errors when attempting to join computers to a domain](https://learn.microsoft.com/en-US/troubleshoot/windows-server/identity/access-denied-when-joining-computers) in the Microsoft Windows Server documentation. + To use Kerberos authentication, you need to provide Service Principal Names (SPNs) and DNS permissions to your AD domain service account: + **Write SPN**: Delegate the **Write SPN** permission to the AD domain service account in the OU where you need to join the RDS for SQL Server DB instance. This permissions is different from the validated write SPN. + **DNS permissions**: Provide the following permissions to the AD domain service account in the DNS manager at the server level for your domain controller: + List contents + Read all properties + Read permissions **Important** Do not move computer objects that RDS for SQL Server creates in the Organizational Unit after your DB instance is created. Moving the associated objects will cause your RDS 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) RDS API operation to modify the domain parameters with the desired location of the computer objects. ## Configuring secure communication over LDAPS Communication over LDAPS is recommended for RDS to query and access computer objects as well as SPNs in the domain controller. To use secure LDAP, use a valid SSL certificate on your domain controller that meets the requirements for secure LDAPS. If a valid SSL certificate does not exist on the domain controller, the RDS for SQL Server DB instance defaults to using LDAP. For more information on certificate validity, see [ Requirements for an LDAPS certificate](https://learn.microsoft.com/en-us/troubleshoot/windows-server/active-directory/enable-ldap-over-ssl-3rd-certification-authority#requirements-for-an-ldaps-certificate). ## Considerations When adding an RDS for SQL Server DB instance to a self-managed AD, keep the consider the following: + Your DB instances sync with AWS's NTP service and not the AD domain's time server. For database connections between linked SQL Server instances within your AD domain, you can only SQL authentication and not Windows authentication. + Group Policy Object settings from your self-managed AD domain are not propagated to your RDS for SQL Server instances. # Setting up self-managed Active Directory To set up a self-managed AD, take the following steps. **Topics** + [ ## Step 1: Create an Organizational Unit in your AD ](#USER_SQLServer_SelfManagedActiveDirectory.SettingUp.CreateOU) + [ ## Step 2: Create an AD domain service account in your AD ](#USER_SQLServer_SelfManagedActiveDirectory.SettingUp.CreateADuser) + [ ## Step 3: Delegate control to the AD domain service account ](#USER_SQLServer_SelfManagedActiveDirectory.SettingUp.DelegateControl) + [ ## Step 4: Create an AWS KMS key ](#USER_SQLServer_SelfManagedActiveDirectory.SettingUp.CreateKMSkey) + [ ## Step 5: Create an AWS secret ](#USER_SQLServer_SelfManagedActiveDirectory.SettingUp.CreateSecret) ## Step 1: Create an Organizational Unit in your AD **Important** We recommend creating a dedicated OU and service credential scoped to that OU for any AWS account that owns an RDS for SQL Server DB instance joined your self-managed AD domain. By dedicating an OU and service credential, you can avoid conflicting permissions and follow the principal of least privilege. **To create an OU in your AD** 1. Connect to your AD domain 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 **Organizational Unit**. 1. Enter a name for the OU. 1. Keep the box selected for **Protect container from accidental deletion**. 1. Click **OK**. Your new OU will appear under your domain. ## Step 2: Create an AD domain service account in your AD The domain service account credentials will be used for the secret in AWS Secrets Manager. **To create an AD domain service account in your AD** 1. Open **Active Directory Users and Computers** and select the domain and OU where you want to create your user. 1. Right-click the **Users** object and choose **New**, then **User**. 1. Enter a first name, last name, and logon name for the user. Click **Next**. 1. Enter a password for the user. Don't select **"User must change password at next login"**. Don't select **"Account is disabled"**. Click **Next**. 1. Click **OK**. Your new user will appear under your domain. ## Step 3: Delegate control to the AD domain service account **To delegate control to the AD domain service account in your domain** 1. Open **Active Directory Users and Computers** MMC snap-in and select the domain where you want to create your user. 1. Right-click the OU that you created earlier and choose **Delegate Control**. 1. On the **Delegation of Control Wizard**, click **Next**. 1. On the **Users or Groups** section, click **Add**. 1. On the **Select Users, Computers, or Groups** section, enter the AD domain service account you created and click **Check Names**. If your AD domain service account check is successful, click **OK**. 1. On the **Users or Groups** section, confirm your AD domain service account was added and click **Next**. 1. On the **Tasks to Delegate** section, choose **Create a custom task to delegate** and click **Next**. 1. On the **Active Directory Object Type** section: 1. Choose **Only the following objects in the folder**. 1. Select **Computer Objects**. 1. Select **Create selected objects in this folder**. 1. Select **Delete selected objects in this folder** and click **Next**. 1. On the **Permissions** section: 1. Keep **General** selected. 1. Select **Validated write to DNS host name**. 1. Select **Validated write to service principal name** and click **Next**. 1. To enable Kerberos authentication, keep **Property-specific** selected and select **Write servicePrincipalName** from the list. 1. For **Completing the Delegation of Control Wizard**, review and confirm your settings and click **Finish**. 1. For Kerberos authentication, open the DNS Manager and open **Server** properties. 1. In the Windows dialog box, type `dnsmgmt.msc`. 1. Add the AD domain service account under the **Security** tab. 1. Select the **Read** permission and apply your changes. ## Step 4: Create an AWS KMS key The KMS key is used to encrypt your AWS secret. **To create an AWS KMS key** **Note** For **Encryption Key**, don't use the AWS default KMS key. Be sure to create the AWS KMS key in the same AWS account that contains the RDS for SQL Server DB instance that you want to join to your self-managed AD. 1. In the AWS KMS console, choose **Create key**. 1. For **Key Type**, choose **Symmetric**. 1. For **Key Usage**, choose **Encrypt and decrypt**. 1. For **Advanced options**: 1. For **Key material origin**, choose **KMS**. 1. For **Regionality**, choose **Single-Region key** and click **Next**. 1. For **Alias**, provide a name for the KMS key. 1. (Optional) For **Description**, provide a description of the KMS key. 1. (Optional) For **Tags**, provide a tag the KMS key and click **Next**. 1. For **Key administrators**, provide the name of an IAM user and select it. 1. For **Key deletion**, keep the box selected for **Allow key administrators to delete this key** and click **Next**. 1. For **Key users**, provide the same IAM user from the previous step and select it. Click **Next**. 1. Review the configuration. 1. For **Key policy**, include the following to the policy **Statement**: ``` { "Sid": "Allow use of the KMS key on behalf of RDS", "Effect": "Allow", "Principal": { "Service": [ "rds.amazonaws.com" ] }, "Action": "kms:Decrypt", "Resource": "*" } ``` 1. Click **Finish**. ## Step 5: Create an AWS secret **To create a secret** **Note** Be sure to create the secret in the same AWS account that contains the RDS for SQL Server DB instance that you want to join to your self-managed AD. 1. In AWS Secrets Manager, choose **Store a new secret**. 1. For **Secret type**, choose **Other type of secret**. 1. For **Key/value pairs**, add your two keys: 1. For the first key, enter `SELF_MANAGED_ACTIVE_DIRECTORY_USERNAME`. 1. For the value of the first key, enter only the username (without the domain prefix) of the AD user. Do not include the domain name as this causes instance creation to fail. 1. For the second key, enter `SELF_MANAGED_ACTIVE_DIRECTORY_PASSWORD`. 1. For the value of the second key, enter the password that you created for the AD user on your domain. 1. For **Encryption key**, enter the KMS key that you created in a previous step and click **Next**. 1. For **Secret name**, enter a descriptive name that helps you find your secret later. 1. (Optional) For **Description**, enter a description for the secret name. 1. For **Resource permission**, click **Edit**. 1. Add the following policy to the permission policy: **Note** We recommend that you use the `aws:sourceAccount` and `aws:sourceArn` conditions in the policy to avoid the *confused deputy* problem. Use your AWS account for `aws:sourceAccount` and the RDS for SQL Server DB instance ARN for `aws:sourceArn`. For more information, see [Preventing cross-service confused deputy problems](cross-service-confused-deputy-prevention.md). ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "rds.amazonaws.com" }, "Action": "secretsmanager:GetSecretValue", "Resource": "*", "Condition": { "StringEquals": { "aws:sourceAccount": "123456789012" }, "ArnLike": { "aws:sourceArn": "arn:aws:rds:us-west-2:123456789012:db:*" } } } ] } ``` ------ 1. Click **Save** then click **Next**. 1. For **Configure rotation settings**, keep the default values and choose **Next**. 1. Review the settings for the secret and click **Store**. 1. Choose the secret you created and copy the value for the **Secret ARN**. This will be used in the next step to set up self-managed Active Directory. # Joining your DB instance to self-managed Active Directory To join your RDS for SQL Server DB instance to your self-managed AD, follow these steps: ## Step 1: Create or modify a SQL Server DB instance You can use the console, CLI, or RDS API to associate an RDS for SQL Server DB instance with a self-managed AD domain. 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). When you use the AWS CLI, the following parameters are required for the DB instance to be able to use the self-managed AD domain that you created: + For the `--domain-fqdn` parameter, use the fully qualified domain name (FQDN) 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 in a previous step. + For the `--domain-dns-ips` parameter, use the primary and secondary IPv4 addresses of the DNS servers for your self-managed AD. If you don't have a secondary DNS server IP address, enter the primary IP address twice. The following example CLI commands show how to create, modify, and remove an RDS for SQL Server DB instance with a self-managed AD domain. **Important** If you modify a DB instance to join it to or remove it from a self-managed AD domain, 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 will cause downtime for a single-AZ DB instance. A multi-AZ DB instance will perform a failover before completing a reboot. For more information, see [Using the schedule modifications setting](USER_ModifyInstance.ApplyImmediately.md). The following CLI command creates a new RDS for SQL Server DB instance and joins it to a self-managed AD domain. For Linux, macOS, or Unix: ``` aws rds create-db-instance \ --db-instance-identifier my-DB-instance \ --db-instance-class db.m5.xlarge \ --allocated-storage 50 \ --engine sqlserver-se \ --engine-version 15.00.4043.16.v1 \ --license-model license-included \ --master-username my-master-username \ --master-user-password my-master-password \ --domain-fqdn my_AD_domain.my_AD.my_domain \ --domain-ou OU=my-AD-test-OU,DC=my-AD-test,DC=my-AD,DC=my-domain \ --domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:my-AD-test-secret-123456" \ --domain-dns-ips "10.11.12.13" "10.11.12.14" ``` For Windows: ``` aws rds create-db-instance ^ --db-instance-identifier my-DB-instance ^ --db-instance-class db.m5.xlarge ^ --allocated-storage 50 ^ --engine sqlserver-se ^ --engine-version 15.00.4043.16.v1 ^ --license-model license-included ^ --master-username my-master-username ^ --master-user-password my-master-password ^ --domain-fqdn my-AD-test.my-AD.mydomain ^ --domain-ou OU=my-AD-test-OU,DC=my-AD-test,DC=my-AD,DC=my-domain ^ --domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:my-AD-test-secret-123456" \ ^ --domain-dns-ips "10.11.12.13" "10.11.12.14" ``` The following CLI command modifies an existing RDS for SQL Server DB instance to use a self-managed AD domain. For Linux, macOS, or Unix: ``` aws rds modify-db-instance \ --db-instance-identifier my-DB-instance \ --domain-fqdn my_AD_domain.my_AD.my_domain \ --domain-ou OU=my-AD-test-OU,DC=my-AD-test,DC=my-AD,DC=my-domain \ --domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:my-AD-test-secret-123456" \ --domain-dns-ips "10.11.12.13" "10.11.12.14" ``` For Windows: ``` aws rds modify-db-instance ^ --db-instance-identifier my-DBinstance ^ --domain-fqdn my_AD_domain.my_AD.my_domain ^ --domain-ou OU=my-AD-test-OU,DC=my-AD-test,DC=my-AD,DC=my-domain ^ --domain-auth-secret-arn "arn:aws:secretsmanager:region:account-number:secret:my-AD-test-secret-123456" ^ --domain-dns-ips "10.11.12.13" "10.11.12.14" ``` The following CLI command removes an RDS for SQL Server DB instance from a self-managed AD domain. For Linux, macOS, or Unix: ``` aws rds modify-db-instance \ --db-instance-identifier my-DB-instance \ --disable-domain ``` For Windows: ``` aws rds modify-db-instance ^ --db-instance-identifier my-DB-instance ^ --disable-domain ``` ## Step 2: Using Kerberos or NTLM Authentication ### NTLM authentication 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 or the listener endpoint if you are using a Multi-AZ deployment. 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 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 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**. ![\[Modify the amount of storage for a DB instance\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/kerb-endpoint-selfManagedAD-RDSMS.png) 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; ``` If your instance returns an NTLM connection when you connect to a Kerberos endpoint, verify your network configuration and user configurations. See [Configure your network connectivity](USER_SQLServer_SelfManagedActiveDirectory.Requirements.md#USER_SQLServer_SelfManagedActiveDirectory.Requirements.NetworkConfig). ## Step 3: Create Windows Authentication SQL Server logins 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 self-managed AD domain, you can provision SQL Server logins and users. You do this from the AD users and groups utility in your self-managed AD domain. Database permissions are managed through standard SQL Server permissions granted and revoked to these Windows logins. In order for a self-managed AD domain service account to authenticate with SQL Server, a SQL Server Windows login must exist for the self-managed AD domain service account or a self-managed AD group that the user is a member of. Fine-grained access control is handled through granting and revoking permissions on these SQL Server logins. A self-managed AD domain service account that doesn't have a SQL Server login or belong to a self-managed AD group with such a login can't access the SQL Server DB instance. The ALTER ANY LOGIN permission is required to create a self-managed 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 self-managed 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 self-managed AD domain service account or group. **Note** Specify users and groups using the pre-Windows 2000 login name in the format `my_AD_domain\my_AD_domain_user`. You can't use a user principal name (UPN) in the format *`my_AD_domain_user`*`@`*`my_AD_domain`*. ``` USE [master] GO CREATE LOGIN [my_AD_domain\my_AD_domain_user] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english]; GO ``` For more information, see [CREATE LOGIN (Transact-SQL)](https://msdn.microsoft.com/en-us/library/ms189751.aspx) in the Microsoft Developer Network documentation. Users (both humans and applications) from your domain can now connect to the RDS for SQL Server instance from a self-managed AD domain-joined client machine using Windows authentication. # Managing a DB instance in a self-managed Active Directory Domain You can use the console, AWS CLI, or the Amazon RDS API to manage your DB instance and its relationship with your self-managed AD 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 self-managed 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 same set of parameters: + `--domain-fqdn` + `--domain-dns-ips` + `--domain-ou` + `--domain-auth-secret-arn` + To remove a DB instance from a self-managed domain, use the `ModifyDBInstance` API operation and specify `--disable-domain` for the domain parameter. + To move a DB instance from one self-managed domain to another, use the `ModifyDBInstance` API operation and specify the domain parameters for the new domain: + `--domain-fqdn` + `--domain-dns-ips` + `--domain-ou` + `--domain-auth-secret-arn` + To list self-managed AD domain membership for each DB instance, use the [DescribeDBInstances](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/DescribeDBInstances.html) API operation. ## Understanding self-managed Active Directory Domain membership After you create or modify your DB instance while specifying AD details, the instance becomes a member of the self-managed AD domain. The AWS console indicates the status of the self-managed Active Directory 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 AD domain. + **joining** – The instance is in the process of becoming a member of the AD domain. + **pending-join** – The instance membership is pending. + **pending-maintenance-join** – AWS will attempt to make the instance a member of the AD domain during the next scheduled maintenance window. + **pending-removal** – The removal of the instance from the AD domain is pending. + **pending-maintenance-removal** – AWS will attempt to remove the instance from the AD domain during the next scheduled maintenance window. + **failed** – A configuration problem has prevented the instance from joining the AD domain. Check and fix your configuration before reissuing the instance modify command. + **removing** – The instance is being removed from the self-managed AD domain. **Important** A request to become a member of a self-managed AD domain can fail because of a network connectivity issue. 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 self-managed AD domain. In this case, either reissue the command to create or modify the DB instance or modify the newly created instance to join the self-managed AD domain. # Troubleshooting self-managed Active Directory The following are issues you might encounter when you set up or modify self-managed 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. For more information, see [Configure your AD domain service account](USER_SQLServer_SelfManagedActiveDirectory.Requirements.md#USER_SQLServer_SelfManagedActiveDirectory.Requirements.DomainAccountConfig). | | 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 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. For more information, see [Configure your AD domain service account](USER_SQLServer_SelfManagedActiveDirectory.Requirements.md#USER_SQLServer_SelfManagedActiveDirectory.Requirements.DomainAccountConfig). | | Error 234 / 0xEA | Specified Organizational Unit (OU) does not exist. | The OU specified with the `—domain-ou` parameter doesn't exist in your self-managed AD. | Review the `—domain-ou` parameter and ensure the specified OU exists in your self-managed 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 self-managed AD. | Ensure the credentials provided in AWS Secrets Manager are correct and the domain account is enabled in your self-managed AD. | | 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 for SQL Server DB instance and ensure your self-managed AD is reachable. For more information, see [Configure your network connectivity](USER_SQLServer_SelfManagedActiveDirectory.Requirements.md#USER_SQLServer_SelfManagedActiveDirectory.Requirements.NetworkConfig). | | 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 for SQL Server DB instance. | | Error 1727 / 0x6BF | The remote procedure call failed and did not execute. | Network connectivity issue or firewall restriction blocking RPC communication to the domain controller. | If using Cross VPC domain join, validate Cross VPC communication is setup correctly with either VPC peering or Transit Gateway. Ensure TCP high ports `49152-65535` are reachable on your domain from your RDS for SQL Server DB instance, including any possible firewall restrictions. | | Error 2224 / 0x8B0 | The user account already exists. | The computer account that's attempting to be added to your self-managed AD already exists. | Identify the computer account by running `SELECT @@SERVERNAME` on your RDS for SQL Server DB instance and then carefully remove it from your self-managed 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 for SQL Server DB instance to your self-managed AD. | After joining your DB instance to a self-managed Active Directory domain, you might receive RDS events related to your domain health. ``` Unhealthy domain state detected while attempt to verify or configure your Kerberos endpoint in your domain on node node_n. message ``` For Multi-AZ instances, you might notice the error reporting for both node1 and node2, which indicates your instance's Kerberos configuration is not ready for failover. In the event of a failover, you might experience authentication difficulties using Kerberos. Resolve the configuration issues to ensure Kerberos setup is valid and up to date. For Multi-AZ instances, no actions are required to use Kerberos authentication on the new primary host given all network and permission configurations are in place. For Single-AZ instances, node1 is the primary node. If your Kerberos authentication is not working as expected, check the instance events and resolve the configuration issues to ensure Kerberos setup is valid and up to date. ## Restoring a SQL Server DB instance and then adding it to a self-managed 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 a self-managed Active Directory domain. Once the DB instance is restored, modify the instance using the process explained in [Step 1: Create or modify a SQL Server DB instance](USER_SQLServer_SelfManagedActiveDirectory.Joining.md#USER_SQLServer_SelfManagedActiveDirectory.SettingUp.CreateModify) to add the DB instance to a self-managed AD domain. # Working with AWS Managed Active Directory with RDS for SQL Server You can use AWS Managed Microsoft AD to authenticate users with Windows Authentication when they connect to your RDS for SQL Server DB instance. The DB instance works with AWS Directory Service for Microsoft Active Directory, also called AWS Managed Microsoft AD, to enable Windows Authentication. When users authenticate with a SQL Server DB instance joined to the trusting domain, authentication requests are forwarded to the domain directory that you create with Directory Service. ## Region and version availability Amazon RDS supports using only AWS Managed Microsoft AD for Windows Authentication. RDS doesn't support using AD Connector. For more information, see the following: + [Application compatibility policy for AWS Managed Microsoft AD](https://docs.aws.amazon.com/directoryservice/latest/admin-guide/ms_ad_app_compatibility.html) + [Application compatibility policy for AD Connector](https://docs.aws.amazon.com/directoryservice/latest/admin-guide/ad_connector_app_compatibility.html) For information on version and Region availability, see [Kerberos authentication with RDS for SQL Server](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.RDS_Fea_Regions_DB-eng.Feature.KerberosAuthentication.html#Concepts.RDS_Fea_Regions_DB-eng.Feature.KerberosAuthentication.sq). ## Overview of setting up Windows authentication Amazon RDS uses mixed mode for Windows Authentication. This approach means that the *master user* (the name and password used to create your SQL Server DB instance) uses SQL Authentication. Because the master user account is a privileged credential, you should restrict access to this account. To get Windows Authentication using an on-premises or self-hosted Microsoft Active Directory, create a forest trust. The trust can be one-way or two-way. For more information on setting up forest trusts using Directory Service, see [When to create a trust relationship](https://docs.aws.amazon.com/directoryservice/latest/admin-guide/ms_ad_setup_trust.html) in the *AWS Directory Service Administration Guide*. To set up Windows authentication for a SQL Server DB instance, do the following steps, explained in greater detail in [Setting up Windows Authentication for SQL Server DB instances](USER_SQLServerWinAuth.SettingUp.md): 1. Use AWS Managed Microsoft AD, either from the AWS Management Console or Directory Service API, to create an AWS Managed Microsoft AD directory. 1. If you use the AWS CLI or Amazon RDS API to create your SQL Server DB instance, create an AWS Identity and Access Management (IAM) role. This role uses the managed IAM policy `AmazonRDSDirectoryServiceAccess` and allows Amazon RDS to make calls to your directory. If you use the console to create your SQL Server DB instance, AWS creates the IAM role for you. For the role to allow access, the AWS Security Token Service (AWS STS) endpoint must be activated in the AWS Region for your AWS account. AWS STS endpoints are active by default in all AWS Regions, and you can use them without any further actions. For more information, see [Managing AWS STS in an AWS Region](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_temp_enable-regions.html) in the *IAM User Guide*. 1. Create and configure users and groups in the AWS Managed Microsoft AD directory using the Microsoft Active Directory tools. For more information about creating users and groups in your Active Directory, see [Manage users and groups 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*. 1. If you plan to locate the directory and the DB instance in different VPCs, enable cross-VPC traffic. 1. Use Amazon RDS to create a new SQL Server DB instance either from the console, AWS CLI, or Amazon RDS API. In the create request, you provide the domain identifier ("`d-*`" identifier) that was generated when you created your directory and the name of the role you created. You can also modify an existing SQL Server DB instance to use Windows Authentication by setting the domain and IAM role parameters for the DB instance. 1. Use the Amazon RDS master user credentials to connect to the SQL Server DB instance as you do any other DB instance. Because the DB instance is joined to the AWS Managed Microsoft AD domain, you can provision SQL Server logins and users from the Active Directory users and groups in their domain. (These are known as SQL Server "Windows" logins.) Database permissions are managed through standard SQL Server permissions granted and revoked to these Windows logins. When you create a domain-connected RDS for SQL Server DB instance using the Amazon RDS console, AWS automatically creates the `rds-directoryservice-access-role` IAM role. This role is essential for managing domain-connected instances and is required for the following operations: + Making configuration changes to domain-connected SQL Server instances + Managing Active Directory integration settings + Performing maintenance operations on domain-joined instances **Important** If you delete the `rds-directoryservice-access-role` IAM role, you can't make changes to your domain-connected SQL Server instance through the Amazon RDS console or API. Attempting to modify the instance results in an error message stating: You don't have permission to iam:CreateRole. To request access, copy the following text and send it to your AWS administrator. This error occurs because Amazon RDS needs to recreate the role to manage the domain connection, but lacks the necessary permissions. Additionally, this error is not logged in CloudTrail, which can make troubleshooting more difficult. If you accidentally delete the `rds-directoryservice-access-role`, you must have `iam:CreateRole` permissions to recreate it before you can make any changes to your domain-connected SQL Server instance. To recreate the role manually, ensure it has the `AmazonRDSDirectoryServiceAccess` managed policy attached and the appropriate trust relationship that allows the RDS service to assume the role. # Creating the endpoint for Kerberos authentication Kerberos-based authentication requires that the endpoint be the customer-specified host name, a period, and then the fully qualified domain name (FQDN). For example, the following is an example of an endpoint you might use with Kerberos-based authentication. In this example, the SQL Server DB instance host name is `ad-test` and the domain name is `corp-ad.company.com`. ``` ad-test.corp-ad.company.com ``` If you want to make sure your connection is using Kerberos, run the following query: ``` 1. SELECT net_transport, auth_scheme 2. FROM sys.dm_exec_connections 3. WHERE session_id = @@SPID; ``` # Setting up Windows Authentication for SQL Server DB instances You use AWS Directory Service for Microsoft Active Directory, also called AWS Managed Microsoft AD, to set up Windows Authentication for a SQL Server DB instance. To set up Windows Authentication, take the following steps. ## Step 1: Create a directory using the AWS Directory Service for Microsoft Active Directory Directory Service creates a fully managed, Microsoft Active Directory in the AWS Cloud. When you create an AWS Managed Microsoft AD directory, Directory Service creates two domain controllers and Domain Name Service (DNS) servers on your behalf. The directory servers are created in two subnets in two different Availability Zones within a VPC. This redundancy helps ensure that your directory remains accessible even if a failure occurs. When you create an AWS Managed Microsoft AD directory, Directory Service performs the following tasks on your behalf: + Sets up a Microsoft Active Directory within the VPC. + Creates a directory administrator account with the user name Admin and the specified password. You use this account to manage your directory. + Creates a security group for the directory controllers. When you launch an AWS Directory Service for Microsoft Active Directory, AWS creates an Organizational Unit (OU) that contains all your directory's objects. This OU, which has the NetBIOS name that you typed when you created your directory, is located in the domain root. The domain root is owned and managed by AWS. The *admin* account that was created with your AWS Managed Microsoft AD directory has permissions for the most common administrative activities for your OU: + Create, update, or delete users, groups, and computers. + Add resources to your domain such as file or print servers, and then assign permissions for those resources to users and groups in your OU. + Create additional OUs and containers. + Delegate authority. + Create and link group policies. + Restore deleted objects from the Active Directory Recycle Bin. + Run AD and DNS Windows PowerShell modules on the Active Directory Web Service. The admin account also has rights to perform the following domain-wide activities: + Manage DNS configurations (add, remove, or update records, zones, and forwarders). + View DNS event logs. + View security event logs. **To create a directory with AWS Managed Microsoft AD** 1. In the [Directory Service console](https://console.aws.amazon.com/directoryservicev2/) navigation pane, choose **Directories** and choose **Set up directory**. 1. Choose **AWS Managed Microsoft AD**. This is the only option currently supported for use with Amazon RDS. 1. Choose **Next**. 1. On the **Enter directory information** page, provide the following information: **Edition** Choose the edition that meets your requirements. **Directory DNS name** The fully qualified name for the directory, such as `corp.example.com`. Names longer than 47 characters aren't supported by SQL Server. **Directory NetBIOS name** An optional short name for the directory, such as `CORP`. **Directory description** An optional description for the directory. **Admin password** The password for the directory administrator. The directory creation process creates an administrator account with the user name Admin and this password. The directory administrator password can't include the word `admin`. The password is case-sensitive and must be 8–64 characters in length. It must also contain at least one character from three of the following four categories: + Lowercase letters (a-z) + Uppercase letters (A-Z) + Numbers (0-9) + Non-alphanumeric characters (\$1\$1@\$1\$1%^&\$1\$1-\$1=`\$1\$1()\$1\$1[]:;"'<>,.?/) **Confirm password** Retype the administrator password. 1. Choose **Next**. 1. On the **Choose VPC and subnets** page, provide the following information: **VPC** Choose the VPC for the directory. You can locate the directory and the DB instance in different VPCs, but if you do so, make sure to enable cross-VPC traffic. For more information, see [Step 4: Enable cross-VPC traffic between the directory and the DB instance](#USER_SQLServerWinAuth.SettingUp.VPC-Peering). **Subnets** Choose the subnets for the directory servers. The two subnets must be in different Availability Zones. 1. Choose **Next**. 1. Review the directory information. If changes are needed, choose **Previous**. When the information is correct, choose **Create directory**. ![\[Review and create page\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/WinAuth2.png) It takes several minutes for the directory to be created. When it has been successfully created, the **Status** value changes to **Active**. To see information about your directory, choose the directory ID in the directory listing. Make a note of the **Directory ID**. You need this value when you create or modify your SQL Server DB instance. ![\[Directory details page\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/WinAuth3.png) ## Step 2: Create the IAM role for use by Amazon RDS If you use the console to create your SQL Server DB instance, you can skip this step. If you use the CLI or RDS API to create your SQL Server DB instance, you must create an IAM role that uses the `AmazonRDSDirectoryServiceAccess` managed IAM policy. This role allows Amazon RDS to make calls to the Directory Service for you. If you are using a custom policy for joining a domain, rather than using the AWS-managed `AmazonRDSDirectoryServiceAccess` policy, make sure that you allow the `ds:GetAuthorizedApplicationDetails` action. This requirement is effective starting July 2019, due to a change in the Directory Service API. The following IAM policy, `AmazonRDSDirectoryServiceAccess`, provides access to Directory Service. **Example IAM policy for providing access to Directory Service** **** ``` { "Version":"2012-10-17", "Statement": [ { "Action": [ "ds:DescribeDirectories", "ds:AuthorizeApplication", "ds:UnauthorizeApplication", "ds:GetAuthorizedApplicationDetails" ], "Effect": "Allow", "Resource": "*" } ] } ``` We recommend using the [https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourcearn](https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourcearn) and [https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourceaccount](https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourceaccount) global condition context keys in resource-based trust relationships to limit the service's permissions to a specific resource. This is the most effective way to protect against the [confused deputy problem](https://docs.aws.amazon.com/IAM/latest/UserGuide/confused-deputy.html). You might use both global condition context keys and have the `aws:SourceArn` value contain the account ID. In this case, the `aws:SourceAccount` value and the account in the `aws:SourceArn` value must use the same account ID when used in the same statement. + Use `aws:SourceArn` if you want cross-service access for a single resource. + Use `aws:SourceAccount` if you want to allow any resource in that account to be associated with the cross-service use. In the trust relationship, make sure to use the `aws:SourceArn` global condition context key with the full Amazon Resource Name (ARN) of the resources accessing the role. For Windows Authentication, make sure to include the DB instances, as shown in the following example. **Example trust relationship with global condition context key for Windows Authentication** **** ``` { "Version":"2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "rds.amazonaws.com" }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "aws:SourceArn": [ "arn:aws:rds:Region:my_account_ID:db:db_instance_identifier" ] } } } ] } ``` Create an IAM role using this IAM policy and trust relationship. For more information about creating IAM roles, see [Creating customer managed policies](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_managed-using.html#create-managed-policy-console) in the *IAM User Guide*. ## Step 3: Create and configure users and groups You can create users and groups with the Active Directory Users and Computers tool. This tool is one of the Active Directory Domain Services and Active Directory Lightweight Directory Services tools. Users represent individual people or entities that have access to your directory. Groups are very useful for giving or denying privileges to groups of users, rather than having to apply those privileges to each individual user. To create users and groups in an Directory Service directory, you must be connected to a Windows EC2 instance that is a member of the Directory Service directory. You must also be logged in as a user that has privileges to create users and groups. For more information, see [Add users and groups (Simple AD and AWS Managed Microsoft AD)](https://docs.aws.amazon.com/directoryservice/latest/admin-guide/creating_ad_users_and_groups.html) in the *AWS Directory Service Administration Guide*. ## Step 4: Enable cross-VPC traffic between the directory and the DB instance If you plan to locate the directory and the DB instance in the same VPC, skip this step and move on to [Step 5: Create or modify a SQL Server DB instance](#USER_SQLServerWinAuth.SettingUp.CreateModify). If you plan to locate the directory and the DB instance in different VPCs, configure cross-VPC traffic using VPC peering or [AWS Transit Gateway](https://docs.aws.amazon.com/vpc/latest/tgw/what-is-transit-gateway.html). The following procedure enables traffic between VPCs using VPC peering. Follow the instructions in [What is VPC peering?](https://docs.aws.amazon.com/vpc/latest/peering/Welcome.html) in the *Amazon Virtual Private Cloud Peering Guide*. **To enable cross-VPC traffic using VPC peering** 1. Set up appropriate VPC routing rules to ensure that network traffic can flow both ways. 1. Ensure that the DB instance's security group can receive inbound traffic from the directory's security group. 1. Ensure that there is no network access control list (ACL) rule to block traffic. If a different AWS account owns the directory, you must share the directory. **To share the directory between AWS accounts** 1. Start sharing the directory with the AWS account that the DB instance will be created in by following the instructions in [Tutorial: Sharing your AWS Managed Microsoft AD directory for seamless EC2 domain-join](https://docs.aws.amazon.com/directoryservice/latest/admin-guide/ms_ad_tutorial_directory_sharing.html) in the *Directory Service Administration Guide*. 1. Sign in to the Directory Service console using the account for the DB instance, and ensure that the domain has the `SHARED` status before proceeding. 1. While signed into the Directory Service console using the account for the DB instance, note the **Directory ID** value. You use this directory ID to join the DB instance to the domain. ## Step 5: Create or modify a SQL Server DB instance Create or modify a 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). Windows Authentication is only supported for SQL Server DB instances in a VPC. For the DB instance to be able to use the domain directory that you created, the following is required: + For **Directory**, you must choose the domain identifier (`d-ID`) generated when you created the directory. + Make sure that the VPC security group has an outbound rule that lets the DB instance communicate with the directory. ![\[Microsoft SQL Server Windows Authentication directory\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/WinAuth1.png) 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` parameter, use the domain identifier (`d-ID`) generated when you created the directory. + For the `--domain-iam-role-name` parameter, use the role that you created that uses the managed IAM policy `AmazonRDSDirectoryServiceAccess`. For example, the following CLI command modifies a DB instance to use a directory. For Linux, macOS, or Unix: ``` aws rds modify-db-instance \ --db-instance-identifier mydbinstance \ --domain d-ID \ --domain-iam-role-name role-name ``` For Windows: ``` aws rds modify-db-instance ^ --db-instance-identifier mydbinstance ^ --domain d-ID ^ --domain-iam-role-name role-name ``` **Important** If you modify a DB instance to enable Kerberos authentication, reboot the DB instance after making the change. ## Step 6: Create Windows Authentication SQL Server logins Use the Amazon RDS master user credentials to connect to the SQL Server DB instance as you do any other DB instance. Because the DB instance is joined to the AWS Managed Microsoft AD domain, you can provision SQL Server logins and users. You do this from the Active Directory users and groups in your domain. Database permissions are managed through standard SQL Server permissions granted and revoked to these Windows logins. For an Active Directory user to authenticate with SQL Server, a SQL Server Windows login must exist for the user or a group that the user is a member of. Fine-grained access control is handled through granting and revoking permissions on these SQL Server logins. A user that doesn't have a SQL Server login or belong to a group with such a login can't access the SQL Server DB instance. The ALTER ANY LOGIN permission is required to create an Active Directory 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. Run a data definition language (DDL) command such as the following example to create a SQL Server login for an Active Directory user or group. **Note** Specify users and groups using the pre-Windows 2000 login name in the format `domainName\login_name`. You can't use a user principal name (UPN) in the format *`login_name`*`@`*`DomainName`*. You can only create a Windows Authentication login on an RDS for SQL Server instance by using T-SQL statements. You can't use the SQL Server Management studio to create a Windows Authentication login. ``` USE [master] GO CREATE LOGIN [mydomain\myuser] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english]; GO ``` For more information, see [CREATE LOGIN (Transact-SQL)](https://msdn.microsoft.com/en-us/library/ms189751.aspx) in the Microsoft Developer Network documentation. Users (both humans and applications) from your domain can now connect to the RDS for SQL Server instance from a domain-joined client machine using Windows authentication. # 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. ## 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. # Connecting to SQL Server with Windows authentication To connect to SQL Server with Windows Authentication, you must be logged into a domain-joined computer as a domain user. After launching SQL Server Management Studio, choose **Windows Authentication** as the authentication type, as shown following. ![\[Connect to SQL Server using Windows Authentication\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/WinAuth4.png) ## Restoring a SQL Server DB instance and then adding it to a 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 a domain. Once the DB instance is restored, modify the instance using the process explained in [Step 5: Create or modify a SQL Server DB instance](USER_SQLServerWinAuth.SettingUp.md#USER_SQLServerWinAuth.SettingUp.CreateModify) to add the DB instance to a domain.