Drop Login issues for logins tied to SQL Server Availability Groups

Problem

We always follow SQL Server security best practices when we grant Database Administrators (DBAs) sysadmin rights on production SQL Servers. We use a Domain Group and don’t add individual logins to the sysadmins server role. The Domain Group is audited and we get alerts every time a member is added or removed to/from this group.

During our regular security reviews we have discovered that one of the SQL Servers with Availability Groups has an individual DBA’s login in addition to the DBA group as a member of the sysadmin server role. The DBA is already a member of the DBA Domain group, so we are not sure why we had this login on SQL Server.

We have tried to remove the login, but couldn’t remove it.

Solution

We suspect this is somehow related to the Availability Groups (AG) setup as this DBA was creating and configuring them.

We tried to remove the DBA’s login as follow:

 USE [master]
GO
DROP LOGIN [DOMAIN\DBAUser1]
GO   

But the login couldn’t be deleted and we got the following error:

Msg 15173, Level 16, State 1, Line 4
Server principal has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.
 

Fixing SQL Server Permissions for Grant

First we need to find out what permissions the DBA was granted and to what login:

 USE [master]
GO
SELECT pm.class, pm.class_desc, pm.major_id, pm.minor_id, 
   pm.grantee_principal_id, pm.grantor_principal_id, 
   pm.[type], pm.[permission_name], pm.[state],pm.state_desc, 
   pr.[name] AS [owner], gr.[name] AS grantee
FROM sys.server_permissions pm 
   JOIN sys.server_principals pr ON pm.grantor_principal_id = pr.principal_id
   JOIN sys.server_principals gr ON pm.grantee_principal_id = gr.principal_id
WHERE pr.[name] = N'DOMAIN\DBAuser1';   

The result shows that the DBA has granted CONNECT permissions to one of the endpoints.

The query below displays which endpoint the DBA has granted permissions to:

 USE [master]
GO
SELECT pm.class, pm.class_desc, pm.major_id, pm.minor_id, 
   pm.grantee_principal_id, pm.grantor_principal_id, 
   pm.[type], pm.[permission_name], pm.[state],pm.state_desc, 
   pr.[name] AS [owner], gr.[name] AS grantee, e.[name] AS endpoint_name
FROM sys.server_permissions pm 
   JOIN sys.server_principals pr ON pm.grantor_principal_id = pr.principal_id
   JOIN sys.server_principals gr ON pm.grantee_principal_id = gr.principal_id
   JOIN sys.endpoints e ON pm.grantor_principal_id = e.principal_id 
        AND pm.major_id = e.endpoint_id
WHERE pr.[name] = N'DOMAIN\DBAUser1';

The endpoint’s name is “Hadr_endpoint” which is the default name of the Availability Group (AG) endpoint. This confirms that login creation on this SQL Server is related to the AG setup.

We are going to change the owner of this endpoint to the renamed “sa” login (“rendba”):

 USE [master]
GO
ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO rendba;
GO   

Read this tip about securing the “sa” account (renaming it, etc.). Check this tip about issues when an endpoint owner is deleted.

Note that we have changed the ownership of the endpoint, but haven’t reassigned the granted permission (CONNECT to DOMAIN\SQL_AG1_Test-Svc). This permission was lost when we changed the owner of the endpoint. We are skipping this step in purpose to demonstrate the related error later.

Fixing SQL Server Ownership

So, we have reassigned the ownership of the AG endpoint. Let’s try to drop the DBA’s login again:

 USE [master]
GO
DROP LOGIN [DOMAIN\DBAUser1]
GO

Msg 15141, Level 16, State 1, Line 4
The server principal owns one or more availability group(s) and cannot be dropped
   

As we can see from this error, the DBA owns one (or more) Availability Groups and we still not able to drop the login.

Let’s get a list of Availability Groups owned by the login:

 USE [master]
GO
SELECT ag.[name] AS AG_name, ag.group_id, r.replica_id, r.owner_sid, p.[name] as owner_name 
FROM sys.availability_groups ag 
   JOIN sys.availability_replicas r ON ag.group_id = r.group_id
   JOIN sys.server_principals p ON r.owner_sid = p.[sid]
WHERE p.[name] = 'DOMAIN\DBAUser1'
GO   

Now we will re-assign the AG’s ownership to the renamed “sa” login:

 USE [master]
GO
ALTER AUTHORIZATION ON AVAILABILITY GROUP::SQLAG1 TO rendba;
GO

When we’ve tried to drop the login on the secondary replica we get another error:

 USE [master]
GO
DROP LOGIN [DOMAIN\DBAUser1]
GO

Msg 15174, Level 16, State 1, Line 4
Login owns one or more database(s). Change the owner of the database(s) before dropping login.

This error looks more familiar. We need to change databases owner of these databases.

Let’s find databases which have our DBA as an owner:

 USE [master]
GO
SELECT [name] AS dbname FROM sys.databases WHERE SUSER_SNAME(owner_sid) = 'DOMAIN\DBAUser1'
GO

And change the owner of the AGTest (the database that was returned by the query above):

 USE [AGTst]
GO
ALTER AUTHORIZATION ON DATABASE::AGTst TO rendba;
GO

Here is an error we get when we try to update the database owner on the secondary replica that is setup as read-only:

Msg 3906, Level 16, State 1, Line 51
Failed to update database because the database is read-only.

When the secondary replica is setup as non-readable you will get this error:

In order to change the database owner on the secondary replica we have to make this database available for writes. It means we have to fail over the AG:

 USE [master]
GO
ALTER AVAILABILITY GROUP SQLAG1 FORCE_FAILOVER_ALLOW_DATA_LOSS;  
GO

Now we can try to drop the DBA’s login again:

 USE [master]
GO
DROP LOGIN [DOMAIN\DBAUser1]
GO

“Side Effects”

At this point we are able to drop the DBA’s login, but when you restart the SQL Server service you may get this error in SQL Server error log:

Connection handshake failed (error severity 10)

We get the same error when we check the AG state with sys.dm_hadr_availability_replica_state DMV:

 SELECT agr.replica_server_name, 
   agr.[endpoint_url],
   agrs.connected_state_desc, 
   agrs.last_connect_error_description, 
   agrs.last_connect_error_number, 
   agrs.last_connect_error_timestamp 
FROM sys.dm_hadr_availability_replica_states agrs 
JOIN sys.availability_replicas agr ON agrs.replica_id = agr.replica_id
WHERE agrs.is_local = 1

Remember the permission we didn’t grant when we reassigned the endpoint’s ownership at the beginning of the tip?

To fix this error we need to grant this missing permission to the Service Account listed in the error:

 USE [master]
GO
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [DOMAIN\sql_service_account_test];
GO

One of the Next Steps

To make sure we catch these and other connection issues we will create alerts for severity 10 and 16 errors containing “Connection handshake failed” in error messages:

 USE msdb 
GO
EXEC dbo.sp_add_alert @name=N'_Connection handshake failed Error: Sev 10', 
      @message_id=0, 
      @severity=10, 
      @enabled=1, 
      @delay_between_responses=120, 
      @include_event_description_in=1, 
      @event_description_keyword=N'Connection handshake failed'
GO
EXEC dbo.sp_add_alert @name=N'_Connection handshake failed Error: Sev 16', 
      @message_id=0, 
      @severity=16, 
      @enabled=1, 
      @delay_between_responses=120, 
      @include_event_description_in=1, 
      @event_description_keyword=N'Connection handshake failed'
GO
EXEC dbo.sp_add_notification @alert_name=N'_Connection handshake failed Error: Sev 10', 
      @operator_name=N'DBA_Operator', 
      @notification_method = 1
GO 
EXEC dbo.sp_add_notification @alert_name=N'_Connection handshake failed Error: Sev 16', 
      @operator_name=N'DBA_Operator', 
      @notification_method = 1
GO   

Here is an example of the email alert:

All in one script

You may or may not have issues with databases ownership, so we haven’t included the ownership changes step in this script.

 USE [master]
GO
ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO rendba;
GO 
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [DOMAIN\sql_service_account_test];
GO
ALTER AUTHORIZATION ON AVAILABILITY GROUP::SQLAG1 TO rendba;
GO
DROP LOGIN [DOMAIN\DBAUser1]
GO

If you still can’t drop the login because it owns databases you will need to re-assign the databases ownership. But keep in mind that the new owner could be different from “sa”. This will depend on the application’s security requirements.

Next Steps

• Check out more security tips here.
• Here is the link to the Microsoft resource about Security Catalog Views.
• Read this tip about security audit/reviews.
• This tip has scripts to help you with your security reviews.
• Use this checklist to harden your new SQL Server security.
• Find more information about setting up SQL Server alerts here.