Use Certificate to secure the endpoint used for AlwaysOn Availability Group if local account used for SQL Server

 

Commands in SSOSQL1	Commands in SSOSQL2
USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!QAZXSW#$%e'; GO USE master; GO CREATE CERTIFICATE SSOSQL1_cert WITH SUBJECT = 'SSOSQL1 certificate', START_DATE = '20141031', EXPIRY_DATE = '20241031'; GO BACKUP CERTIFICATE SSOSQL1_cert TO FILE = 'C:\Backup\SSOSQL1_cert.cer'; GO ALTER ENDPOINT [Hadr_endpoint] for database_mirroring( AUTHENTICATION = CERTIFICATE SSOSQL1_cert ) GO	USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!QAZXSW#$%e'; GO USE master; GO CREATE CERTIFICATE SSOSQL2_cert WITH SUBJECT = 'SSOSQL2 certificate', START_DATE = '20141031', EXPIRY_DATE = '20241031'; GO BACKUP CERTIFICATE SSOSQL2_cert TO FILE = 'C:\Backup\SSOSQL2_cert.cer'; GO ALTER ENDPOINT [Hadr_endpoint] for database_mirroring( AUTHENTICATION = CERTIFICATE SSOSQL2_cert ) GO
Copy SSOSQL1_cert.cer to Server SSOSQL2	Copy SSOSQL2_cert.cer to Server SSOSQL1
USE master; CREATE LOGIN [SSOSQL2_Login] WITH PASSWORD = '1Sample_Strong_Password!!#'; GO CREATE USER [SSOSQL2_User] FOR LOGIN [SSOSQL2_Login]; GO CREATE CERTIFICATE SSOSQL2_Cert AUTHORIZATION SSOSQL2_User FROM FILE = 'C:\backup\SSOSQL2_cert.cer' GO GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [SSOSQL2_Login]; GO	USE master; CREATE LOGIN [SSOSQL1_Login] WITH PASSWORD = '1Sample_Strong_Password!!#'; GO CREATE USER [SSOSQL1_User] FOR LOGIN [SSOSQL1_Login]; GO CREATE CERTIFICATE SSOSQL1_Cert AUTHORIZATION SSOSQL1_User FROM FILE = 'C:\backup\SSOSQL1_cert.cer' GO GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [SSOSQL1_Login]; GO

Possible error message in the errorlog if certification not configured correctly:

Date 11/13/2014 8:43:50 AM Log SQL Server (Current - 11/13/2014 8:26:00 AM) Source Logon Message Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 192.168.6.140]

Date 11/13/2014 8:43:53 AM Log SQL Server (Current - 11/13/2014 8:26:00 AM) Source Logon Message Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89.'. [CLIENT: 192.168.6.140]

 

Successful message:

Date 11/13/2014 8:49:47 AM Log SQL Server (Current - 11/13/2014 8:26:00 AM) Source spid25s Message A connection for availability group 'TestAG' from availability replica 'SSOSQL2' with id [20F90002-3F1A-440E-B8B1-BBDF23CAC3AC] to 'SSOSQL1' with id [DF75459C-A7C4-423A-92B8-DFE097ECAF06] has been successfully established. This is an informational message only. No user action is required.