SQL SERVER – AlwaysOn Wizard Error – The Endpoints Tab Lists at Least One Endpoint that Uses Only Windows Authentication

SQL
3 Comments

While configuring AlwaysOn availability group for one of my clients, there were various errors. I planned to write blog on all errors which I encountered. Here is an earlier blog. After we bypassed all errors in “Select Databases” screen, we came to “Specify Replicas” screen. We added both server names and as soon as we hit next, we got below message. Let us learn about how to fix AlwaysOn Wizard Error.

SQL SERVER - AlwaysOn Wizard Error - The Endpoints Tab Lists at Least One Endpoint that Uses Only Windows Authentication ao-error2-wiz-01

The Endpoints tab lists at least one endpoint that uses only Windows Authentication. However, the server instance might be running under a nondomain account. To use the listed endpoint, change the corresponding SQL Server service account to a domain account. To continue using the nondomain account, alter the endpoint to use a certificate.
Do you want to use the listed endpoints?

To check the endpoints, we went to “Endpoints” table on the same screen and found below.

SQL SERVER - AlwaysOn Wizard Error - The Endpoints Tab Lists at Least One Endpoint that Uses Only Windows Authentication ao-error2-wiz-02

WORKAROUND/SOLUTION

As per error message, if we use the LocalSystem account, then we need to create certificates. But my client had a domain, so we needed to change the service account for SQL Server services to a domain account.

Below screenshot will tell you what you need to do.

  1. Go to configuration manager and choose properties of SQL Server (MSSQLSERVER). If you have named instance than its SQL Server (Instance Name)
    SQL SERVER - AlwaysOn Wizard Error - The Endpoints Tab Lists at Least One Endpoint that Uses Only Windows Authentication ao-error2-wiz-03
  1. Go to “Log On” tab and provide a user name and password for a domain account.
    SQL SERVER - AlwaysOn Wizard Error - The Endpoints Tab Lists at Least One Endpoint that Uses Only Windows Authentication ao-error2-wiz-04
  1. Hit OK and retry AG setup using wizard.

If you don’t want to use a domain account as service account, then on, all servers we need to create accounts for each other’s machine account. Below is an secondary replica.

CREATE LOGIN [domain\primary$] FROM WINDOWS

For primary, we need to create accounts for secondary replica.

CREATE LOGIN [domain\secondary$] FROM WINDOWS

If we don’t do above, then the database would not synchronize, and we would see below the messages

Database Mirroring login attempt by user domain\primary$.’ failed with error: ‘Connection handshake failed. The login ‘domain\primary$’ does not have CONNECT permission on the endpoint. State 84.’. [CLIENT: nn.nn.nn.nn]

Here is the command to fix above issue.

GRANT CONNECT ON endpoint::hadr_endpoint TO [domain\primary$]

Hope this would help you in solving the issue.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Error – Auto Close is Enabled. Only Databases with Auto Close Disabled can be Added to an Availability Group
Next Post
SQL SERVER – AlwaysOn Wizard Error – Checking for Compatibility of the Database File Locations on the Server Instance that Hosts Secondary Replica

Related Posts

3 Comments. Leave new

  • Hi Dave! I’ve been trying to achieve creating a Dag, but this error message always shows up, even tough I´m using domain accounts for the SQL Server Services, is there any KB or workaround for this? In my case it´s a false positive

    Reply
  • Thank you so much.

    Reply

Leave a Reply

Menu