Rajendra Gupta
Verify AG dashboard in new replica

Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups

September 17, 2020 by

This article explores the impact of dropping a login in the active directory if that owns a HADR endpoint and SQL Server Always On Availability Groups. It is the 23rd article in the SQL Server Always On Availability Group.

Introduction

Database security is a critical aspect, and it protects you from compromising integrity, availability, and confidentiality. DBA should regularly audit the security logins, their permissions in the SQL instances. It is essential from SOX and PCI audits as well. You should remove the users who left the organization although it is removed from the AD group. You can do the internal security audit every quarterly or yearly.

Usually, DBA creates an AD group for their team members and assign the sysadmin (highest permissions). Each DBA connects to SQL Server using his credentials and uses Windows authentication for performing database administration activities.

For this article, I assume that [MyDemoSQL\Sunil.g] login exists individually in my primary and secondary replicas, and it has the sysadmin permissions on both replicas.

Suppose one of my DBA friends, Sunil G, did the following tasks in my production environment:

  • Configured a two-node SQL Server Always On Availability group
  • Used an existing database [SQLShackDemo] for the AG configuration
  • Configured a SQL listener for application connectivity

You can use the previous article in this series (TOC at the bottom) for creating a similar environment.

All well here. The SQL Server Always On Availability Group dashboard is healthy. The application works fine after AG failover as well. The AG dashboard is also ready for the failover( failover readiness = No data loss).

SQL Server Always On Availability Group dashboard

Now, the DBA Sunil G moved (resigned) from the organization and his domain id(mydemosql\sunil.g)is deleted from the active directory by AD team. You want to remove this ID from SQL logins as well. Before we drop the login, let’s see it make any impact on the availability group synchronization.

To simulate the issue, I connected to the active directory and dropped the user, as shown below.

active directory drop user

  • Note: You should not remove a production user from the active directory for testing purpose. You can test the scenario using a lab account

Now, verify the AG dashboard. It looks good. We do not have any impact on dropping the user.

To be sure that it does not put any impact, let’s perform the availability group failover using the failover availability group wizard in SSMS.

  • Availabilty group: SQLShackDemoAG
  • Current primary replica: SQLNode1\INST
  • New primary replica after failover: SQLNode2\INST1
  • AG Database: SQLShackDemo

Failover AG

After you move the availability group from the current to the new primary replica, verify the dashboard. It still looks good. AG replica status is synchronized, as shown below.

Verify AG dashboard in new replica

To further check for any issue after we dropped a user from the active directory, I also restarted the AG endpoint on both replicas, but still, the AG works fine. You can also try to restart SQL Services on both nodes to restart endpoints. We need to stop the endpoint first and start it using the ALTER ENDPOINT command.

  • Note: You should not restart the endpoint on production unless required. It stops the AG synchronization between the primary and secondary replica, and data could not propagate for AG databases. You get the disconnected status in the AG dashboard as shown below

Policy evaluation result

Now, let’s drop the login from the SQL instances as well. We can expand security and right-click on the login to delete it or use the DROP LOGIN statement.

You cannot drop this SQL login account as shown below. SQL Server complains that this user [mydemosql\suni.g] has granted one or more permissions. We need to drop the permissions of the existing permissions and then drop this server principal.

Always On Availability Groups

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

576 Views