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.
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.
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.
Right-click on Users folder and it gives you the option to create a User, Group, Computer, Organization Unit.
It opens the following new user object form. Enter the first name, last name, user logon name for the new user as shown below.
Specify the user account passwords and properties as per your requirement
Click Next, and it creates the user, as shown below.
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.
In computer management, go to Local Users and Groups. Click on Groups, and here it sees an Administrators group.
Open the Administrators group and add the user we created earlier. Click on Apply and Ok.
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.
It connects to EC2 using the new user. We can verify it logged in the user as shown below.
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.
- Download SQL Server Management Studio 18.5
- Download Azure Data Studio
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.
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.
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.
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.
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.
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.
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.
We can use the SYSTEM_USER() function to return the current logged in user in RDS instance. Execute the following query in ADS.
|
1 2 3 4 |
DECLARE @sys_usr char(30); SET @sys_usr = SYSTEM_USER; SELECT 'The current system user is: '+ @sys_usr; GO |
We can verify that it is connected with the domain user [sqlshackdemo\rajendra.gupta]
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE LOGIN [sqlshack\testsql] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english]; USE [mssqldemo] GO CREATE USER [sqlshackdemo\testsql] FOR LOGIN [sqlshackdemo\testsql] GO USE [mssqldemo] GO ALTER ROLE [db_datareader] ADD MEMBER [sqlshackdemo\testsql] GO USE [mssqldemo] GO ALTER ROLE [db_datawriter] ADD MEMBER [sqlshackdemo\testsql] GO USE [mssqldemo] GO ALTER ROLE [db_ddladmin] ADD MEMBER [sqlshackdemo\testsql] GO |
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.
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.
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.
Refresh the dashboard after some time, and you see it is now removing the domain membership for AWS RDS SQL Server.
Once it successfully removes the domain from AWS RDS SQL Server, you cannot see the column Directory status from the 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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023