Still need help with mirroring (Error 1418)

Hello again,

This is further to my previous post, which has had over thirty viewing but as yet no replies. Is there really no solution to this problem

I have now given up trying to use the wizard to get mirroring running, as although I'm not entirely sure, some stuff I've read implies that the wizard only works using Windows authentication. Maybe someone can confirm this. Either way, the wizard doesn't work for me (see my previous post).

So instead of using the wizard I've now tried to set up mirroring manually using SQL statements. Following are the steps I've taken. I've tried to replicated exactly what it says in the online documentation. At the end of the post are the SQL statements issued.

1. Enable encrypted outbound connections on the primary server
2. Enable encrypted outbound connections on the mirror server
3. Enable encrypted outbound connections on the witness server
4. Enable encrypted inbound connections on the primary server
5. Enable encrypted inbound connections on the mirror server
6. Enable encrypted inbound connections on the witness server
7. Set mirror's partner to the primary
8. Set primary's partner the mirror (EXPLOSION)

There might be more stuff to do after this, but here is where it breaks down. Again, the error is the same as before when using the wizard:

Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://10.152.58.243:7024" can not be reached or does not exist. Check the network address name and reissue the command.

Which is **INCORRECT** or at least highly unhelpful because:
i) netstat -abn shows the sql server process listening on port 7024; no other processes are listening on this port
ii) I can telnet to port 7024 on this machine and issue the 16 keystrokes

Please, someone help, I am crying tears of despair. SQL below.

Cheers,

Mike

/*
The following is a complete list of the SQL statements issued.
Please assume they are issued on the relevant master databases.
*/

/* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */

DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE BILL_PRIMARY_CERT
GO
DROP MASTER KEY
GO

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '**************' -- real password used
GO

CREATE CERTIFICATE BILL_PRIMARY_CERT
WITH SUBJECT = 'BILL_PRIMARY_CERT for database mirroring',
START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
GO

CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BILL_PRIMARY_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
)
GO

BACKUP CERTIFICATE BILL_PRIMARY_CERT
TO FILE = 'C:\certificates\BILL_PRIMARY_CERT.cer'
GO

-- then copy certificate to other two machines

/* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */

DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE BILL_SECONDARY_CERT
GO
DROP MASTER KEY
GO

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '**************' -- real password used
GO

CREATE CERTIFICATE BILL_SECONDARY_CERT
WITH SUBJECT = 'BILL_SECONDARY_CERT for database mirroring',
START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
GO

CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BILL_SECONDARY_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
)
GO

BACKUP CERTIFICATE BILL_SECONDARY_CERT
TO FILE = 'C:\certificates\BILL_SECONDARY_CERT.cer'
GO

-- then copy certificate to other two machines

/* -------- 3. ENABLE OUTBOUND CONNECTIONS ON THE WINTESS -------- */

DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE BILL_WITNESS_CERT
GO
DROP MASTER KEY
GO

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '**************' -- real password used
GO

CREATE CERTIFICATE BILL_WITNESS_CERT
WITH SUBJECT = 'BILL_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=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BILL_WITNESS_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
)
GO

BACKUP CERTIFICATE BILL_WITNESS_CERT
TO FILE = 'C:\certificates\BILL_WITNESS_CERT.cer'
GO

-- then copy certificate to other two machines

/* -------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY -------- */

/* enable inbound from the mirror */

DROP CERTIFICATE BILL_SECONDARY_CERT
GO
DROP USER MIRROR_SECONDARY_USER
GO
DROP LOGIN MIRROR_SECONDARY_LOGIN
GO

CREATE LOGIN MIRROR_SECONDARY_LOGIN
WITH PASSWORD = '****************' -- real password used
GO

CREATE USER MIRROR_SECONDARY_USER
FOR LOGIN MIRROR_SECONDARY_LOGIN
GO

CREATE CERTIFICATE BILL_SECONDARY_CERT
AUTHORIZATION MIRROR_SECONDARY_USER
FROM FILE = 'c:\certificates\BILL_SECONDARY_CERT.cer'
GO

GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_SECONDARY_LOGIN
GO

/* enable inbound from the witness */

DROP CERTIFICATE BILL_WITNESS_CERT
GO
DROP USER MIRROR_WITNESS_USER
GO
DROP LOGIN MIRROR_WITNESS_LOGIN
GO

CREATE LOGIN MIRROR_WITNESS_LOGIN
WITH PASSWORD = '****************' -- real password used
GO

CREATE USER MIRROR_WITNESS_USER
FOR LOGIN MIRROR_WITNESS_LOGIN
GO

CREATE CERTIFICATE BILL_WITNESS_CERT
AUTHORIZATION MIRROR_WITNESS_USER
FROM FILE = 'c:\certificates\BILL_WITNESS_CERT.cer'
GO

GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_WITNESS_LOGIN
GO

/* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */

/* enable inbound from the primary */

DROP CERTIFICATE BILL_PRIMARY_CERT
GO
DROP USER MIRROR_PRIMARY_USER
GO
DROP LOGIN MIRROR_PRIMARY_LOGIN
GO

CREATE LOGIN MIRROR_PRIMARY_LOGIN
WITH PASSWORD = '****************' -- real password used
GO

CREATE USER MIRROR_PRIMARY_USER
FOR LOGIN MIRROR_PRIMARY_LOGIN
GO

CREATE CERTIFICATE BILL_PRIMARY_CERT
AUTHORIZATION MIRROR_PRIMARY_USER
FROM FILE = 'c:\certificates\BILL_PRIMARY_CERT.cer'
GO

GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_PRIMARY_LOGIN
GO

/* enable inbound from the witness */

DROP CERTIFICATE BILL_WITNESS_CERT
GO
DROP USER MIRROR_WITNESS_USER
GO
DROP LOGIN MIRROR_WITNESS_LOGIN
GO

CREATE LOGIN MIRROR_WITNESS_LOGIN
WITH PASSWORD = '****************' -- real password used
GO

CREATE USER MIRROR_WITNESS_USER
FOR LOGIN MIRROR_WITNESS_LOGIN
GO

CREATE CERTIFICATE BILL_WITNESS_CERT
AUTHORIZATION MIRROR_WITNESS_USER
FROM FILE = 'c:\certificates\BILL_WITNESS_CERT.cer'
GO

GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_WITNESS_LOGIN
GO

/* -------- 6. ENABLE INBOUND CONNECTIONS ON THE WITNESS -------- */

/* enable inbound from the mirror */

DROP CERTIFICATE BILL_SECONDARY_CERT
GO
DROP USER MIRROR_SECONDARY_USER
GO
DROP LOGIN MIRROR_SECONDARY_LOGIN
GO

CREATE LOGIN MIRROR_SECONDARY_LOGIN
WITH PASSWORD = '****************' -- real password used
GO

CREATE USER MIRROR_SECONDARY_USER
FOR LOGIN MIRROR_SECONDARY_LOGIN
GO

CREATE CERTIFICATE BILL_SECONDARY_CERT
AUTHORIZATION MIRROR_SECONDARY_USER
FROM FILE = 'c:\certificates\BILL_SECONDARY_CERT.cer'
GO

GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_SECONDARY_LOGIN
GO

/* enable inbound from the primary */

DROP CERTIFICATE BILL_PRIMARY_CERT
GO
DROP USER MIRROR_PRIMARY_USER
GO
DROP LOGIN MIRROR_PRIMARY_LOGIN
GO

CREATE LOGIN MIRROR_PRIMARY_LOGIN
WITH PASSWORD = '****************' -- real password used
GO

CREATE USER MIRROR_PRIMARY_USER
FOR LOGIN MIRROR_PRIMARY_LOGIN
GO

CREATE CERTIFICATE BILL_PRIMARY_CERT
AUTHORIZATION MIRROR_PRIMARY_USER
FROM FILE = 'c:\certificates\BILL_PRIMARY_CERT.cer'
GO

GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_PRIMARY_LOGIN
GO

/* -------- 7. SET MIRROR'S PARTNER TO THE PRIMARY SERVER -------- */

ALTER DATABASE failover_test
SET PARTNER OFF
GO

ALTER DATABASE failover_test
SET PARTNER = 'TCP://10.152.58.242:7024';
GO

/* -------- 8. SET PRIMARY'S PARTNER TO THE MIRROR SERVER -------- */

ALTER DATABASE failover_test
SET PARTNER OFF
GO

ALTER DATABASE failover_test
SET PARTNER = 'TCP://10.152.58.243:7024';
GO

/*
Response:
Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://10.152.58.243:7024" can not be reached or does not exist. Check the network address name and reissue the command.
*/

Answer this question

Still need help with mirroring (Error 1418)

amr osama

Looks at the trouble shooting section in BOL.

Attach the errorlogs from the session to help further debugging.

Thanks,

Mark
Carlos Tortajada

If you could post the errorlog (s) from both the principal and mirror that cover the time period when you execute the commands, that would be most helpful.

Also, if you could run the sql service as a domain SA account, that would make the security setup easier.

Meanwhile, I'm going to restart my local SQL server process as Local System and try to get your scripts working.

Thanks,

Mark
Carl B.
hi there.

i'm having de same problem. but de quorum i setup is without witness and certificate.

its a straightforward database mirroring with two servers - principal and mirror.

had setup the endpoints, checked port specified listening and

from the mirror server, i can set partner to de principal server from de mirror,

but not de other way round.

i got de same error msg when i try to set partner to the mirror server, from principal.

any ideas

thanks in advance.

mchart
Richard Tkachuk

I haven't heard anything, so I am going to mark this as answered...

Hope you got it working...

Thanks,

Mark
Owen T. Soroke

OK,

Can you telnet from ALL the servers to ALL the other servers A network connection for database mirroring requires both servers to login in both directions.

We did run into a problem where windows was grabbing some ports around 5000 for its own use but was not reporting them as in use. That's why some of the examples use 7000, etc.

Now I changed my SQL processes to run as Local System, and I ran this from the principal:

(NOTE: You have to run some of the commands in order on both the Pincipal and Mirror)

CREATE DATABASE BILLSDB

GO

BACKUP DATABASE BILLSDB TO DISK = '\\MWISTROM5\SHARED\BILLSDB.BAK' WITH INIT

GO

/* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */

DROP ENDPOINT Mirroring

GO

DROP CERTIFICATE BILL_PRIMARY_CERT

GO

DROP MASTER KEY

GO

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'Password1234567890' -- real password used

GO

CREATE CERTIFICATE BILL_PRIMARY_CERT

WITH SUBJECT = 'BILL_PRIMARY_CERT for database mirroring',

START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'

GO

SELECT * FROM sys.tcp_endpoints

DROP ENDPOINT Endpoint_Mirroring

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=7024

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE BILL_PRIMARY_CERT

, ENCRYPTION = REQUIRED ALGORITHM AES

, ROLE = ALL

)

GO

BACKUP CERTIFICATE BILL_PRIMARY_CERT

TO FILE = 'C:\certificates\BILL_PRIMARY_CERT.cer'

GO

-- then copy certificate to other two machines

/* -------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY -------- */

/* enable inbound from the mirror */

DROP CERTIFICATE BILL_SECONDARY_CERT

GO

DROP USER MIRROR_SECONDARY_USER

GO

DROP LOGIN MIRROR_SECONDARY_LOGIN

GO

CREATE LOGIN MIRROR_SECONDARY_LOGIN

WITH PASSWORD = 'Password1234567890' -- real password used

GO

CREATE USER MIRROR_SECONDARY_USER

FOR LOGIN MIRROR_SECONDARY_LOGIN

GO

CREATE CERTIFICATE BILL_SECONDARY_CERT

AUTHORIZATION MIRROR_SECONDARY_USER

FROM FILE = 'c:\certificates\BILL_SECONDARY_CERT.cer'

GO

GRANT CONNECT ON ENDPOINT::Mirroring

TO MIRROR_SECONDARY_LOGIN

GO

/* enable inbound from the witness */

/* -------- 8. SET PRIMARY'S PARTNER TO THE MIRROR SERVER -------- */

ALTER DATABASE failover_test

SET PARTNER OFF

GO

ALTER DATABASE BILLSDB

SET PARTNER = 'TCP://mwistrom5:7024';

GO

And then I ran this on the mirror:

RESTORE DATABASE BILLSDB FROM DISK = 'C:\SHARED\BILLSDB.BAK' WITH NORECOVERY

GO

/* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */

DROP ENDPOINT Mirroring

GO

DROP CERTIFICATE BILL_SECONDARY_CERT

GO

DROP MASTER KEY

GO

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'Password1234567890' -- real password used

GO

CREATE CERTIFICATE BILL_SECONDARY_CERT

WITH SUBJECT = 'BILL_SECONDARY_CERT for database mirroring',

START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'

GO

SELECT * FROM sys.tcp_endpoints

DROP ENDPOINT Endpoint_Mirroring

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=7024

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE BILL_SECONDARY_CERT

, ENCRYPTION = REQUIRED ALGORITHM AES

, ROLE = ALL

)

GO

BACKUP CERTIFICATE BILL_SECONDARY_CERT

TO FILE = 'C:\certificates\BILL_SECONDARY_CERT.cer'

GO

-- then copy certificate to other two machines

/* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */

/* enable inbound from the primary */

DROP CERTIFICATE BILL_PRIMARY_CERT

GO

DROP USER MIRROR_PRIMARY_USER

GO

DROP LOGIN MIRROR_PRIMARY_LOGIN

GO

CREATE LOGIN MIRROR_PRIMARY_LOGIN

WITH PASSWORD = 'Password1234567890' -- real password used

GO

CREATE USER MIRROR_PRIMARY_USER

FOR LOGIN MIRROR_PRIMARY_LOGIN

GO

CREATE CERTIFICATE BILL_PRIMARY_CERT

AUTHORIZATION MIRROR_PRIMARY_USER

FROM FILE = 'c:\certificates\BILL_PRIMARY_CERT.cer'

GO

GRANT CONNECT ON ENDPOINT::Mirroring

TO MIRROR_PRIMARY_LOGIN

GO

/* -------- 7. SET MIRROR'S PARTNER TO THE PRIMARY SERVER -------- */

ALTER DATABASE failover_test

SET PARTNER OFF

GO

ALTER DATABASE BILLSDB

SET PARTNER = 'TCP://mwistrom1:7024';

GO

and it worked fine.

OK, the best thing that we can do now is send me the errorlogs so that I can look into them.

Thanks,

Mark
Still need help with mirroring (Error 1418)

Answer this question