January 21, 2010 at 5:59 pm
I am configuring database mirroring over a WAN link between two SQL 2008 named instances who's host servers are not domain members, using certificates for authentication as laid out in BOL (http://technet.microsoft.com/en-us/library/ms191140.aspx). After many attempts to get this working by myself I have started from scratch and went step-by-step according to the link above, however the issue I was trying to resolve is still present.
The issue is on the set of final steps which sets the partner status on each server, when I perform step #2 to set the partner status on "HOST_A", I get the following error:
Msg 1418, Level 16, State 1, Line 2
The server network address "TCP://server-b.our-domain.com: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.
The interesting thing however is that I can see traffic on the firewall (TCPDUMP) going back and forth between the two servers for about 15 seconds before that error gets spit back at me.
At this point I am not sure how to proceed because I can connect to the SERVER-A\BLUE instance from SSMS on SERVER-B and I can connect to the SERVER-B\RED instance from SSMS on SERVER-A without a problem. I am very confused why I am getting the error at this point in time. The endpoints on both sides are listed as started in sys.tcp_endpoints and sys.endpoints.
Another interesting note is that _before_ attempting step 2, I can telnet from SERVER-A to SERVER-B over 5022 and from SERVER-B to SERVER-A over 5022, however after step 2 fails, I can no longer telnet either direction. TCPDUMP will show traffic going from either to the other, but there is no return traffic after step 2 fails.
The main issue for me is that this error seems to have the wrong description for whatever is actually happening since clearly the network address exists and can be reached and the endpoints are operational as well (at least until the operation fails :rolleyes:) I have also tried doing the config in the opposite direction (doing a full backup/restore with no recovery etc. going the other direction) and it fails the exact same way providing the same errors, but again with all of the traffic showing on the firewall.
Lastly, in SQL logs I also get the error "Error: 1443, Severity: 16, State: 2." Which seems to be directly related, and some of what I have found online suggests an issue with windows authentication, however that should not be the case since my endpoints are configured with certificates.
Any help would be greatly appreciated.
January 21, 2010 at 6:20 pm
Also, here is the complete list of TSQL executed... the testdb on SERVER-A\BLUE is online and operational, testdb on SERVER-B\RED is a restored copy of BLUE's database with no recovery.
--ON SERVER-A\BLUE
use master
go
create master key encryption by password = 'password123!'
go
create certificate CA_cert
With subject = 'CA_cert Certificate'
go
create endpoint Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE CA_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
)
go
BACKUP CERTIFICATE CA_cert TO FILE = 'c:\sql\CA_cert.cer'
go
--ON SERVER-B\RED
use master
go
create master key encryption by password = 'password123!'
go
create certificate NJ_cert
With subject = 'NJ_cert Certificate'
go
create endpoint Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE NJ_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
)
go
BACKUP CERTIFICATE NJ_cert TO FILE = 'c:\sql\NJ_cert.cer'
go
--ON SERVER-A\BLUE
create login NJ_login WITH PASSWORD = 'password123!'
go
CREATE USER NJ_user FOR LOGIN NJ_login
go
CREATE CERTIFICATE NJ_cert
AUTHORIZATION NJ_user
FROM FILE = 'C:\sql\NJ_cert.cer'
go
GRANT CONNECT ON ENDPOINT::Mirroring TO NJ_login
go
--ON SERVER-B\RED
create login CA_login WITH PASSWORD = 'password123!'
go
CREATE USER CA_user FOR LOGIN CA_login
go
CREATE CERTIFICATE CA_cert
AUTHORIZATION CA_user
FROM FILE = 'C:\sql\CA_cert.cer'
go
GRANT CONNECT ON ENDPOINT::Mirroring TO CA_login
go
--ON SERVER-B\RED
alter database testdb
set partner = 'TCP://server-a.our-domain.com:5022'
go
--ON SERVER-A\BLUE
alter database testdb
set partner = 'TCP://server-b.our-domain.com:5022'
go
-- Everything works fine up until this point at which time I get the previously mentioned errors
January 22, 2010 at 4:53 pm
With some guidance from a post on another site I was able to figure out the problem. I was advised to trace the database mirroring login event and the broker:connection event. This lead me to a negotiate failure which prompted me to double check all of my settings. Turns out I was missing one of the users on the SERVER-A side. PEBKAC fail lol. :w00t:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply