Problem
We have a large number of users, applications, and services accessing our RDS SQL Server environment. The DBAs have to continuously manage and keep track of these user accounts and their permission levels. So far, they have been creating and managing standard SQL Server logins and roles with custom scripts, but now there is a new requirement for users to log in with their Active Directory account only. How can we make the RDS SQL Servers use an AD-based authentication system?
Solution
Database Administrators in large enterprises often need to manage many SQL Server logins for users and services. DBAs have to create SQL Server standard logins and roles without any centralized authentication system, assign role-based permissions, and manually audit login events. With a growing database environment, the process becomes difficult to manage and monitor.
Amazon Web Service now allows RDS SQL Server to integrate with the AWS Managed Active Directory platform. The managed AD can also integrate with existing Active Directories of an enterprise. This allows creating a single, centralized authentication mechanism for all SQL Server database users. By default, Active Directory uses Kerberos as a built-in authentication protocol that encrypts passwords sent over the network. Kerberos makes the authentication process faster and secure. The benefits of Windows-integrated authentication also include better control over user management and auditing. Also, to use services like SSIS and SSRS with RDS, it has to use Windows-based authentication.
This article will show how to set up an AWS Managed Active Directory and integrate it with an AWS RDS SQL Server instance. We will verify the integration by logging in to the database instance.
Setting up an AWS Managed Active Directory
To create an AWS Managed Active Directory, follow these steps:
Select the “Directory Service” link in the AWS Management Console under the “Security, Identity, & Compliance” section.
In the Directory Service console, click the “Set up directory” button:
This shows three options for creating a directory service. Select the option “AWS Managed Microsoft AD” and click “Next”:
In the next screen, select the edition of the Active Directory. In this case, we are choosing the Standard Edition:
In the same screen, specify the FQDN (Fully Qualified Domain Name) of the directory, its NetBIOS name, and the administrator password:
In the next screen, enter the VPC and preferred subnets for the Active Directory. To ensure communication between the RDS SQL Server instance and the Active Directory, select the same VPC where the RDS instance is running. Many organizations use a separate “admin” VPC for its core systems like Active Directory. In such cases, the target VPC and the RDS instance VPC must be peered.
In the next screen, review the options chosen and click “Create directory”. It will take approximately half-hour for the Active Directory to become available.
Configure RDS SQL Server for AD Authentication
Both new and existing RDS instances can be configured for Active Directory authentication. In the image below, we are selecting the option to use Active Directory authentication for an RDS SQL Server instance:
Note how this process also authorizes Amazon RDS to create the IAM role necessary for using Windows authentication.
This IAM role has a policy with the following permissions:
Create an Active Directory User
As a next step, install the “Active Directory Users and Computers” applet in a Windows Server and register the newly created AD in the applet. You can do so using the default Admin user:
Create a new user in the directory:
For our test purposes, we have created a user named USER01.
Create SQL Server Windows Login
Once the user is created, log in to the RDS SQL Server instance as the master user from SSMS:
Next, create a login account for the AD user created before:
CREATE LOGIN [PROD\USER01] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english]; USE [tempdb] GO
You can now create a database user for this login and grant the user permissions to database schemas and objects.
To test the login process for PROD\USER01, log out from the current session, select Windows as the authentication mode, and specify the AD account name:
If everything was done successfully, the user should be logged in successfully.
Conclusion
As you can see, configuring an RDS SQL Server for Windows-based authentication is simple but offers data security benefits. Security auditing can easily check log in/log off events for AD users, just like SQL standard logins. However, it also means users can be temporarily disabled or removed from an AD group without anything done from the database side. Role-based permissions assigned to AD groups can free up DBAs from assigning roles to individual users. AD user accounts can also be made to conform to various Active Directory policies. Finally, using AWS-managed Microsoft AD means system administrators don’t have to manually install, configure, and manage a separate Active Directory in EC2 instances and worry about backup, fault tolerance, etc.
Next Steps
Learn more about:
