none
Error Configuring Database Mirroring from PC to PC with Certificates RRS feed

  • Question

  • I am experiencing an issue when setting up database mirroring from (2) SQL Server Instances which are hosted on their own PC's. The PCs are NOT in a domain, they are in a work group

    Each PC has a SQL 2014 license and the server instances are upgraded to Standard Edition.

    The PC's can ping each other.


    Ive tried configuring the mirroring with the graphic user interface to no prevail.  I received the error,

    "The server network address TCP:\\PC1.XXXXX.com:7022 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." 

    After trying the GUI for a while, I then switched to t-SQL commands using certificates.  The following configurations were established, and detail of exact commands will be provided later in this post.

    Here is the configuration structure:
    PC1 - Outbound configuration
    PC2 - Outbound configuration
    PC1 - Inbound configuration
    PC2 - Inbound configuration

    After both outbound and inbound configurations were established on both PCs, I then imported a database into PC1 instance, and the same database into PC2 instance (Note, the same database was used for both imports).  A full backup was taken of the DB on PC1, then the transaction log was backed up from the DB on PC1 as well.

    I restored the database backups from PC1 on PC2 with NO RECOVERY, putting PC2 DB into "recovering..." mode using the Full backup and Trans Backup from PC1.  Now my DB on PC2 was configured for mirroring.
    After this step, T-Sql commands were issued from PC2 to set the partner as PC1, making PC1 the Principal Instance (as the DB on PC2 was in "recovering..." mode)

    This executed successfully!

    The same command was used on PC1 to set the partner as PC2, as I understand, this would establish the mirroring connection once successful, however, this is the point where the address error was generated.

    After this was not successful, I reversed the roles of the PC's making PC1 the Mirrored instance and PC2 the Principal, restoring PC1 database with a backup and trans log from PC2 with no recovery, putting it into "recovering..." state.  

    On PC1, i then issued the command ALTER DATBASE xxxxx SET PARTNER = 'TCP:\\PC2.XXXXX.com:7022' making PC2 the Principal database.

    This was then successful. 

    I then tried to point PC1 as PC2's partner issuing the command on PC2:
    ALTER DATBASE xxxxx SET PARTNER = 'TCP:\\PC1.XXXXX.com:7022' 

    Upon reversing the roles of the Principal and Mirrored Instances, I ran into the same issue. The Principal instance cannot connect to the Mirrored Instance using SET PARTNER command after the Mirrored Instance (pc1) was able to Set the Principal instance (pc2) as its partner. 

    Here are the t-SQL commands that were used/issued/executed...


    ----------CONFIGURING PC1 OUTBOUND CONNECTIONS----------



    Created new server:
    xxxxxx_Host_A
    Full name = PC1\xxxxxx_Host_A

    Using the command prompt, changed the Collation on Server xxxxxx_Host_A to;
    Latin1_General_CS_AS

    Opened Configuration Manager
    Expanded Protocols for xxxxxx_Host_A
    Enabled TCP/IP
    Set the TCP Port for IP4 to 1433, set dynamic to ""
    Set the TCP port for IPALL to 1433, set dynamic to ""


    Setting Up Outbound Connections PC1:

    Note: The following commands were executed on PC1 which hosts the instance xxxxxx_Host_A, each t-SQL command should be executed seperately.

    --CREATE THE MASTER KEY
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxx';
    GO  

    --CREATE THE CERTIFICATE
    USE master;  
    CREATE CERTIFICATE xxxxxx_Host_A_Certificate  
    WITH SUBJECT = 'xxxxxx_Host_A_Certificate';  
    GO

    --CREATE THE MIRRORING ENDPOINT
    CREATE ENDPOINT Host_A_Mirroring
    STATE = STARTED  
    AS TCP (  
    LISTENER_PORT=7022,
    LISTENER_IP = ALL
    )   
    FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE xxxxxx_Host_A_Certificate,
    ENCRYPTION = REQUIRED ALGORITHM AES,
    ROLE = PARTNER
    );  
    GO  

    --BACKUP THE CERTIFICATE AND PRIVATE KEY, ENSURE THERE ARE NO FILES IN THE FOLDER OR YOU WILL GET AN ERROR
    BACKUP CERTIFICATE xxxxxx_Host_A_Certificate TO FILE = 'C:\xxxxxx_Host_A_Certificate_Backups\xxxxxx_Host_A_Certificate_Backup.cer' 
    WITH PRIVATE KEY (File = 'C:\xxxxxx_Host_A_Certificate_Backups\xxxxxx_Host_A_Certificate_Private_Key_Backup.pvk',
    ENCRYPTION BY PASSWORD = 'xxxxxx')
    GO  




    ----------CONFIGURING PC2 OUTBOUND CONNECTIONS----------



    Created new server:
    ASRS_SSB_Host_B
    Full name = PC2\xxxxxx_Host_B

    Using the command prompt, changed the Collation on Server xxxxxx_Host_B to;
    Latin1_General_CS_AS

    Opened Configuration Manager
    Expanded Protocols for xxxxxx_Host_B
    Enabled TCP/IP
    Set the TCP Port for IP4 to 1433, set dynamic to ""
    Set the TCP port for IPALL to 1433, set dynamic to ""



    Setting Up Outbound Connections:

    Note: The following commands were executed on PC1 which hosts the instance xxxxxx_Host_A, each t-SQL command should be executed separately.

    --CREATE THE MASTER KEY
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxx';
    GO  

    --CREATE THE CERTIFICATE
    USE master;  
    CREATE CERTIFICATE xxxxxx_Host_B_Certificate  
    WITH SUBJECT = 'xxxxxx_Host_B_Certificate';  
    GO

    --CREATE THE MIRRORING ENDPOINT
    CREATE ENDPOINT Host_B_Mirroring
    STATE = STARTED  
    AS TCP (  
    LISTENER_PORT=7022,
    LISTENER_IP = ALL
    )   
    FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE xxxxxx_Host_B_Certificate,
    ENCRYPTION = REQUIRED ALGORITHM AES,
    ROLE = PARTNER
    );  
    GO  

    --BACKUP THE CERTIFICATE AND PRIVATE KEY, ENSURE THERE ARE NO FILES IN THE FOLDER OR YOU WILL GET AN ERROR
    BACKUP CERTIFICATE xxxxxx_Host_B_Certificate TO FILE = 'C:\xxxxxx_Host_B_Certificate_Backups\xxxxxx_Host_B_Certificate_Backup.Cer' 
    WITH PRIVATE KEY (File = 'C:\xxxxxx_Host_B_Certificate_Backups\xxxxxx_Host_B_Certificate_Private_Key_Backup.Pvk',
    ENCRYPTION BY PASSWORD = 'xxxxxx')
    GO  

    ----------CONFIGURING PC1 INBOUND CONNECTIONS----------

    The follow t-SQL commands were executed on PC1, they should be executed separately

    --Create a login on HOST_A for HOST_B.
    USE master;  
    CREATE LOGIN HOST_B_login WITH PASSWORD = 'xxxxxx';  
    GO  

    --Create a user for that login.
    CREATE USER HOST_B_user FOR LOGIN HOST_B_login;  
    GO  

    --Associate the certificate with the user.
    CREATE CERTIFICATE xxxxxx_Host_B_Certificate  
    AUTHORIZATION HOST_B_user  
    FROM FILE = 'C:\xxxxxx_Host_B_Certificate_Backups\xxxxxx_Host_B_Certificate_Backup.cer'
    With Private Key (File = 'C:\xxxxxx_Host_B_Certificate_Backups\xxxxxx_Host_B_Certificate_Private_Key_Backup.pvk',
    DECRYPTION BY PASSWORD = 'xxxxxx');
    GO  

    --Grant CONNECT permission on the login for the remote mirroring endpoint.
    GRANT CONNECT ON ENDPOINT::Host_A_Mirroring TO [HOST_B_login];  
    GO 

    ----------CONFIGURING PC2 INBOUND CONNECTIONS----------

    The follow t-SQL commands were executed on PC2, they should be executed separately

    --Create a login on HOST_B for HOST_A.
    USE master;  
    CREATE LOGIN HOST_A_login WITH PASSWORD = 'xxxxxx';  
    GO  

    --Create a user for that login.
    CREATE USER HOST_A_user FOR LOGIN HOST_A_login;  
    GO  

    --Associate the certificate with the user.
    CREATE CERTIFICATE xxxxxx_Host_A_Certificate  
    AUTHORIZATION HOST_A_user  
    FROM FILE = 'C:\xxxxxx_Host_A_Certificate_Backups\xxxxxx_Host_A_Certificate_Backup.cer'
    With Private Key (File = 'C:\xxxxxx_Host_A_Certificate_Backups\xxxxxx_Host_A_Certificate_Private_Key_Backup.pvk',
    DECRYPTION BY PASSWORD = 'xxxxxx');
    GO  

    --Grant CONNECT permission on the login for the remote mirroring endpoint.
    GRANT CONNECT ON ENDPOINT::Host_B_Mirroring TO [HOST_A_login];  
    GO 

    CONFIGURING THE MIRROING ENDPOINTS
    The following t-sql command was issued on PC2, this command would set PC1 instance DB as principal, as PC2 instance DB was in the state "recovering..." (set up for mirroring)

    ALTER DATABASE xxxxxx
    SET PARTNER = 'TCP://PC1.xxxxxx.com:7022'
    GO
    Note: This was the command that was issued successfully.

    CONFIGURING THE MIRROING ENDPOINTS
    The following t-sql command was issued on SSBPC1, as previously stated, as i understand, this would establish the mirroring connection one successful, however, this is the point where i received my Address error.

    ALTER DATABASE dbSSB_RM
    SET PARTNER = 'TCP://PC2.xxxxxx.com:7022'
    GO



    Im hoping someone can shoot me in the right direction regarding this issue.  Below are common suggestions to resolve the issue:
    "The server network address TCP:\\PC1.XXXXX.com:7022 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." 

    However, I have tried all of these suggestions to no prevail. Any help is appreciated.

    Regards,
    Jrdn Shlsn


    Suggestion 1: Make sure that on Mirror Server the database is restored with NO RECOVERY option (This is the most common problem).

    Suggestion 2: Make sure that from Principal the latest LOG backup is restored to mirror server. (Attempt this one more time even though the full backup has been restored recently).

    Suggestion 3: Check if you can telnet to your ports using command TELNET ServerName Ports like “telnet SQLServerName 5023”.

    Suggestion 4: Make sure your firewall is turned off.

    Suggestion 5: Verify that the endpoints are started on the partners by using the state or state_desc column the of the

    sys.database_mirroring_endpoints catalog view. You can start end point by executing an ALTER ENDPOINT statement.

    Suggestion 6: Try the following command as one of the last options.
    GRANT CONNECT ON ENDPOINT::Mirroring TO ALL

    Suggestion 7: Delete the end points and recreate them.


    Thursday, August 8, 2019 6:35 PM

Answers

  • Hi, this problem was solved for me. When backing up the certificate, I was backing up the private key as well. Apparently this is not needed. The PCs host file was also resolved to point to the other computers IP and PC name. As well as it’s full FQDN.
    Monday, August 12, 2019 1:49 AM

All replies

  • anyone help?
    Monday, August 12, 2019 1:42 AM
  • Hi, this problem was solved for me. When backing up the certificate, I was backing up the private key as well. Apparently this is not needed. The PCs host file was also resolved to point to the other computers IP and PC name. As well as it’s full FQDN.
    Monday, August 12, 2019 1:49 AM
  • Hi Jrdn shlsn,

     

    I am glad to know that your problem has been solved In order to close this thread, please  mark useful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 12, 2019 7:19 AM