2

I am at my wits end with this. I am trying to set up mirroring between a development server and my local machine.

The error that keeps coming up is:

Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://machineabc:7023" 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.

Which occurs when I go through the GUI or run

ALTER DATABASE TESTING SET PARTNER = 'TCP://machineabc:7023'

I restored the database first with the .bak and with no recovery then the .trn with no recovery. The local database has (Restoring...) next to it...

I have run select * from sys.database_mirroring_endpoints and it all looks good (Partner, started, both have rc4 encryption).

Any suggestions? Edit: So I tried again and I did GRANT CONNECT on the access point then when I tried setting the partner I did a net stat and this was the result: nettstat

Here are the results of the connectivity tests in Craig Efrein's post:

tests2

  • Does the service account have rights to connect to the endpoint ? – Stijn Wynants Apr 16 '15 at 7:30
  • How would I test that? – Adrian Apr 16 '15 at 7:31
  • Did you include after the creation of your endpoint ? GRANT CONNECT ON ENDPOINT::AlwaysOn_Endpoint TO [yourserviceAccount]; – Stijn Wynants Apr 16 '15 at 7:35
  • There can be many reasons for 1418 error message. Did you went through this article msdn.microsoft.com/en-us/library/aa337361.aspx – Shanky Apr 16 '15 at 7:35
  • Are the Dev server and your workstation on the same domain? Have you tried to drop the encryption ? – Spörri Apr 16 '15 at 8:53
3

I got this same error a few weeks ago, despite the fact the connection was fine; it was actually due to an authentication issue.

If you don't have domain connectivity between the two machines, you can use certificates to create credentials that can be used for mirroring endpoints. To do this, you create a certificate on the database on each end, create an endpoint based on the certificate, then export each certificate to the opposite end. The public key in the certificate is thus used to authenticate with the private master key on the remote database.

  1. Create a master encryption key on each server. This is only done once per server instance.

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
    
  2. Create a certificate for each instance with an arbitrary subject. I will provide T-SQL for endpoint 1, but you will need to do this on the other endpoint as well.

    CREATE CERTIFICATE Endpoint1_Cert WITH SUBJECT = 'CERT for endpoint 1'
    
  3. Create the endpoint using the certificate:

    CREATE ENDPOINT Endpoint_Mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT=7023, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE Endpoint1_cert,
    ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
    
  4. Back up the certificate from each end for export:

    BACKUP CERTIFICATE Endpoint1_cert TO FILE = 'C:\BACKUP\Endpoint1.cer';
    
  5. Now move each file to the opposite server using your favored method. Here I assume you use the C:\backup directory as both source and target on each server.

  6. Create server logins, based on the moved files. Note the full credential consists of a server level login and a user credential on the master database. (Kind of strange but that's the way it works...). Here's the code you'd run on endpoint 2 to import the certificate from endpoint 1. The password is totally arbitrary and is not used later. The logins must be granted mirroring rights.

     --note, run on endpoint 2
     USE master;
    
     CREATE LOGIN Endpoint1_login WITH PASSWORD = 'userpassword';
    
     CREATE USER Endpoint1_user FOR LOGIN Endpoint1_login;
    
     CREATE CERTIFICATE Endpoint1_cert AUTHORIZATION Endpoint1_user
     FROM FILE = 'C:\backup\Endpoint1.cer'
    
     GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Endpoint1_login];
    
  7. Run a converse process for creating a certificate on endpoint 2 and using it to create a credential on endpoint 1.

  8. Now, it's time to fire up the mirroring. You start with the server whose database is not in recovery mode.

    ALTER DATABASE TESTING
    SET PARTNER = 'TCP://endpoint1host:7023';
    
  9. Next run the converse command on the alternate server. (If you get a database is not configured for mirroring at this point, there may be an environment-specific security problem that can be worked around with a sysadmin rights grant to the endpoint -- see comment by AllenKing below. This did not occur in our lab.)

I ran this process successfully on 2012 SP1 on 21 March. After I corrected an error I made on the account creation -- I did not use master but instead used the mirror database -- everything worked fine.

In troubleshooting configuration, I ended up using the following dynamic views:

-- To check on endpoints status -- is the port right, etc?
SELECT * FROM SyS.endpoints 
SELECT * FROM SYS.tcp_endpoints
SELECT * FROM sys.database_mirroring_endpoints

-- This shows the endpoint rights
SELECT EP.name, SP.STATE, 
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) 
AS GRANTOR, 
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) 
AS GRANTEE 
FROM sys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDER BY Permission,grantor, grantee; 
  • help.. I indented 4 spaces but I only got code blocks on the last section! – John Meyer Apr 16 '15 at 16:10
  • When you use bullets / numbers then you have to put 8 spaces. Check this help article for more reference. – Kin Shah Apr 16 '15 at 16:15
  • Omg that looks so much better.. thank you! – John Meyer Apr 16 '15 at 16:24
  • After following these steps when I execute ALTER DATABASE TESTING SET PARTNER = 'TCP://endpoint1host:7023'; I get "Database Testing is not configured for mirroring". – Allen King May 7 '16 at 6:20
  • 1
    @JohnMeyer It works now. You may want to add to the instruction that Endpoint1_login that is created in Endpoint 2, it must be granted sysadmin type permissions under SQL Server Security (in the slave SQL Server i.e. in Endpoint2). I found this out after I tried to setup mirroring using SQL Server Management Studio after performing the steps in your message. – Allen King May 9 '16 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.