Friday, November 14, 2014

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

 

image

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.