Always On - Endpoint Ownership

Question

Always On - Endpoint Ownership

Ginger Daniel

Mr or Mrs. 500

Points: 525
More actions
October 25, 2018 at 12:02 am

#366917

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

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

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

andre.quitta SSCommitted Points: 1889 More actions · Answer 1

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.

Michael.Beeby Ten Centuries Points: 1281 More actions · Answer 2

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: 10.203.241.70]

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]).

shaun.stuart SSCertifiable Points: 7033 More actions · Answer 3

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 ,ag.name AS ag_name ,ar.owner_sid ,sp.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..

sak3438 SSC Enthusiast Points: 110 More actions · Answer 4

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, ag.name AS ag_name, ar.owner_sid, sp.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') ;

-- Change AG_Owner TO SA
ALTER AUTHORIZATION ON AVAILABILITY GROUP::YourAGGroupNameGoesHere 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]
GO
EXEC dbo.sp_changedbowner @loginame = N'SA', @map = false
GO

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]
GO
DROP LOGIN [domain\individualLogin]
GO

Ginger Daniel Mr or Mrs. 500 Points: 525 More actions · Answer 5

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.