How to Configure Amazon RDS SQL Server for Windows Authentication


By:   |   Updated: 2020-11-20   |   Comments   |   Related: More > Amazon AWS


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.

Directory Service link in the AWS Management Console.

In the Directory Service console, click the "Set up directory" button:

AWS Directory Service console

This shows three options for creating a directory service. Select the option "AWS Managed Microsoft AD" and click "Next":

Selecting the directory type.

In the next screen, select the edition of the Active Directory. In this case, we are choosing the Standard Edition:

Selecting the Active Directory edition.

In the same screen, specify the FQDN (Fully Qualified Domain Name) of the directory, its NetBIOS name, and the administrator password:

Specifying directory FQDN, NetBIOS name and Admin 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.

Selecting the VPC and subnets for the Active Directory.

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.

Reviewing the options before creating the Active Directory.

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:

Configuring Amazon RDS SQL Server instance for Windows authentication.

Note how this process also authorizes Amazon RDS to create the IAM role necessary for using Windows authentication.

Integrating RDS SQL Server with Amazon Managed Microsoft AD creates a role

This IAM role has a policy with the following permissions:

The IAM policy for the IAM role created by RDS SQL Server and AD integration.

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:

Registering the Active Directory as the Admin user

Create a new user in the directory:

Creating a new AD user.

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:

Logging in to RDS SQL Server as the master user

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
Creating a login for the Windows user.

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:

Logging into the RDS SQL Server instance with Windows authentication.

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:



Last Updated: 2020-11-20


get scripts

next tip button



About the author
MSSQLTips author Poojan Mehta Coming soon...

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Limitations of SQL Server Native Backup and Restore in Amazon RDS

Serverless ETL using AWS Glue for RDS databases

Restore SQL Server database backup to an AWS RDS Instance of SQL Server

Troubleshoot Slow RDS SQL Servers with Performance Insights

How to Natively Import Data from Amazon S3 to an RDS SQL Server Database














get free sql tips
agree to terms