Always On - Endpoint Ownership

  • Comments posted to this topic are about the item Always On - Endpoint Ownership

  • We created a pattern where setting up Always On was done with a remote login of a windows service account. It took care of a lot of problems.

  • My own a/c was the EndPointOwner.  So made sa but then yesterday evening patched system (delayed Microsoft Tuesday).  Had to put the service a/c in as follows as was getting issue and found error in SQL Server Logs and it stated:
    Database Mirroring login attempt by user '<my domain>\<account attempting>.' failed with error: 'Connection handshake failed. The login '<my domain>\<account attempting>' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT:]

    GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [<MyDomain>\<Service a/c]

    (Note that this is to Hadr_endpoint and not as shown in some web help pages as Mirroring Grant Connect on Endpoint::Mirroring to [my domain>\<account attempting]).

  • This will change the endpoint owner, but you may also run into the situation where the owner of the actual AG was no longer with the company. This query shows the owner of your AG:

    SELECT ar.replica_server_name
       , AS ag_name
    FROM sys.availability_replicas ar
    LEFT JOIN sys.server_principals sp
       ON sp.sid = ar.owner_sid
    INNER JOIN sys.availability_groups ag
       ON ag.group_id = ar.group_id
    WHERE ar.replica_server_name = SERVERPROPERTY('ServerName') ;

    It looks like you can use the command

    ALTER AUTHORIZATION ON availability group::[agname] TO [newowner]

    to change the AG owner. Needless to say, this makes me very nervous. I haven't been able to find much about people doing this. If anyone has done this in the past, I'd appreciate some input as to how it went..

  • Nice Article. But when running the AlwaysOn Wizard GUI you will get ownership to multiple things not only endpoint that may need changing

    1. Your login becomes owner of the endpoint which is already discussed in this article and how to change it.

    --Check Endpoint Owner
    USE master;
    SELECT SUSER_NAME(principal_id) AS endpoint_owner, name AS endpoint_name
    FROM sys.database_mirroring_endpoints;

    2. Your login also becomes the owner of the AG Group you created

    -- Check Owner of AG Group
    SELECT ar.replica_server_name, AS ag_name, ar.owner_sid,
    FROM sys.availability_replicas ar
    LEFT JOIN sys.server_principals sp
        ON sp.sid = ar.owner_sid
    INNER JOIN sys.availability_groups ag
        ON ag.group_id = ar.group_id
    WHERE ar.replica_server_name = SERVERPROPERTY('ServerName') ;

    -- Change AG_Owner TO SA

    3. Your login becomes owner of the AG DB on the secondary replicas if you used the AG wizard to restore the database on the secondary replicas

    -- Check All Database Owners ( Run it on the secondary replicas to check)
    SELECT name, suser_sname(owner_sid) FROM sys.databases;

    -- Change DB Owner to SA (You will first need to failover to that server and make it primary first before you can run below)
    USE [YourAGDBName]
    EXEC dbo.sp_changedbowner @loginame = N'SA', @map = false

    4. Your individual login also gets added directly as a server login with public access. You will need to drop it if you get access using group membership (ie: domain\DBADroup).

    -- You will not be able to run below to drop login with which you are already logged into the server with. Have someone else run it or use a different login account.

    USE [master]
    DROP LOGIN [domain\individualLogin]

  • Absolutely!  We just ran into an issue with your #3 above.  Thanks for elaborating...I only pointed out one aspect to keep the article short, but they are all noteworthy.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply