Menu
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Using Windows Authentication with an Amazon RDS DB Instance Running Microsoft SQL Server

You can use Windows Authentication to authenticate users when they connect to your Amazon RDS DB instance running Microsoft SQL Server. The DB instance works with AWS Directory Service for Microsoft Active Directory (Enterprise Edition), also called 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 AWS Directory Service.

Amazon RDS supports Windows Authentication for SQL Server in the following AWS Regions:

  • US East (N. Virginia)

  • US West (Oregon)

  • Asia Pacific (Singapore)

  • Asia Pacific (Sydney)

  • Asia Pacific (Tokyo)

  • EU (Frankfurt)

  • EU (Ireland)

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, you need to create a forest trust. For more information on setting up forest trusts using AWS Directory Service, see http://docs.aws.amazon.com/directoryservice/latest/admin-guide/setup_trust.html.

To set up Windows authentication for a SQL Server DB instance, you do the following steps (explained in greater detail in this section):

  1. Use the AWS Directory Service for Microsoft Active Directory (Enterprise Edition), also called Microsoft AD, either from the AWS console or AWS Directory Service API to create a Microsoft AD directory.

  2. If you use the AWS CLI or Amazon RDS API to create your SQL Server DB instance, you need to create an IAM role that uses the managed IAM policy AmazonRDSDirectoryServiceAccess. The role allows Amazon RDS to make calls to your directory. If you use the AWS console to create your SQL Server DB instance, AWS creates the IAM role for you.

  3. Create and configure users and groups in the Microsoft AD directory using the Microsoft Active Directory tools. For more information about creating users and groups in your Active Directory, see Add Users and Groups (Simple AD and Microsoft AD) in the AWS Directory Service documentation.  http://docs.aws.amazon.com/directoryservice/latest/admin-guide/creating_ad_users_and_groups.html.

  4. Use Amazon RDS to create a new SQL Server DB instance either from the AWS 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, and locating the DB instance in the same VPC as the domain directory.

  5. Use the Amazon RDS master user credentials to connect to the SQL Server DB instance as you would any other DB instance. Because the DB instance is joined to the Microsoft AD domain, you can provision SQL Server logins and users from the Active Directory users and groups in their domain (known as SQL Server "Windows" logins). Database permissions are managed through standard SQL Server permissions granted and revoked to these windows logins.

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 would 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 check to make sure your connection is using Kerberos, you can run the following query:


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

Setting Up Windows Authentication for SQL Server DB Instances

You use AWS Directory Service for Microsoft Active Directory (Enterprise Edition), also called Microsoft AD, to set up Windows Authentication for a SQL Server DB instance. To set up Windows Authentication, you take the following steps:

Step 1: Create a Directory Using the AWS Directory Service

AWS Directory Service creates a fully managed, Microsoft Active Directory in the AWS cloud. When you create a Microsoft AD directory, AWS Directory Service creates two domain controllers and DNS servers on your behalf. The directory servers are created in different subnets in a VPC; this redundancy helps ensure that your directory remains accessible even if a failure occurs.

When you create a Microsoft AD directory, AWS 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.

    Note

    Be sure to save this password. AWS Directory Service does not store this password and it cannot be retrieved or reset.

  • Creates a security group for the directory controllers.

When you launch an AWS Directory Service for Microsoft Active Directory (Enterprise Edition), 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 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 Directory Service for Microsoft Active Directory (Microsoft AD)

  1. In the AWS Directory Service console navigation pane, select Directories and choose Set up Directory.

  2. Choose Create Microsoft AD. Microsoft AD is the only option currently supported for use with Amazon RDS.

  3. Provide the following information:

    Directory DNS

    The fully qualified name for the directory, such as corp.example.com.

    NetBIOS name

    The short name for the directory, such as CORP.

    Administrator 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 and cannot include the word "admin." The password is case-sensitive and must be between 8 and 64 characters in length, inclusive. 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 (~!@#$%^&*_-+=`|\(){}[]:;"'<>,.?/)

    Confirm password

    Retype the administrator password.

    Description

    An optional description for the directory.

  4. Provide the following information in the VPC Details section and choose Next Step.

    VPC

    The VPC for the directory. Note that the SQL Server DB instance must be created in this same VPC.

    Subnets

    Select the subnets for the directory servers. The two subnets must be in different Availability Zones.

  5. Review the directory information and make any necessary changes. When the information is correct, choose Create Microsoft AD.

    graphic of Directory details page

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, select the directory in the directory listing. Note the Directory ID; you will need this value when you create or modify your SQL Server DB instance.

graphic of details page

Step 2: Create the IAM role for Use by Amazon RDS

If you use the AWS console to create your SQL Server DB instance, you can skip this step. If you used the AWS CLI or Amazon RDS API to create your SQL Server DB instance, you must create an IAM role that uses the managed IAM policy AmazonRDSDirectoryServiceAccess. This role allows Amazon RDS to make calls to the AWS Directory Service for you.

The following IAM policy, AmazonRDSDirectoryServiceAccess, provides access to AWS Directory Service:


{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Action": [
            "ds:DescribeDirectories", 
            "ds:AuthorizeApplication", 
            "ds:UnauthorizeApplication"
        ],
      "Effect": "Allow",
      "Resource": "*"
    }
  ]
}				
			

Create an IAM role using this policy. For more information about creating IAM roles, see http://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_managed-using.html#create-managed-policy-console.

Step 3: Create and Configure Users and Groups

You can create users and groups with the Active Directory Users and Computers tool, which is part 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 AWS Directory Service directory, you must be connected to a Windows EC2 instance that is a member of the AWS Directory Service directory, and be logged in as a user that has privileges to create users and groups. For more information, see http://docs.aws.amazon.com/directoryservice/latest/admin-guide/creating_ad_users_and_groups.html.

Step 4: Create or Modify a SQL Server DB Instance

Next, you create or modify a Microsoft SQL Server DB instance for use with the directory. You can do this in one of the following ways:

  • Create a new SQL Server DB instance

  • Modify an existing SQL Server DB instance

  • Restore a SQL Server DB instance from a DB Snapshot

  • Restore a SQL Server DB instance from a Point-in-Time Restore

Windows Authentication is only supported for SQL Server DB instances in a VPC, and the DB instance must be in the same VPC as the directory.

Several parameters are required for the DB instance to be able to use the domain directory you created:

  • For the domain parameter, you must enter the domain identifier ("d-*" identifier) generated when you created the directory.

  • Use the same VPC that was used when you created the directory.

  • Use a security group that allows egress within the VPC so the DB instance can communicate with the directory.

Step 5: Create Windows Authentication SQL Server Logins

Use the Amazon RDS master user credentials to connect to the SQL Server DB instance as you would any other DB instance. Because the DB instance is joined to the Microsoft AD domain, you can provision SQL Server logins and users 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.

To allow 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. If a user does not have a corresponding SQL Server login and is not a member of a group with a corresponding SQL Server login, that user cannot access the SQL Server DB instance.

The ALTER ANY LOGIN permission is required to create an Active Directory SQL Server login. If you have not yet created any logins with this permission, connect as the DB instance's master user using SQL Server Authentication. Run the following data definition language (DDL) command to create a SQL Server login for an Active Directory user or group:

CREATE LOGIN [<user or group>] FROM WINDOWS WITH DEFAULT_DATABASE = [master],
   DEFAULT_LANGUAGE = [us_english];

Users or groups must be specified using the pre–Windows 2000 login name in the format domainName\login_name. You cannot use a User Principle Name (UPN) in the format login_name@DomainName. For more information about CREATE LOGIN, go to 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 SQL Server instance from a domain joined client machine using Windows authentication.

Managing a DB Instance in a Domain

You can use the AWS console, AWS CLI, or the Amazon RDS API to manage your DB instance and its relationship with your domain, such as moving the DB instance into, out of, or between domains.

For example, using the Amazon RDS API, you can do the following:

  • To re-attempt a domain join for a failed membership, use the ModifyDBInstance API action and specify the current membership's directory ID.

  • To update the IAM role name for membership, use the ModifyDBInstance API action 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 action and specify 'none' as the domain parameter.

  • To move a DB instance from one domain to another, use the ModifyDBInstance API action and specify the domain identifier of the new domain as the domain parameter.

  • To list membership for each DB instance, use the DescribeDBInstances API action.

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 re-issuing 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. If you create a DB instance or modify an existing instance and the attempt to become a member of a domain fails, you should re-issue the modify command 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.

Restoring a SQL Server DB Instance and then Adding It to a Domain

You can restore a DB snapshot or do a point-in-time restore 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 the section Step 4: Create or Modify a SQL Server DB Instance to add the DB instance to a domain.