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.
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.
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.
- Go to configuration manager and choose properties of SQL Server (MSSQLSERVER). If you have named instance than its SQL Server (Instance Name)
- Go to “Log On” tab and provide a user name and password for a domain account.
- 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)