Mirroring Connection Problem

Question

 

Greetings,
             I am trying to setup database mirroring on two seperate servers running SQL 2005 SP2.  I would eventually like to add a witness. I have tried the SQL wizard with and without a witness to no avail.  I am now trying to use certificates.  I have changed both servers to use a domain administrator logon to run the services.  I am getting the following message each time I try to alter the database on the principal after I have setup everything on the mirror server.
Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://10.99.99.02:5022" 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.

Here is the TSQL statements I am using on the principal server:
-- Step 1
create master key encryption by password = 'xxxxxx'

-- Step2
create certificate Principal_Cert
with subject = 'Principal Certificate',
expiry_date = '12/31/9999'

--Step 3
backup certificate Principal_Cert to file = 'c:\Principal_cert.cer'

--Step4
create endpoint Endpoint_Mirroring
state = started
as TCP (listener_port = 5022, listener_ip = all)
for database_mirroring (authentication = certificate Principal_Cert,
encryption = required algorithm AES,
role = all)

-- Backup database SPOT - go to Mirror Server

-- Step 5 after setup on Mirror server

create login Mirror_login with password = 'xxxxxxxx'

-- Step 6
create user Mirror_user for login Mirror_login

--Step 7
create certificate Mirror_Cert
authorization Mirror_user
from file = 'c:\Mirror_cert.cer'
 
--Step 8
use master
grant connect on endpoint::Endpoint_Mirroring to [Mirror_login]

-- Step 9
alter database spot
set partner = 'TCP://10.99.99.02:5022'

****  Getting message here **********

-- Checks

select * from sys.server_principals

select * from sys.sysusers

select * from sys.certificates

select * from sys.database_mirroring_endpoints

SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
         t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
         e.connection_auth_desc
FROM   sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON     e.endpoint_id = t.endpoint_id

Here is the TSQL statements I am using on the mirror server.  This all works fine.

-- Step 1
create master key encryption by password = 'xxxxxxxx'

-- Step2
create certificate Mirror_Cert
authorization dbo
with subject = 'Principal Certificate',
EXPIRY_DATE = '12/31/9999'
ACTIVE FOR BEGIN_DIALOG = ON

--Step 3
backup certificate Mirror_Cert to file = 'c:\Mirror_cert.cer'

--Step4
create endpoint Endpoint_Mirroring
state = started
as TCP (listener_port = 5022, listener_ip = all)
for database_mirroring (authentication = certificate Mirror_Cert,
encryption = required algorithm AES,
role = all)

-- Step 5

create login Principal_login with password = 'xxxxxxxx'

-- Step 6
create user Principal_user for login Principal_login

--Step 7
create certificate Principal_Cert
authorization Principal_user
from file = 'c:\Principal_cert.cer'
ACTIVE FOR BEGIN_DIALOG = ON
 
--Step 8
use master
grant connect on endpoint::Endpoint_Mirroring to [Principal_login]

-- Restore Step

RESTORE DATABASE SPOT
FROM DISK = 'C:\SPOT.bak'
WITH NORECOVERY

RESTORE log SPOT
FROM DISK = 'C:\SPOT_log.bak'
WITH NORECOVERY

-- Step 9
alter database spot
set partner = 'TCP://10.99.99.01:5022'

-- Checks

select * from sys.server_principals

select * from sys.sysusers

select * from sys.certificates

select * from sys.database_mirroring_endpoints

SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
         t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
         e.connection_auth_desc
FROM   sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON     e.endpoint_id = t.endpoint_id

select *
FROM   sys.database_mirroring_endpoints

 

Does anyone have any ideas? 

Thanks

 

Answer 1

Create end point for witness server.

Answer 2

Please check the windows event log for any errors. that would give you good reason why this failling.

Worth check the account under sql server running on both instances having enough permissions

have you configured endpoint under right account, does it have enough permissions on both servers?

 

check here for more http://msdn.microsoft.com/en-us/library/aa337361(v=SQL.90).aspx

---

http://uk.linkedin.com/in/ramjaddu

Answer 3

I'll need some more info but let's walk through your certificate setup scripts from above.  So....  Here goes...

Here is the TSQL statements I am using on the principal server:
-- Step 1
create master key encryption by password = 'xxxxxx'

-- Step2
create certificate Principal_Cert
with subject = 'Principal Certificate',
expiry_date = '12/31/9999' - expiry date is not required, when initially working with this and learning keep things simple

--Step 3
backup certificate Principal_Cert to file = 'c:\Principal_cert.cer'

--Step4
create endpoint Endpoint_Mirroring
state = started
as TCP (listener_port = 5022, listener_ip = all)
for database_mirroring (authentication = certificate Principal_Cert,
encryption = required algorithm AES,
role = all)

-- Backup database SPOT - go to Mirror Server - move your db backup to after all of the connections, logins, etc... have been setup on both servers.

-- Step 5 after setup on Mirror server

create login Mirror_login with password = 'xxxxxxxx'

-- Step 6
create user Mirror_user for login Mirror_login

--Step 7
create certificate Mirror_Cert
authorization Mirror_user
from file = 'c:\Mirror_cert.cer'

--Step 8
use master
grant connect on endpoint::Endpoint_Mirroring to [Mirror_login]

-- Step 9
alter database spot
set partner = 'TCP://10.99.99.02:5022'

**** Getting message here **********

-- Checks

select * from sys.server_principals

select * from sys.sysusers

select * from sys.certificates

select * from sys.database_mirroring_endpoints

SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
e.connection_auth_desc
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON e.endpoint_id = t.endpoint_id

Here is the TSQL statements I am using on the mirror server. This all works fine.

-- Step 1
create master key encryption by password = 'xxxxxxxx'

-- Step2
create certificate Mirror_Cert
authorization dbo
with subject = 'Principal Certificate',
EXPIRY_DATE = '12/31/9999' - again not require
ACTIVE FOR BEGIN_DIALOG = ON - not required as well

--Step 3
backup certificate Mirror_Cert to file = 'c:\Mirror_cert.cer'

--Step4
create endpoint Endpoint_Mirroring
state = started
as TCP (listener_port = 5022, listener_ip = all)
for database_mirroring (authentication = certificate Mirror_Cert,
encryption = required algorithm AES,
role = all)

-- Step 5

create login Principal_login with password = 'xxxxxxxx'

-- Step 6
create user Principal_user for login Principal_login

--Step 7
create certificate Principal_Cert
authorization Principal_user
from file = 'c:\Principal_cert.cer'
ACTIVE FOR BEGIN_DIALOG = ON - not required

--Step 8
use master
grant connect on endpoint::Endpoint_Mirroring to [Principal_login]

Let's move our DB backup here.

-- Restore Step

RESTORE DATABASE SPOT
FROM DISK = 'C:\SPOT.bak'
WITH NORECOVERY

RESTORE log SPOT
FROM DISK = 'C:\SPOT_log.bak'
WITH NORECOVERY

-- Step 9
alter database spot
set partner = 'TCP://10.99.99.01:5022'

 

Looks as though thing have been done correctly.  It should work if you backup the log again and restore it to the mirror and then attempt to alter the db on the primary.  If that doesnt work post the output of the items you have listed below and from the post referenced below.

-- Checks

select * from sys.server_principals

select * from sys.sysusers

select * from sys.certificates

select * from sys.database_mirroring_endpoints

SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
e.connection_auth_desc
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON e.endpoint_id = t.endpoint_id

select *
FROM sys.database_mirroring_endpoints

 

To keep the order of things straight as what you have done above jumps around some in the future use this (from a previous post of mine):

-- =====================================
-- Mirroring with Certificates Template
-- =====================================
-- CONFIGURE OUTBOUOND CONNECTIONS
---- ON PRINCIPAL SERVER - PRIMARY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword1';
GO
USE master;
CREATE CERTIFICATE PRIMARY_cert
WITH SUBJECT = 'PRIMARY certificate';
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE PRIMARY_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
BACKUP CERTIFICATE PRIMARY_cert TO FILE = 'C:\CERTS\PRIMARY_cert.cer';
GO

---- ON MIRROR SERVER - MIRROR
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword1';
GO
CREATE CERTIFICATE MIRROR_cert
WITH SUBJECT = 'MIRROR certificate for database mirroring';
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7025
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE MIRROR_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
BACKUP CERTIFICATE MIRROR_cert TO FILE = 'C:\CERTS\MIRROR_cert.cer';
GO

-- Configure Inbound Connections
---- ON PRINCIPAL SERVER PRIMARY
USE master;
CREATE LOGIN MIRROR_login WITH PASSWORD = 'P@ssword1';
GO
CREATE USER MIRROR_user FOR LOGIN MIRROR_login;
GO
CREATE CERTIFICATE MIRROR_cert
AUTHORIZATION MIRROR_user
FROM FILE = 'C:\CERTS\MIRROR_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIRROR_login];
GO

---- ON MIRROR SERVER MIRROR
USE master;
CREATE LOGIN PRIMARY_login WITH PASSWORD = 'P@ssword1';
GO
CREATE USER PRIMARY_user FOR LOGIN PRIMARY_login;
GO
CREATE CERTIFICATE PRIMARY_cert
AUTHORIZATION PRIMARY_user
FROM FILE = 'C:\PRIMARY_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [PRIMARY_login];
GO

-- BACKUP DATABASE ON PRINCIPAL
---- FULL BACKUP
USE dbname;
GO
BACKUP DATABASE dbname
TO DISK = 'C:\CERTS\dbname_Full.Bak'
WITH FORMAT,
MEDIANAME = 'dbnameBackups',
NAME = 'Full Backup of dbname';
GO

-- RESTORE DATABASE TO MIRROR
---- FULL BACKUP RESTORE - WITH NORECOVERY & MOVE
RESTORE DATABASE dbname
FROM DISK='C:\CERTS\dbname_Full.Bak'
WITH NORECOVERY,
MOVE 'dbname_Data' TO
'D:\Data\dbname_Data.mdf',
MOVE 'dbname_Log' TO
'D:\Data\dbname_Log.ldf';
GO

---- TRANSACTION LOG BACKUP
USE dbname;
GO
BACKUP LOG dbname
TO DISK = 'C:\CERTS\dbname_Log.trn'
WITH FORMAT,
MEDIANAME = 'dbnameBackups',
NAME = 'Log Backup of dbname';
GO

---- TRANSACTION LOG BACKUP RESTORE - WITH NORECOVERY
RESTORE LOG dbname
FROM DISK = 'C:\CERTS\dbname_Log.trn'
WITH NORECOVERY
MOVE 'dbname_Data' TO
'D:\Data\dbname_Data.mdf',
MOVE 'dbname_Log' TO
'D:\Data\dbname_Log.ldf';
GO

-- CONFIGURE MIRRORING PARTNERS
---- ON MIRROR SERVER MIRROR
ALTER DATABASE dbname
SET PARTNER = 'TCP://PRIMARYSERVERNAME.DOMAIN.com:7024';
GO

---- ON PRINCIPAL SERVER PRIMARY
ALTER DATABASE dbname
SET PARTNER = 'TCP://MIRRORSERVERNAME.DOMAIN.com:7025';
GO

Here is another post with some good information on troubleshooting mirroring from Uri Dimant and myself: http://social.technet.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/cfece276-5359-44a0-804d-102fe1271b54

---

MCM: Microsoft Certified Master - SQL Server 2008 | MCT: Microsoft Certified Trainer | MCITP: Database Administrator 2008 | MCITP: Database Developer 2008 | MCITP: Database Administrator | MCITP: Database Developer | MCDBA: Microsoft SQL Server 2000