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.

132 Views