Well, here is an odd error message that had me looking in the wrong direction for a few minutes.

While attempting to resolve an asynchronous database mirroring issue on a SQL Server 2012 Enterprise instance running on Windows 2008 R2 Server, I came across this error:

I was able to resolve the issue after a few minutes, but took the following steps to get there.

  1. On both the local and remote SQL instances, did a ping test to their respective partners FQDN … success.
  2. On both the local and remote SQL instances, did a ‘telnet’ to their respective partners FQDN and the mirroring endpoint, 7022 … success.
  3. Went into the services.msc control panel interface and verified that both the SQL service and the SQL agent service were running under the same account. While this was true in this case, I’m not 100% sure that this is actually needed, as the SQL agent service was not running and I knew that the agent service was not required to run for database mirroring to be monitored. This service is only required if you are planning on using the ‘Database Mirroring Monitor’ provided by Microsoft within SSMS.
  4. Checked that each endpoint on the local and remote instance were in a started state by running the following t-sql command … success.
  5. Checked in SQL Server Configuration Manager that the TCP protocol for both the local and remote instance was enabled … success.
  6. Also using the same t-sql statement in 4 above on each server, verified that the endpoints were using the same protocol, in this case, TCP; verified their role was PARTNER and lastly verified that they were using the same encryption algorithm.
  7. Since the error message was pointing me in the network direction saying that the endpoints were basically unreachable, I then checked the Windows firewall status. While this was turned on for both the local and remote servers due to a security GPO in place, further looking at the rule indicated that the port 7022 was open, which was good. This gave some reassurance that my telnet step above was valid as well.
  8. Then, moving onto how the mirrored database was restored on the remote system; this was verified to have been done using the ‘WITH NORECOVERY’ option.

After all of this I was still getting the dreaded Error: 1418 from SQL. While frustrating, it dawned on me. Looking back at what I had done in step 3, that is, checking what accounts the services were running under, I did notice that the SQL service was running under a local account on each server. This alone will not pose a problem for database mirroring to work, but if it doesn’t have the proper permissions at the operating system level, database mirroring will not be successful.

In this case to resolve the problem, I put the local SQL Server service account into the local Adminstrators group, and what do you know, I was able to successfully establish a database mirroring connection without any issues. Now, why Microsoft would give specifically error 1418 for this issue still boggles me, because as I read the error message, this is telling me that there is a network connectivity problem. Anyway, problem resolved. Hopefully these steps assists others in troubleshooting database mirroring in SQL Server.

Misleading SQL database mirroring error 1418
Tagged on: