Authenticate Microsoft SQL Server on Amazon EC2 using AWS Directory Service
Created by Jagadish Kantubugata (AWS) and Oludahun Bade Ajidahun (AWS)
Environment: PoC or pilot | Source: Active Directory | Target: AWS Directory Service |
R Type: N/A | Workload: Microsoft | Technologies: Security, identity, compliance; Databases |
AWS services: AWS Directory Service |
Summary
This pattern describes how to create an AWS Directory Service directory and use it to authenticate Microsoft SQL Server on an Amazon Elastic Compute Cloud (Amazon EC2) instance.
AWS Directory Service provides multiple ways to use Amazon Cloud Directory and Microsoft Active Directory (AD) with other AWS services. Directories store information about users, groups, and devices, and administrators use them to manage access to information and resources. AWS Directory Service provides multiple directory choices for users who want to use their existing Microsoft AD or Lightweight Directory Access Protocol (LDAP)–aware applications in the cloud. It also offers those same choices to developers who need a directory to manage users, groups, devices, and access.
Prerequisites and limitations
Prerequisites
An active AWS account
A virtual private cloud (VPC) with a minimum of two private subnets and two public subnets
An AWS Identity and Access Management (IAM) role to join the server into the domain
Architecture
Source technology stack
The source can be an on-premises Active Directory
Target technology stack
AWS Directory Service for Microsoft Active Directory (AWS Managed Microsoft AD)
Target architecture
Tools
SQL Server Management Studio (SSMS) is a tool for managing Microsoft SQL Server, including accessing, configuring, and administering SQL Server components.
Epics
| Task | Description | Skills required |
|---|---|---|
Select AWS Managed Microsoft AD as the directory type. | On the AWS Directory Service console | DevOps |
Select edition. | From the available editions for AWS Managed Microsoft AD, choose Standard Edition. | DevOps |
Specify the directory DNS name. | Use a fully qualified domain name. This name will resolve inside your VPC only. It does not need to be publicly resolvable. | DevOps |
Set the administrator password. | Set the password for the default administrative user, which is named Admin. | DevOps |
Choose the VPC and subnets. | Choose the VPC that will contain your directory and the subnets for the domain controllers. If you do not have a VPC with at least two subnets, you must create one. | DevOps |
Review and launch the directory. | Review the edition and price information for the directory, and then choose Create directory. | DevOps |
| Task | Description | Skills required |
|---|---|---|
Select an AMI for SQL Server. | The steps in this epic seamlessly join a Windows EC2 instance to your AWS Managed Microsoft AD directory. On the Amazon EC2 console | DevOps, DBA |
Configure instance details. | Configure the Windows instance to meet your requirements for SQL Server. | DevOps, DBA |
Select the key pair name. | Select a key pair, and then launch the instance. | DevOps, DBA |
Add a network. | You can choose the VPC that your directory was created in. | DevOps, DBA |
Select an IAM role. | In Advanced settings, select an IAM profile that has the AWS managed policies | DevOps, DBA |
Add a subnet. | Choose one of the public subnets in your VPC. The subnet that you choose must have all external traffic routed to an internet gateway. If this is not the case, you won't be able to connect to the instance remotely. | DevOps, DBA |
Choose your domain. | Choose the domain that you created from the Domain join directory list. | DevOps, DBA |
Launch the instance. | Choose Launch instance. | DBA |
| Task | Description | Skills required |
|---|---|---|
Log in as a Windows administrator. | Log in to the Windows EC2 instance by using Windows administrator credentials. | DBA |
Log in to SQL Server. | Launch SQL Server Management Studio (SSMS) and log in to SQL Server by using the Windows authentication method. | DBA |
Create a login for the directory user. | In SSMS, choose Security, and then choose New Login. | DBA |
Search for a login name. | Choose the search button next to the login text box. | DBA |
Select a location. | In the Select User or Group dialog box, choose Locations. | DBA |
Enter network credentials. | Enter the fully qualified network credentials you used when you created the directory service; for example: | DBA |
Select the directory. | Choose the AWS directory name, and then choose OK. | DBA |
Select an object name. | Select the user for which you want to create the login. Select the location, choose the entire directory, search for the user, and add the login. | DBA |
Log in to the SQL Server instance. | Log in to the Windows EC2 instance for SQL Server by using your domain credentials. | DBA |
Log in to SQL Server as a domain user. | Launch SSMS and connect to the database engine by using the Windows authentication method. | DBA |
Related resources
AWS Directory Service documentation (AWS website)
Create your AWS Managed Microsoft AD directory (AWS Directory Service documentation)
Seamlessly join a Windows EC2 instance (AWS Directory Service documentation)
Microsoft SQL Server on AWS
(AWS website) SSMS documentation
(Microsoft website) Create a login in SQL Server
(SQL Server documentation)
