AG is continually reporting failover due to mirroring connect error

  • Hello experts,

    I'm seeing this Always On Availability Group behavior around every 5 minutes. And it is not like the AG is moving back and forth - it is always saying it failed over the BARDB02.

    The AG group FOOAG01 failed over to BARDB02.

    In the SQL error log, I see this error, which I think is related:

    messageDatabase Mirroring login attempt by user 'MYAD\BARDB03$.' failed with error: 
    'Connection handshake failed.
    The login 'MYAD\BARDB03$' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: XXX.XX.XXX.XX]

    Thing is, I can't find a SQL login matching the one in the error. I can't even find a Windows account on the host matching the name.

    I Googled the error and found the following suggested fix:

    GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [MYAD\BARDB03$]
    GO

    But I get this error on both the primary and the secondary:

    Msg 15151, Level 16, State 1, Line 1
    Cannot find the login 'MYAD\BARDB03$', because it does not exist or you do not have permission.

    Does anyone know how to track down where this account is, and (if necessary) how to replace it with another account? At the moment, I don't know where else to look.

    Thanks for any help.

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • NOTE - I do not use AG's nor have I configured them before.  I am just replying based on my interpretation of the errors you have.

    If memory serves, the logins that end in a $ are computer names.  This likely means that your SQL instance and failover setup are running as local system.  Change that to an AD account and make sure the account exists in SQL with the appropriate permissions and you should be in a better place.

    Alternately (not recommended), create the login MYAD\BARDB03$ and then grant permissions on it.  I don't recommend this approach as I don't like computer logins on my SQL instances if I can avoid them.  They are harder to work with, support, and maintain.  And best practice is to use an actual AD account rather than a computer.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

  • Run the query: Select * From sys.endpoints

    Look at the principal_id column - if that column is not a 1 (for the sa account) then the endpoint was configured to use a different account.  When you use the wizard to setup the HADR_Endpoint it uses your account - which can cause issues later.  You will want to change that to the sa account.

    As Brian noted - if SQL Server was installed to run with the default account, then it would be running under 'Local System'.  That account will use the computer name account for its connection.  That account will be: {servername}\{servername}$

    You can grant that account access, but it isn't recommended.  You should be running SQL Server with either a domain service account - or better yet - a Group Managed Service Account (https://docs.microsoft.com/en-us/windows-server/security/group-managed-service-accounts/group-managed-service-accounts-overview).

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 3 posts - 1 through 3 (of 3 total)

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