Thx for the reply but how come I am able to use the exact method in LAN (works with witness server). I already checked that the certificate used by the A is installed on C's master [database] and all the certificates' thumbprint version are match. Here's my sqls for creating certificates as trusted connection :
-----------------------------------------------------------------------------------------------------------------------------------
1) Server A (Principal)
-------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY --------
BACKUP DATABASE abc
To DISK = 'C:\abc.bak'
WITH FORMAT
GO
BACKUP LOG abc
TO DISK = 'C:\abc_Log.bak'
WITH FORMAT
DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE A_PRIMARY_CERT
GO
DROP MASTER KEY
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '123456'
GO
CREATE CERTIFICATE A_PRIMARY_CERT
WITH SUBJECT = 'A_PRIMARY_CERT for database mirroring',
START_DATE = '01/11/2006', EXPIRY_DATE = '01/01/2099'
GO
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT= 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE A_PRIMARY_CERT,
ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = PARTNER)
GO
BACKUP CERTIFICATE A_PRIMARY_CERT
TO FILE = 'C:\certificates\A_PRIMARY_CERT.cer'
GO
-- Copy A_PRIMARY_CERT to server B & C (Location path: 'C:\certificates)
2. Server B (Mirror)
/* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */
RESTORE DATABASE abc
FROM DISK = 'C:\abc.bak'
WITH NORECOVERY
GO
RESTORE LOG abc
FROM DISK = 'C:\abc_Log.bak'
WITH NORECOVERY
DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE B_SECONDARY_CERT
GO
DROP MASTER KEY
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '123456'
GO
CREATE CERTIFICATE B_SECONDARY_CERT
WITH SUBJECT = A_SECONDARY_CERT for database mirroring',
START_DATE = '01/11/2006', EXPIRY_DATE = '01/01/2099'
GO
CREATE ENDPOINT Mirroring
STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE B_SECONDARY_CERT,
ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL)
GO
BACKUP CERTIFICATE B_SECONDARY_CERT
TO FILE = 'C:\certificates\B_SECONDARY_CERT.cer'
GO
-- Copy B_SECONDARY_CERT to server A & C (Location path: 'C:\certificates)
3. Server C (Witness)
/* -------- 3. ENABLE OUTBOUND CONNECTIONS ON THE WITNESS -------- */
DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE C_WITNESS_CERT
GO
DROP MASTER KEY
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '123456'
GO
CREATE CERTIFICATE C_WITNESS_CERT
WITH SUBJECT = 'C_WITNESS_CERT for database mirroring',
START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
GO
CREATE ENDPOINT Mirroring
STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE C_WITNESS_CERT,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = WITNESS)
GO
BACKUP CERTIFICATE C_WITNESS_CERT
TO FILE = 'C:\certificates\C_WITNESS_CERT.cer'
GO
-- Copy C_WITNESS_CERT to server A & B (Location path: 'C:\certificates)
4) Server A (Principal)
-------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY --------
/* enable inbound from the mirror */
DROP CERTIFICATE B_SECONDARY_CERT
GO
DROP USER MIRROR_SECONDARY_USER
GO
DROP LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE LOGIN MIRROR_SECONDARY_LOGIN
WITH PASSWORD = '123456'
GO
CREATE USER MIRROR_SECONDARY_USER
FOR LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE CERTIFICATE B_SECONDARY_CERT
AUTHORIZATION MIRROR_SECONDARY_USER
FROM FILE = 'C:\certificates\B_SECONDARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_SECONDARY_LOGIN
GO
/* enable inbound from the witness */
DROP CERTIFICATE C_WITNESS_CERT
GO
DROP USER MIRROR_WITNESS_USER
GO
DROP LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE LOGIN MIRROR_WITNESS_LOGIN
WITH PASSWORD = '123456'
CREATE USER MIRROR_WITNESS_USER
FOR LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE CERTIFICATE C_WITNESS_CERT
AUTHORIZATION MIRROR_WITNESS_USER
FROM FILE = 'C:\certificates\C_WITNESS_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_WITNESS_LOGIN
GO
5) Server B (Mirror)
/* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */
/* enable inbound from the primary */
DROP CERTIFICATE A_PRIMARY_CERT
GO
DROP USER MIRROR_PRIMARY_USER
GO
DROP LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE LOGIN MIRROR_PRIMARY_LOGIN
WITH PASSWORD = '123456'
GO
CREATE USER MIRROR_PRIMARY_USER
FOR LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE CERTIFICATE A_PRIMARY_CERT
AUTHORIZATION MIRROR_PRIMARY_USER
FROM FILE = 'C:\certificates\A_PRIMARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_PRIMARY_LOGIN
GO
/* enable inbound from the witness */
DROP CERTIFICATE C_WITNESS_CERT
GO
DROP USER MIRROR_WITNESS_USER
GO
DROP LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE LOGIN MIRROR_WITNESS_LOGIN
WITH PASSWORD = '123456' -- real password used
GO
CREATE USER MIRROR_WITNESS_USER
FOR LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE CERTIFICATE C_WITNESS_CERT
AUTHORIZATION MIRROR_WITNESS_USER
FROM FILE = 'C:\certificates\C_WITNESS_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_WITNESS_LOGIN
GO
6) Mirror (Server C)
/* -------- 6. ENABLE INBOUND CONNECTIONS ON THE WITNESS -------- */
/* enable inbound from the mirror */
DROP CERTIFICATE B_SECONDARY_CERT
GO
DROP USER MIRROR_SECONDARY_USER
GO
DROP LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE LOGIN MIRROR_SECONDARY_LOGIN
WITH PASSWORD = '123456'
GO
CREATE USER MIRROR_SECONDARY_USER
FOR LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE CERTIFICATE B_SECONDARY_CERT
AUTHORIZATION MIRROR_SECONDARY_USER
FROM FILE = 'C:\certificates\B_SECONDARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_SECONDARY_LOGIN
GO
/* enable inbound from the primary */
DROP CERTIFICATE A_PRIMARY_CERT
GO
DROP USER MIRROR_PRIMARY_USER
GO
DROP LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE LOGIN MIRROR_PRIMARY_LOGIN
WITH PASSWORD = '123456'
GO
CREATE USER MIRROR_PRIMARY_USER
FOR LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE CERTIFICATE A_PRIMARY_CERT
AUTHORIZATION MIRROR_PRIMARY_USER
FROM FILE = 'C:\certificates\A_PRIMARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_PRIMARY_LOGIN
GO
-----------------------------------------------------------------------------------------------------------
Is there any wrong for the t-sql scripts Hope can get further assistance here. Thx.
Best Regards,
Hans