Hello,
Few days back i got this error while setting up mirroring so thought to share this knowledge with everyone.
1. After restoring database on Mirror server, i tried to establish mirroring but it failed with below error
Message
Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'
2. As soon as i receive this error, the database on Mirror server database went into “In Recovery” or “Suspect” mode and it wont restore any new files mentioning that database is part of mirroring even though it failed so to fix that, we ran the command to break the mirroring on Mirror server
ALTER DATABASE DBName SET PARTNER OFF
3. Post that i thought it might be because on Primary server, mirroring is configured using Certificate so that might be the cause of it so i installed certificates on Mirror server and added entries of it on Primary server and vice-versa, I used below link to configure Mirroring via Certificate authentication
https://www.mssqltips.com/sqlservertip/1705/implementing-database-mirroring-in-sql-server-2005-across-domains/
4. The above solution also didn’t worked, then i was checking Endpoints on both servers then after long troubleshooting i was able to found out that main issue for this error.
CREATE ENDPOINT [Endpoint_Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE [ServerName_cert]
, ENCRYPTION = REQUIRED ALGORITHM RC4)
CREATE ENDPOINT [Endpoint_Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE [ServerName_cert]
, ENCRYPTION = REQUIRED ALGORITHM AES_192)
5. If you the see the highlighted part in orange in principal i have RC4 algorithm and on mirror i have AES 92 algorithm.
5. I re-created Endpoint on mirror server by using RC4 as encryption algorithm, issue was resolved.
Few days back i got this error while setting up mirroring so thought to share this knowledge with everyone.
1. After restoring database on Mirror server, i tried to establish mirroring but it failed with below error
Message
Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'
2. As soon as i receive this error, the database on Mirror server database went into “In Recovery” or “Suspect” mode and it wont restore any new files mentioning that database is part of mirroring even though it failed so to fix that, we ran the command to break the mirroring on Mirror server
ALTER DATABASE DBName SET PARTNER OFF
3. Post that i thought it might be because on Primary server, mirroring is configured using Certificate so that might be the cause of it so i installed certificates on Mirror server and added entries of it on Primary server and vice-versa, I used below link to configure Mirroring via Certificate authentication
https://www.mssqltips.com/sqlservertip/1705/implementing-database-mirroring-in-sql-server-2005-across-domains/
4. The above solution also didn’t worked, then i was checking Endpoints on both servers then after long troubleshooting i was able to found out that main issue for this error.
CREATE ENDPOINT [Endpoint_Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE [ServerName_cert]
, ENCRYPTION = REQUIRED ALGORITHM RC4)
CREATE ENDPOINT [Endpoint_Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE [ServerName_cert]
, ENCRYPTION = REQUIRED ALGORITHM AES_192)
5. If you the see the highlighted part in orange in principal i have RC4 algorithm and on mirror i have AES 92 algorithm.
5. I re-created Endpoint on mirror server by using RC4 as encryption algorithm, issue was resolved.
No comments:
Post a Comment