July 21, 2011 at 11:00 am
Hello everyone.
I am having a small issue with a mirroring config and was hoping that someone could help me with it.
I am getting the following error message when I try to setup mirroring:
Database Mirroring login attempt failed with error: '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. State 89.'. [CLIENT: 192.168.100.64]
Here is my script:
On the principal I run:
USE MASTER;
-- Create the Master Key should it not already be created.
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE NAME = '##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Blah*'
END
-- Create the Certificate needed
IF NOT EXISTS (SELECT * FROM SYS.Certificates WHERE NAME = 'Server1_Principal')
BEGIN
CREATE CERTIFICATE Server1_Principal
WITH SUBJECT = 'Certificate for principal server login authentication'
END
-- Create the endpoint that will be used for mirroring
IF NOT EXISTS (SELECT * FROM SYS.ENDPOINTS WHERE NAME = 'Mirror' AND Type_Desc = 'DATABASE_MIRRORING')
BEGIN
CREATE ENDPOINT Mirror
STATE = STARTED
AS TCP (
LISTENER_PORT = 9200
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE Server1_Principal
, ENCRYPTION = DISABLED
, ROLE = PARTNER
)
END
-- Now Backup the Certificate
BACKUP CERTIFICATE Server1_Principal TO FILE = 'F:\Certificates\Server1_Principal.cer'
On the mirror I run:
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE NAME = '##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Blah*'
END
-- Create the Certificate needed
IF NOT EXISTS (SELECT * FROM SYS.Certificates WHERE NAME = 'Server2_Mirror')
BEGIN
CREATE CERTIFICATE Server2_Mirror
WITH SUBJECT = 'Certificate for Mirror server login authentication'
END
-- Create the endpoint that will be used for mirroring
IF NOT EXISTS (SELECT * FROM SYS.ENDPOINTS WHERE NAME = 'Mirror' AND Type_Desc = 'DATABASE_MIRRORING')
BEGIN
CREATE ENDPOINT Mirror
STATE = STARTED
AS TCP (
LISTENER_PORT = 9200
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE Server2_Mirror
, ENCRYPTION = DISABLED
, ROLE = PARTNER
)
END
-- Now Backup the Certificate
BACKUP CERTIFICATE Server2_Mirror TO FILE = 'F:\Certificates\Server2_Mirror.cer'
I now copy over the certificates over to the other server.
After coping the certificates over I setup the logins and such as follows:
Principal:
USE MASTER;
/*************************** Create logins for mirrored server *************************************/
IF NOT EXISTS (SELECT * FROM SYS.SERVER_PRINCIPALS WHERE NAME = 'Server2_Mirror_login')
BEGIN
CREATE LOGIN Server2_Mirror_login WITH PASSWORD = 'BlahBlah*'
END;
-- Now create the user for the login above.
IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = 'Server2_Mirror_user')
BEGIN
CREATE USER Server2_Mirror_user FOR LOGIN Server2_Mirror_login
END;
-- Create the certificate based upon the certificate from the other mirrored server so that we can then have correct authentication
-- for our logins
IF NOT EXISTS (SELECT * FROM SYS.Certificates WHERE NAME = 'Server2_Mirror')
BEGIN
CREATE CERTIFICATE Server2_Mirror
AUTHORIZATION Server2_Mirror_user
FROM FILE = 'F:\Certificates\Server2_Mirror.cer'
END;
-- Finally, give the permissions
GRANT CONNECT ON ENDPOINT::Mirror to Server2_Mirror_login
and on Mirror:
USE MASTER;
/*************************** Create logins for mirrored server *************************************/
IF NOT EXISTS (SELECT * FROM SYS.SERVER_PRINCIPALS WHERE NAME = 'Server1_Principal_login')
BEGIN
CREATE LOGIN Server1_Principal_login WITH PASSWORD = 'BlahBlah*'
END
-- Now create the user for the login above.
IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = 'Server1_Principal_user')
BEGIN
CREATE USER Server1_Principal_user FOR LOGIN Server1_Principal_login
END
-- Create the certificate based upon the certificate from the other mirrored server so that we can then have correct authentication
-- for our logins
IF NOT EXISTS (SELECT * FROM SYS.Certificates WHERE NAME = 'Server1_Principal')
BEGIN
CREATE CERTIFICATE Server1_Principal
AUTHORIZATION Server1_Principal_user
FROM FILE = 'F:\Certificates\Server1_Principal.cer'
END
-- Finally, give the permissions
GRANT CONNECT ON ENDPOINT::Mirror to Server1_Principal_login
After this is done, I take a backup of the database on the principal and copy it over to the mirror server and restore with using (NORecovery) as required.
Then I run the following command on the Mirror Server:
ALTER DATABASE MirrorTest SET PARTNER = 'tcp://Server1.MyDomain.local:9200' -- setup connect to Principal
Then I try to run the following on the Principal Server:
ALTER DATABASE MirrorTest SET PARTNER = 'TCP://Server2.MyDomain.local:9200'-- Connect to Mirror
I get the following error message when I run this:
The server network address "TCP://Server2.MyDomain.local:9200" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.
When Looking at the error log, I get the message at the top of this post that I am asking about
Thanks for the help and suggestions.
Fraggle.
July 22, 2011 at 8:59 am
I resolved this. Figured I would let everyone know what happened.
When we intially tried getting this setup, there was a database user account that was not getting removed. This user account was tied to one of the certificates, and was retaining that certificate information, even after the certificate had been dropped. And since the scripts I use, check to see if the user is already created, I never noticed on the creation of the process.
Hope this helps someone in the future.
Fraggle.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply