2

all. (I have read through all the other topics related to this one, and I cannot find anything to is similar to this issue, unless there is one hidden that I have missed.)

OK.

SQL Server Always On High Availability

When going through the Wizard, it completes successfully, all the databases in the joined nodes are all set as Name (Restoring...)

Here is a picture of what the SSMS tree looks like for the Primary and Secondary Servers Group listings. [enter image description here

When connecting to the Windows Core through [b]Computer Mansagement[/b] To access the SQL Server Configuration Manager (SQL 2019). SQL Server Services is BLANK.
enter image description here As you can see, this is like this when connecting to ALL Windows Core with SQL Server instances installed.
So, I cannot access this part of SQL Server Services, to administer it on any on the VM's

Wanted to also show the DASHBOARD for SQLCore-01
Which, as you can see, shows all the other SQLCore-02 - SQLCore-05 as bad.
enter image description here

I have checked all the ports that are set for SQL Server and the group, and they are there are reachable.
I am at a complete loss. I have tried and searched and tried some more, but cannot find anything to resolve this issue. I need this working by the end of the weekend, or I am going to have to go back to using the SQL Cluster again, which is what I want to stay away from. I like this method better, as it had multiple SQL Servers with databases on each server—more failover than the SQL Cluster.

Ports (5022) are open on all machines
Error in SQL Log is:

Database Mirroring login attempt by user "Domain\sqlengine" failed with error: Connection handshake failed. The login domain\sqlengine does not have CONNECT permission on the endpoint. State 84.'.

The user domain\sqlengine
I ran this command on each instance 

GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [domain\sqlengine]

The output is: Commands completed successfully.
But it still shows that same error in the logs.

I read this blog post SQL Authority .com
However, I cannot access through Computer Management, the
SQL Server Services for SQL 2019, for whatever reason. See Image.
Computer Management - SQL Server Services is BLANK for SQL 2019

Another note. Going by this article here. Always On Replica Disconnected After Changing SQL Server Service Account

The user is domain.local\sqlengine
This user I added to the SQL Server Logins for each SQL Server.
This is the account that is used for logging all actions of which AG will be doing but still have the same error message.

I found this page. Availability replica is in disconnected
I used the script provided on the article and ran I then restarted SQL Server on each Instance. 

Restart-Service -Force 'MSSQL$SQL2019'

But to no avail. Still disconnected.

I also found this as well.
How to change SQL Server Service Account using PS
I tried running the script.
But I get an error.

You cannot call a method on a null-valued expression. At D:\Files\SQL.ps1:23 char:1 $ChangeService.SetServiceAccount($Uname, $PWord) Which is point at this section. 

$UName="domain\sqlengine"
$PWord="**********"
$ChangeService.SetServiceAccount($UName, $PWord)

I was going to try and change the password, as maybe the password might have gotten corrupted or something. But, with this error, I am at a standstill.

Wayne

Improve this question
5
  • Do you have port 5022 open on all servers?
    – Tony Hinkle
    May 15, 2020 at 11:59
  • Are there any AG-related errors in the SQL Server error log for any of the replica servers? Have you been through the troubleshooting steps here? Availability Group reporting disconnected replica
    – Josh Darnell
    May 15, 2020 at 12:20
  • My response is added to the bottom of the. I should have added in the ports and errors, but it was late, and I was tired.
    – CodingEE
    May 15, 2020 at 22:32
  • Added in a bunch more information to the bottom section.
    – CodingEE
    May 16, 2020 at 17:47
  • Figured it out. Working like a charm. Posted answer below.
    – CodingEE
    May 17, 2020 at 6:50

2 Answers 2

Reset to default
1

I just figured it out.

(You have to have the [Domain\sqlengine] added to the Logins for SSMS.)

In Management Studio
Click [Logins]

Perform the following (2) actions

Action #1
Double Click [Domain\sqlengine] Click on [Server Roles] (I have the following checked, I actually had them all checked for testing, and now these are the ones that are needed.)
dbcreator
public (default cannot uncheck this)
serveradmin
sysadmin

(This is what did the trick, this is what I was missing.)

Action #2
Click on [User Mapping]
Put a check on [Master]

Click [OK]
Then Re-run the Always On Wizard

This is all it took.
For the last few days. I have been banging my head against the wall to get this working.
And it was right here all along.

Hope this will help someone out.

Wayne

Improve this answer
2
  • Mark your answer as accepted so that it would help others in case they face similar issue.
    – Learning_DBAdmin
    May 17, 2020 at 10:13
  • I forgot about doing it this morning when I posted it. Just marked it.
    – CodingEE
    May 17, 2020 at 16:29
0

Nevertheless there is an answer here, please note that it is just a potentially dangerous workaround.

SQL Server database engine service account does not need any additional privileges except public role and endpoint connect permission.

As we may see from the screenshot, the topic starter uses SQL Server version 15.0.2000.5 which is VANILLA RTM (the first released build).

If you face the same issue please install the latest cumulative update available. The same for older versions (2012, 2014, 2016, 2017).

You may check the full list here: https://sqlserverbuilds.blogspot.com/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.