Manage the logon (start) account and password of the SQL Server service

Manage the logon (start) account and password of the SQL Server service

Today, I handled an alwaysOn problem-the secondary copy is always displayed [not synchronized-suspicious] due to insufficient disk space. In the log, we can see that the database is suspended and the master copy is out of sync. I thought that I only needed to clean up the disk of the secondary replica, free up some space, and restart the SQL Server service (restart the database from the suspended state to the online state, then let alwaysOn re-start synchronization ).
However, the restart failed !!!
In the operating system log, the cause of SQL Server startup failure is: (startup account) incorrect user name and password !!!

 

At the beginning of alwaysOn, the figure was convenient, and a domain administrator's username and password were used directly. Later, due to the security policy, the password of this account was changed again, at that time, no one remembers to change the SQL Server startup account password synchronously. In normal cases, as long as the SQL Server is not restarted and the password is not changed, the correct password must be used after the restart. Otherwise, this error occurs.
To solve this problem, you only need to change it to the correct password.

Even so, alwaysOn still won't immediately resume synchronization. From the database log, we can see that another unfortunate thing happened:

Database login ing login attempt failed with error: 'Connection handshake failed. An OS call failed (8009030c) x8009030c (login failed ). State.67 '. [client: 10.1.2.2]

 

10.1.2.2 is the IP address of the alwaysOn master copy. According to the error message, the database image endpoint of the master copy (AlwaysOn communicates with the database image endpoint) cannot log on to the secondary copy.
This is an account logon issue. We just modified the Logon account password of the secondary copy, but did not change the password of the primary copy. The primary copy still uses an invalid password to access the image endpoint of the secondary copy, the secondary copy will naturally reject this connection request. Therefore, we still need to modify the password of the primary copy Logon account (during non-business hours) and then restart SQL Server.

After the database image endpoint re-establishes the connection, this error will not occur again, but alwaysOn will not resume synchronization at this time, you also need to right-click the available database on the secondary copy and select "recover data movement". Since then, alwaysOn has started to resume synchronization.

This problem can be avoided. If the SQL Server startup account used a separate and dedicated account at that time, this problem would not occur, in fact, we also recommend that such an account be separated from the business account as much as possible to avoid mutual influence.

This article permanently updates the link address: