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

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 [MyDemoSQLSunil.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(mydemosqlsunil.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: SQLNode1INST
  • New primary replica after failover: SQLNode2INST1
  • 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 [mydemosqlsuni.g] has
granted one or more permissions. We need to drop the permissions of the existing permissions and then drop this
server principal.

  • Note: If your login is the owner of database objects such as tables, views, stored procedures, you need to transfer
    those ownerships as well. This article discusses the dependency of login on SQL Server Always On Availability Group
    objects
  • Drop logins

    This user does not hold ownership of any database object, but still, we get the error while dropping the login.

    Is it related to the SQL Server Always On Availability Group Configuration? Let’s check it out.

    Check permissions granted to the server principal [mydemosqlsunil.g] for AG group objects

    To check the permissions for a server principal, we can join sys.server_permissions and sys.server_principals as shown in the below query. It filters the records for
    the specified login.

    It shows that the user has connect permissions on the ENDPOINT. In an SQL Server Always On Availability Group
    configuration, SQL Server creates an endpoint and by default, the connected user in SSMS becomes the owner.

    Check permissions granted to the server principal

    To verify the endpoint name, we join the above query result with the sys.endpoints. It returns all endpoint information in the SQL instance. To get the result, we join on the major_id column of the sys.server_principals and endpoint_id of the sys.endpoints.

    As shown below, the endpoint owner is still the [mydemosqlsunil.g]. Due to this endpoint ownership, we cannot drop
    the login from the specific SQL instance.

    endpoint owner

    This endpoint HADR_ENDPOINT is the default endpoint for the availability group. You can find it in Server
    Objects -> Endpoints-> Database Mirroring
    in the SSMS object explorer

    HADR endpoint

    To modify the endpoint owner, we can use the ALTER AUTHORIZATION command and specify the new owner. In the below
    query, we specify the login [MyDemoSQL]ADADMIN] as the new HADR endpoint owner.

    We also need to give a connect permission on the HADR endpoint for the new account as well.

    If we try to drop the login now, it still complains about the availability group.

    drop the login

    Check SQL Server Always On Availability Group ownership for the [mydemosqlsunil.g]

    As per the above screenshot, the user [mydemosqlsunil.g] also has the ownership of the availability group. We can
    check the AG group ownership using the following dynamic management views (DMV)

    In the output, it lists the AG replica and its owner. As specified earlier, Sunil configured the [SQLShackDemoAG]
    availability group with his id.

    Availability Group ownership

    In the below query, we modify the availability group ownership using the ALTER AUTHORIZATION statement.

    Rerun the query and verify that AG owner is the new login we specified in the above query.

    availability group ownership

    We are now able to drop the Windows user from the SQL login in the primary replica, as shown below.

    Drop logins in AG

    Now, if we try to drop the login from the secondary replica, it gave a similar error message.

    drop the login from the secondary replica

    Execute both alter authorization statements on the secondary replica, and you can drop the login.

    You should perform the AG failover after dropping the login on both replicas and validate that it does not report
    any issues. It makes sure you do not have existing issues due to dropping logins.

    Guidelines to configure the SQL Server Always On Availability Group dashboard

    We observed that dropping the login did not put any impact on AG health. As per best practice, you should drop the
    unwanted logins from your SQL instance. Your domain administrator might have removed from the active directory but
    sill you should remove from the SQL logins. It is useful for your security audit, as well.

    • You should configure the availability group using a service account. This service account should not be shared with another SQL instance or service
    • In case, you use your credentials for configurations, and you should change the endpoint and availability group ownerships to service account or SA

    Conclusion

    In this article, we dropped a login if it is the owner of the SQL Server Always On Availability Group and the HADR
    endpoint. You should use the scripts in this article to assign authorization to a service account to avoid the issue while dropping the login.

    Table of contents

    Rajendra Gupta
    Latest posts by Rajendra Gupta (see all)

Author: admin

Leave a Reply

Your email address will not be published.