locked
Mirroring problem between pcs in different location RRS feed

  • Question

  • I am facing this problem here. Well , I did setup mirroring in LAN successfully (using certificates). Then I try to implement the same method for WAN. Here's the info :

    Principal : location A (111.222.333.444,1234:5022)

    Mirror : location B (444.555.666.777:5022)

    Witness : location A (111.222.333.444,5678:5022)

    For the mirror side, I didn't put any firewall settings at all; and both principal n witness , i did open the specific port for mirroring endpoint n sql. However, when I start mirroring , it shows


    'The server network address "TCP://444.555.666: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. (Microsoft SQL Server, Error: 1418)'

    When I open the Error log file on principal side, it shows :

    2006-12-03 15:10:56.04 spid26s     Error: 1443, Severity: 16, State: 2.
    2006-12-03 15:10:56.04 spid26s     Database mirroring has been terminated for database 'testing'. This is an informational message only. No user action is required.
    2006-12-03 15:12:50.03 spid22s     Error: 1474, Severity: 16, State: 1.
    2006-12-03 15:12:50.03 spid22s     Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://444.555.666.777:5022.

    When I open the Error log file on mirror side, it shows :

    2006-12-03 15:10:09.78 spid25s     Error: 1443, Severity: 16, State: 2.
    2006-12-03 15:10:09.78 spid25s     Database mirroring has been terminated for database 'testing'. This is an informational message only. No user action is required.
    2006-12-03 15:10:09.79 spid24s     Starting up database 'testing'.
    2006-12-03 15:10:09.81 spid24s     The database 'testing' is marked RESTORING and is in a state that does not allow recovery to be run.

    Why I am still facing Error 1418 again? I used the same method like what I did in LAN and able to setup successfully. I need an urgent assistance. Thanx alot and hope can get any help from here.

    Best Regards,

                   Hans

    Sunday, December 3, 2006 7:26 AM

All replies

  • You mention the mirror address as being 444.555.666.777:5022, but the error message is for address TCP://444.555.666:5022. Is this a typo in your message or a real problem? I assume is a typo. I would trust the error message in that the port 5022 on the witness host cannot be reached, because of various resons: firewall, IPSEC, some routing configuration, server not listenning (check errorlog). Using telnet, ensure that you can open a TCP connection to the witness port.

    HTH,
    ~ Remus

    Monday, December 4, 2006 10:13 PM
  • I guess probably is my firewall setting. Sorry about that and thx for the guide anyway. However, I am facing another problem. As I said, I am able to setup mirroring with witness in LAN but there's an error when I set up with witness in WAN.  I am using the same method (certificates) to implement mirroring anyway. There's several scenarios that I have tried on it.

    Server A (Principal) : 111.222.333.444:5022

    Server B (Mirror) : 444.555.666.777:5022

    Server C (Witness) : 777.888.999.000:5022 

    ---------------------------------------------------------------------------------------------------

    Scenario 1 (Mirroring without Witness)

    - It works fine to me and mirroring is working.

    Scenario 2(Mirroring with Witness)

    - It shows that I was unable to connect to the C for witness. Here's the following error message :

    Msg 1456, Level 16, State 3, Line 1
    The ALTER DATABASE command could not be sent to the remote server instance 'TCP://777.888.999.000:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

    The error log shows in principal server are :

    2006-12-08 10:10:12.06 spid24s     Database mirroring is active with database 'abc' as the principal copy. This is an informational message only. No user action is required.
    2006-12-08 10:10:12.20 spid52      Error: 1456, Severity: 16, State: 3.
    2006-12-08 10:10:12.20 spid52      The ALTER DATABASE command could not be sent to the remote server instance 'TCP://777.888.999.000:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
    2006-12-08 10:12:00.00 spid27s     Error: 1474, Severity: 16, State: 1.
    2006-12-08 10:12:00.00 spid27s     Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://777.888.999.000:5022'.

    Scenario 3 (Mirroring without Witness)

    - I used server C to act as Mirror right now and mirroring with A. As a result It works fine to me.

    --------------------------------------------------------------------------------------

    There's no firewall settings for all the location, ports are ok (tested using telnet), already enabled local and remote connections for all the servers, all servers log on as 'Local System'. I was wondering why I'm able to set it up in LAN but can't in WAN. Is it still need some settings for the witness? Besides that, I did try set host file and set 'Full Control' on the machine key but getting the same result as well. Hope can get some assistance and I do really need help on it urgently. Thx in advance.

    PS: If possible, I prefer a discussion through msn messenger and I online always. Here's my email : tsuyoshi_lim82@hotmail.com

    Friday, December 8, 2006 3:05 AM
  • Error 64 typically happens when you connect to the other party (the witness) and during handshake the other party cannot validate the access (CONNECT permision ont he endpoint). In this case the witness abruptly closes the connection because it cannot trust the conectee. To investigate the issue, you must look into the witness host (C), it will generate Profiler events when this happens. Monitor the 'Security Audit/Audit Broker Login' and 'Broker/Broker:Connection' events. Most likely either the certificate used by the A is not installed on C's master [database] or because the login that owns A's certificate on C does not have CONNECT permission on the mirroring endpoint. Same for B, he has to be trusted by the witness as well.

    HTH,
    ~ Remus

    Saturday, December 9, 2006 1:31 AM
  • Thx for the reply but how come I am able to use the exact method in LAN (works with witness server). I already checked that the certificate used by the A is installed on C's master [database] and all the certificates' thumbprint version are match.  Here's my sqls for creating certificates as trusted connection :

    -----------------------------------------------------------------------------------------------------------------------------------

    1) Server A (Principal)

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

    BACKUP DATABASE abc
     To DISK = 'C:\abc.bak'
     WITH FORMAT
    GO

    BACKUP LOG abc
     TO DISK = 'C:\abc_Log.bak'
     WITH FORMAT

    DROP ENDPOINT Mirroring
    GO

    DROP CERTIFICATE A_PRIMARY_CERT
    GO

    DROP MASTER KEY
    GO

    CREATE MASTER KEY
    ENCRYPTION BY PASSWORD = '123456'
    GO

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

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

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

    -- Copy A_PRIMARY_CERT to server B & C (Location path: 'C:\certificates)

    2. Server B (Mirror)

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

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

    RESTORE LOG abc
     FROM DISK = 'C:\abc_Log.bak'
     WITH NORECOVERY

    DROP ENDPOINT Mirroring
    GO

    DROP CERTIFICATE B_SECONDARY_CERT
    GO

    DROP MASTER KEY
    GO

    CREATE MASTER KEY
    ENCRYPTION BY PASSWORD = '123456'
    GO


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

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

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

    -- Copy B_SECONDARY_CERT to server A & C (Location path: 'C:\certificates)

    3. Server C (Witness)

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

    DROP ENDPOINT Mirroring
    GO

    DROP CERTIFICATE C_WITNESS_CERT
    GO

    DROP MASTER KEY
    GO

    CREATE MASTER KEY
      ENCRYPTION BY PASSWORD = '123456'
    GO

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

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

    -- Copy C_WITNESS_CERT to server A & B (Location path: 'C:\certificates)

    4) Server A (Principal)

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

    /* enable inbound from the mirror */
    DROP CERTIFICATE B_SECONDARY_CERT
    GO

    DROP USER MIRROR_SECONDARY_USER
    GO

    DROP LOGIN MIRROR_SECONDARY_LOGIN
    GO

    CREATE LOGIN MIRROR_SECONDARY_LOGIN
    WITH PASSWORD = '123456'
    GO

    CREATE USER MIRROR_SECONDARY_USER
    FOR LOGIN MIRROR_SECONDARY_LOGIN
    GO

    CREATE CERTIFICATE B_SECONDARY_CERT
    AUTHORIZATION MIRROR_SECONDARY_USER
    FROM FILE = 'C:\certificates\B_SECONDARY_CERT.cer'

    GO

    GRANT CONNECT ON ENDPOINT::Mirroring
    TO MIRROR_SECONDARY_LOGIN
    GO

    /* enable inbound from the witness */

    DROP CERTIFICATE C_WITNESS_CERT
    GO

    DROP USER MIRROR_WITNESS_USER
    GO

    DROP LOGIN MIRROR_WITNESS_LOGIN
    GO

    CREATE LOGIN MIRROR_WITNESS_LOGIN
      WITH PASSWORD = '123456'

    CREATE USER MIRROR_WITNESS_USER
      FOR LOGIN MIRROR_WITNESS_LOGIN
    GO

    CREATE CERTIFICATE C_WITNESS_CERT
      AUTHORIZATION MIRROR_WITNESS_USER
      FROM FILE = 'C:\certificates\C_WITNESS_CERT.cer'
    GO

    GRANT CONNECT ON ENDPOINT::Mirroring
      TO MIRROR_WITNESS_LOGIN
    GO


    5) Server B (Mirror)

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

    /* enable inbound from the primary */
    DROP CERTIFICATE A_PRIMARY_CERT
    GO

    DROP USER MIRROR_PRIMARY_USER
    GO

    DROP LOGIN MIRROR_PRIMARY_LOGIN
    GO

    CREATE LOGIN MIRROR_PRIMARY_LOGIN
    WITH PASSWORD = '123456'
    GO

    CREATE USER MIRROR_PRIMARY_USER
    FOR LOGIN MIRROR_PRIMARY_LOGIN
    GO

    CREATE CERTIFICATE A_PRIMARY_CERT
    AUTHORIZATION MIRROR_PRIMARY_USER
    FROM FILE = 'C:\certificates\A_PRIMARY_CERT.cer'
    GO

    GRANT CONNECT ON ENDPOINT::Mirroring
    TO MIRROR_PRIMARY_LOGIN
    GO

    /* enable inbound from the witness */

    DROP CERTIFICATE C_WITNESS_CERT
    GO

    DROP USER MIRROR_WITNESS_USER
    GO

    DROP LOGIN MIRROR_WITNESS_LOGIN
    GO

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

    CREATE USER MIRROR_WITNESS_USER
      FOR LOGIN MIRROR_WITNESS_LOGIN
    GO

    CREATE CERTIFICATE C_WITNESS_CERT
      AUTHORIZATION MIRROR_WITNESS_USER
      FROM FILE = 'C:\certificates\C_WITNESS_CERT.cer'
    GO

    GRANT CONNECT ON ENDPOINT::Mirroring
      TO MIRROR_WITNESS_LOGIN
    GO

    6) Mirror (Server C)

    /* -------- 6. ENABLE INBOUND CONNECTIONS ON THE WITNESS -------- */
    /* enable inbound from the mirror */

    DROP CERTIFICATE B_SECONDARY_CERT
    GO

    DROP USER MIRROR_SECONDARY_USER
    GO

    DROP LOGIN MIRROR_SECONDARY_LOGIN
    GO

    CREATE LOGIN MIRROR_SECONDARY_LOGIN
     WITH PASSWORD = '123456' 
    GO

    CREATE USER MIRROR_SECONDARY_USER
      FOR LOGIN MIRROR_SECONDARY_LOGIN
    GO

    CREATE CERTIFICATE B_SECONDARY_CERT
      AUTHORIZATION MIRROR_SECONDARY_USER
      FROM FILE = 'C:\certificates\B_SECONDARY_CERT.cer'
    GO

    GRANT CONNECT ON ENDPOINT::Mirroring
      TO MIRROR_SECONDARY_LOGIN
    GO

    /* enable inbound from the primary */

    DROP CERTIFICATE A_PRIMARY_CERT
    GO

    DROP USER MIRROR_PRIMARY_USER
    GO

    DROP LOGIN MIRROR_PRIMARY_LOGIN
    GO

    CREATE LOGIN MIRROR_PRIMARY_LOGIN
     WITH PASSWORD = '123456'
    GO

    CREATE USER MIRROR_PRIMARY_USER
     FOR LOGIN MIRROR_PRIMARY_LOGIN
    GO

    CREATE CERTIFICATE A_PRIMARY_CERT
      AUTHORIZATION MIRROR_PRIMARY_USER
      FROM FILE = 'C:\certificates\A_PRIMARY_CERT.cer'
    GO

    GRANT CONNECT ON ENDPOINT::Mirroring 
    TO MIRROR_PRIMARY_LOGIN
    GO

    -----------------------------------------------------------------------------------------------------------

    Is there any wrong for the t-sql scripts ? Hope can get further assistance here. Thx.

    Best Regards,

                Hans

     

    Sunday, December 10, 2006 9:47 AM
  • Please attach Profiler to all three instances (principal, mirror, witness), enable the 'Security Audit/Audit Broker Login' and 'Broker/Broker:Connection' events (make sure you enable all columns) then try to establish a mirroring session. If you don't see anything obvious int he traces, post here the resulted traces or send me the saved trc files to remus.rusanu at microsoft com (archive with zip if necessary). 

    HTH,
    ~ Remus

    Sunday, December 10, 2006 8:19 PM
  • Hi Remus, I already sent the trace files to you by email. Thx for the help.

    Best Regards,

                      Hans

    Monday, December 11, 2006 4:12 AM
  • Hello,

    I'm having the same issue. Were you able to find a solution for this problem?

    Thanks,

    baldo2

    Monday, December 11, 2006 5:46 PM
  • The problem is most likely from the names used as partner and witness. When the principal is connecting to the witness, it advertises its name as the FQDN name of the machine. Similarly, the mirror would advertise its name as its FQDN, and this name will be compared with the mirror name as reported by the principal (that is the name in ALTEr DATABSE ... SET PARTNER ...). So what that implies for your setup is:

    - the principal FQDN (as returned by GetComputerNameEx (..., ComputerNameDnsFullyQualified) http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sysinfo/base/getcomputernameex.asp) must be resolvable and reachable by the witness (the witness must be able to open a TCP connection with this DNS name) in order for the witness to be able to respond to messages sent by the principal

    - the mirror FQDN name and the ALTER DATABASE ... SET PARTNER NAME must mach.

    - since the principal and mirror role can reverse at any moment, the above rules should continue to apply after roles have reversed

    In practice what that mean is the you must set the PARTNER/WITNESS name to the principal/mirror/witness FQDN names, eg. "tcp://REMUSR10.redmond.corp.microsoft.com:5022" and these names should be 'visible' to each other (DNS resolvable, IP reachable, port allowed in firewall etc)

    HTH,
    ~ Remus

    Tuesday, December 12, 2006 7:08 PM
  • Hi Remus, I did try on the FQDN, but it seems still the same. Is that any additional settings for the database mirroring? I will appreciate if there's a setup guide like print-screeen for setting up the database mirroring because I really need it. Thx.

    Best Regards,

                        Hans 

    Friday, December 15, 2006 9:24 AM
  • Hi,

    Sorry for dropping in.  Not sure if issue is still persistant.

    I ran into similar issue few days back.

    Might be a basic step, but I would suggest you to check the account under which all the 3 sql servers are running.  If you are using certificates, they should be running under local system account, and if you are using machine accounts, ensure that they are running under network service account.

    Regards,

    Shiva

    Tuesday, February 6, 2007 10:11 PM
  • I ran into the same problem with the network below and finally got it to work. i just wanted to share the solution for anyone else who needs it

     

    192.168.4.1    Principal DB

    192.168.4.2    Mirror DB

    192.168.3.2    Witness

     

    192.168.4.x and 192.168.3.x each is on a diffrent vLan

    and both VLans are on the same windows (2003) Workgroup

     

    the mirroring wizard did not work at all and kept giving me errors about the FQDN of the servers

     

    the solution was to do it manually following the certificate  authentication samples in the BOL. one thing the BOL missed is that i needed to edit the hosts file on 192.168.4.x and add an entry for the witness and on the widness add entries for the DB's

     

    besides that and after two days of trying it finally appears to be working.

     

    Tuesday, March 27, 2007 8:21 AM
  • Hi,

    Not sure if issue is still persistant.

    I ran into similar issue yesterday.

    We just added the host entries on all the 3 servers. Also ensured that we are using fully qualified server names rather  than IP.

    This worked for us.

    Thanks,

    Tushar Chheda

    Monday, November 15, 2010 5:20 AM
  • @ Remus is aboslutely correct. We were experiencing the same problem in that the witness would not authnticate the principal server because there where on different algorithm schemes, the witness on our end was on another algorithm and the principal was on AES 256. The fix to this problem for me was to drop the end point that was created at the witness and recreated by the credentials that where propulagated on the principal server. On the principal>Go to Server Properties>Endpoints>Database Mirroring>Right Click>Script End Point to New Query Window...copy and open new query Window on the WITNESS Server and paste it...RUN the query...VOILA!!! FIXED!

    Try to recreate your security settings now on Database mirroring...you should be good to go now...

    Here is the script that I ran on the WITNESS SERVER:

    CREATE ENDPOINT [Mirroring]
     AUTHORIZATION [Domain\Account]
     STATE=STARTED
     AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
     FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
    , ENCRYPTION = REQUIRED ALGORITHM AES)
    GO

     

     

     

    • Proposed as answer by rtinoco Friday, April 1, 2011 11:45 AM
    Friday, April 1, 2011 11:45 AM