SQL Server 2019 Mirroring Server Network Address Cannot Be Reached

Question

I am trying to setup mirroring between two servers with separate sql server instances but every time I have received an error message with the following details:

An error occurred while starting mirroring.

Alter failed for Database 'WC_PROD'. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The server network address "TCP://ServerName.DOMAIN.local:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (.Net SqlClient Data Provider)

Server Name: ServerName,5022 Error Number: 1418 Severity: 16 State: 1 Line Number: 1

I've reviewed the documentation from the learn microsoft sites for Specify Network Address ; Setting up using windows authentication ; and the initial guide when setting up the initial attempts at mirroring and troubleshooting the above error.

Several other posts here, and here, recommended confirming port availability with netsat, which I can confirm the ports are open and listening. I've also confirmed the user roles are configured in the sysadmin group and tried several different port and TCP address configurations. I was unable to connect to either server from the other with the Telnet command as well.

If anyone has any additional advice or knows more about the error number than cannot reach server it would be greatly appreciated. I'm new to SQL server so I'm doing what I can while running in circles here and may be missing something obvious.

The end goal is to have an identical backup in the event of a DR scenario so we do not have any data loss hence the mirroring.

Edit: Both servers are running on Windows server 2019. Using Telnet server.domain.local:5022 from either host to the other results in "Could not open connection to the host, on port 23: Connect failed" despite a specific firewall rule to allow the servers to communicate with each other through ports 23 and 5022.

Edit2: I deleted the previously created endpoints and went through from the beginning, I then ran into an error when setting the mirror endpoint to 5022, changed that to 7022 and successfully created the endpoints with no errors. Once I started the mirror session I ran into the same error. The server that it is failing to connect to is the Principal server that I am setting this up from.

Edit3: Some digging into further logs found a new error message stating that the user failed with error, login does not have CONNECT permission on the endpoint. Working on getting that configured now although the account is the computer and not a user, and neither is the configured service account.

Answer 1

# Variables
$principalServer = "PrincipalServer"
$mirrorServer = "MirrorServer"
$databaseName = "YourDatabase"
$backupPath = "C:\Backup\YourDatabase.bak"
$port = 5022

# Backup and restore database
Invoke-Sqlcmd -ServerInstance $principalServer -Query "BACKUP DATABASE [$databaseName] TO DISK = '$backupPath'"
Invoke-Sqlcmd -ServerInstance $mirrorServer -Query "RESTORE DATABASE [$databaseName] FROM DISK = '$backupPath' WITH NORECOVERY"

# Create endpoints
$endpointQuery = @"
CREATE ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = $port, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE=PARTNER, AUTHENTICATION=WINDOWS NEGOTIATE, ENCRYPTION=REQUIRED ALGORITHM RC4)
GO
"@
Invoke-Sqlcmd -ServerInstance $principalServer -Query $endpointQuery
Invoke-Sqlcmd -ServerInstance $mirrorServer -Query $endpointQuery

# Grant CONNECT permission to service accounts
$grantQuery = @"
GRANT CONNECT ON ENDPOINT::[Mirroring] TO [DOMAIN\ServiceAccount]
GO
"@
Invoke-Sqlcmd -ServerInstance $principalServer -Query $grantQuery
Invoke-Sqlcmd -ServerInstance $mirrorServer -Query $grantQuery

# Set up mirroring
$setPartnerQueryPrincipal = "ALTER DATABASE [$databaseName] SET PARTNER = 'TCP://$mirrorServer.DOMAIN.local:$port'"
$setPartnerQueryMirror = "ALTER DATABASE [$databaseName] SET PARTNER = 'TCP://$principalServer.DOMAIN.local:$port'"
Invoke-Sqlcmd -ServerInstance $principalServer -Query $setPartnerQueryPrincipal
Invoke-Sqlcmd -ServerInstance $mirrorServer -Query $setPartnerQueryMirror

Write-Host "Mirroring setup completed."