SQL2005镜像故障处理

SSB – Connection handshake failed

 

 

The following shows you some kinds of SSB exception when enabling Transport Security and Dialog Security in the cross-server Service Broker conversation.

1. Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found.

 

HINT: Usually this error is related to the EndPoint authentication, so the problem is from the certificates in MASTER DBs in two SQL SERVER instances. Please make sure that the right certificates are deployed and referenced. Look into master.sys.certificates in two respectively SQL SERVERS and validate the certificate cert_serial_number or thumbprint matches between two instances.

 

2. Connection handshake failed. The certificate used by the peer is invalid due to the following reason: The database principal has no mapping to a server principal..

 

HINT: You are using certificates for adjacent transport security. The certificate used by the peer belongs to a user in master DB who is not mapped to a login. This could happen for the following cases:

(1) You created a user in master without login.

(2) You created a user in master with login, but later dropped that login, thus orphaning that user.

The fix would be to create a SQL login, then create a user in master for this login and alter authorization of the peer’s certificate to be owned by this user. Next grant connection permission on the service broker endpoint to the newly created login.

 

You can refer to the following SQL SCRIPT for help.

-- Create a new login & user in the MASTER system database

Create Login Demo_Login

       With password=’YOURPASSWORD’

GO

 

Create User Demo_User

       For Login Demo_Login

GO

 

-- Give authorization permissions

Create Certificate YOURCERTIFICATENAME

       Authorization Demo_User

       From File=’THE PEERS CERTIFICATE FILE’

GO

 

-- Grant CONNECT permissions to the associated endpoint

Grant Connect on EndPoint::DB_ENDPOINT to Demo_Login

GO