Rajendra Gupta
database rdsadmin

Advanced Windows Authentication configurations in AWS RDS SQL Server

May 13, 2020 by

This article will cover advanced configurations for Windows Authentication in AWS RDS SQL Server.

Introduction

In this article, we will cover the following sections:

  • Create active directory users
  • Modify RDS instance to join domain membership
  • Connect to SQL Server using Windows authentication
  • Remove a domain membership from RDS Server

You should go through the article Initial Windows Authentication configurations in AWS RDS SQL Server before going further in this article.

A Quick Recap of Windows authentication for RDS instance

Windows authentication is a preferred method to connect to the SQL Server instance. It provides you with the flexibility to manage user accounts, groups in an active directory. You can also set policies similar to changing the password after a period, use a complex password, account lockout policy.

We can use both SQL and Windows authentication in AWS RDS SQL Server similar to a native SQL Server instance. In the previous article, we covered the following topics regarding it.

  • Create an AWS managed active directory [sqlshackdemo.com] using the AWS directory service. Its status should be active so that we can use it in subsequent resources of AWS such as EC2, RDS
  • Create an IAM role to manage directory service on your behalf
  • We have created an AWS EC2 server with Windows operating system. We configured this EC2 with the active directory [sqlshackdemo.com] and logged in with the default [sqlshackdemo\admin] account
  • We have installed Microsoft Active directory administration tools on Windows Server 2019 operating system

Create active directory users in the [sqlshackdemo.com] domain

As you know, we use public DNS to take RDP of the EC2 instance. By default, EC2 does not get a static IP, and if we restart the server, it might change the IP address and public DNS. We can assign an elastic IP address (static), but it is out of scope for this article.

We should not use the admin account for the EC2 account as it is having the administrative permission on the active directory and users can create, update, delete the records from the AD. To create a new user, go to Run and type DSA.MSC command inside EC2.

It opens the following active directory users and computers snippet. You can see a root folder with the domain name we used in the directory service.

Create active directory users

Expand the domain tree, and you get the following organizational units (OUs).

  • AWS delegated groups
  • AWS Reserved
  • Built-in
  • Computers
  • Domain controllers
  • ForeignSecurityPrincipals
  • Managed Service accounts
  • Sqlshackdemo
  • Users

You can go through this article to understand these organization groups.

Organization groups for AWS RDS SQL Server

We need to choose an organization group in which the new account should exist. Let’s select the [sqlshackdemo], and it has folders for Users and Computers.

Users and Computers

Right-click on Users folder and it gives you the option to create a User, Group, Computer, Organization Unit.

Users folder options

It opens the following new user object form. Enter the first name, last name, user logon name for the new user as shown below.

New AD object

Specify the user account passwords and properties as per your requirement

User properties

Click Next, and it creates the user, as shown below.

View the user

Now, in the next step, we need to add this user to the administrative group on the EC2 instance. Go to Server Manager and open Computer Management.

Computer Management

In computer management, go to Local Users and Groups. Click on Groups, and here it sees an Administrators group.

Administrators group

Open the Administrators group and add the user we created earlier. Click on Apply and Ok.

Add a user in the Administrators group

Now, we should be able to RDP in the EC2 instance using this newly created user. Sign out the EC2 instance and login with a new user.

RDP in the EC2 instance

It connects to EC2 using the new user. We can verify it logged in the user as shown below.

RDP in the EC2 instance using a new user for AWS RDS SQL Server

Install SSMS or Azure Data Studio to connect with RDS

We require SQL Server client tools such as SSMS or Azure Data Studio in the EC2 server. You can use your preferred client tool. I will not cover the installation of these tools. It is very straightforward. You can download the latest version of these tools using the following links.

Add active directory domain in the RDS instance

To use Windows authentication with the RDS instance, we need to join the AD account in the RDS instance. For this purpose, RDS should be in available status.

You can use the article AWS RDS SQL Server – Launching a new database instance to launch a new RDS SQL instance.

I have the following RDS instance with SQL Server Express edition for my demo purpose.

Add active directory domain in the RDS instance

To modify the RDS instance, click on Modify. In the Microsoft SQL Server Windows Authentication, choose the AD directory from the drop-down as shown below.

Microsoft SQL Server Windows Authentication

Click on Continue. On the next page, we can schedule the modification option. We can either apply during the next scheduled maintenance window or apply it immediately.

Schedule of modification

Let’s apply the change immediately and Modify DB instances. It will restart the RDS instance and might take a few minutes to get active.

It shows status as Modifying while RDS applies changes for Active directory.

Active directory change

You can see the following status in the RDS instance related to the domain.

  • Joining: It shows that the RDS instance is joining the domain
  • Joined: Once an instance completed joining an active directory domain, its status changes to Joined
  • Pending-join: If the instance membership to a domain is pending, you get this status
  • Pending-maintenance-join: Once we apply a domain for RDS instance, we have the option to apply changes immediately or wait for the scheduled maintenance window. In this article, we joined immediately to a domain, but if you select other option, you get status as pending-maintenance-join
  • Failed: if there are any configurations related issues, it might prevent RDS to join a domain. You should investigate and review configurations in case status is failed
  • Pending-removal: We can remove membership of an RDS instance from a domain. If the removal of instance is pending, you get this status
  • Pending-maintenance-removal: Similar to the pending-maintenance-join, you can schedule the removal of RDS from a domain in the next scheduled maintenance window. This status gives you information about pending removal due to the maintenance schedule
  • Removing: You get this message while AWS is removing the domain membership from the RDS

In the following screenshot, we see the status as Joined in the connectivity & Security page.

Connectivity & Security page

Once the instance is available again, launch Azure Data Studio or SSMS to connect with the RDS instance. We require endpoint for the RDS instance that you can get it from the instance property page.

Connect using Windows authentication

It connects to AWS instance using the Windows authentication. You can see the connected instance and the server dashboard. By default, we get the user database rdsadmin in the RDS instance.

database rdsadmin

We can use the SYSTEM_USER() function to return the current logged in user in RDS instance. Execute the following query in ADS.

We can verify that it is connected with the domain user [sqlshackdemo\rajendra.gupta]

Domain user

You can add more active directory domain users in the AWS RDS SQL Server instance using the CREATE LOGIN AND CREATE USER commands. In the following queries, we create a Windows domain user [sqlshack\testsql]. It also assigns [db_datareader],[db_datawriter] and [db_ddladmin] permission in the [mssqldemo] database.

Remove a domain membership from an AWS RDS SQL Server

Suppose you need to remove the domain membership from an AWS RDS SQL Server. You can also change the active directory to another domain using this option. First, remove the membership from an existing directory and join it with another domain. We have already covered the process to join a domain in the RDS instance in the previous section. Let’s explore the process to remove the domain membership from an AWS RDS SQL Server.

In the RDS dashboard of AWS web console, modify the instance and choose None for the directory. It is located in the Microsoft SQL Server Windows Authentication, as shown below.

Remove a domain membership for AWS RDS SQL Server

Click Next and select the option to schedule the modification. It is a demo instance for me, so we are ok to apply changes immediately.

Schedule the modification

Click on Modify DB instance. It starts applying the changes. We can see the status pending-removal because it is preparing an instance to remove the domain membership.

Pending-removal status for AWS RDS SQL Server

Refresh the dashboard after some time, and you see it is now removing the domain membership for AWS RDS SQL Server.

View directory status

Once it successfully removes the domain from AWS RDS SQL Server, you cannot see the column Directory status from the RDS dashboard.

View RDS Dashboard

Conclusion

In this article, we explored steps to add a domain membership for an AWS RDS SQL Server along with the process to remove the domain membership. We also created a user in Active directory and accessed the SQL database using Windows authentication. It is always recommended to use Windows authentication. In case you do not have an active directory, you can use AWS managed active directory service as we saw in this article.

Rajendra Gupta
AWS RDS

About Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features. While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs. He can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

471 Views