4

I have this very frustrating problem that I've been trying to figure out for almost a week now. I have two SQL Server 2008 R2 STD w/SP3 box running on Windows 2012 R2 with two instances each, Server1 is the PRIMARY and Server2 is the MIRROR. Server2 was purchased for the purpose of mirroring so no live DBs yet. But Server1 is already in production. So here's the complete detailed setup for both.

Created a domain\user account and used it for both Servers' SQLService

Principal Server Configuration

  • Name: SERVER1.domain.com
  • NIC1 : 172.xx.xx.80
  • NIC2 : 172.xx.xx.81 (use for mirroring)
  • Named Instances: INSTANCE1 and INSTANCE2

Mirror Server Configuration

  • Name: SERVER2.domain.com
  • NIC1 : 172.xx.xx.82
  • NIC2 : 172.xx.xx.83 (use for mirroring)
  • Named Instances: INSTANCE1 and INSTANCE2

NO WITNESS YET

Static A records in DNS server

172.xx.xx.81 dbm-pri.domain.com and 172.xx.xx.83 dbm-mir.domain.com

Here's what's done on both servers:

  1. checked SQL Server Config Manager and made sure TCP is enabled, then added the ports to be used in IPAll field

  2. restarted the SQL Service (at one point of despair I restarted the servers itself, yes even the one running on production)

  3. run netstat and confirmed that the endpoint ports are LISTENING, (sqlserver.exe), also run ping and tracert all tests are good

  4. from both PRINCIPAL and MIRROR, ran telnet FQDN port, confirmed that I can connect from/to both

  5. created the endpoints on both servers

PRINCIPAL

    CREATE ENDPOINT INSTANCE1_EndPoint
       STATE=STARTED
       AS TCP (LISTENER_PORT=6025,LISTENER_IP=(172.xx.xx.81))
       FOR DATABASE_MIRRORING (ROLE=PARTNER)

MIRROR

    CREATE ENDPOINT INSTANCE1_EndPoint
       STATE=STARTED
       AS TCP (LISTENER_PORT=6025,LISTENER_IP=(172.xx.xx.83))
       FOR DATABASE_MIRRORING (ROLE=PARTNER)

  1. granted CONNECT permission to the domain\user account

    CREATE LOGIN [domain\user] 
FROM WINDOWS 
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

GRANT CONNECT on ENDPOINT::INSTANCE1_EndPoint TO [domain\user]
GO

  2. backed up and restored the DB and logs with NO RECOVERY

  3. ran alter db set partner on the mirror server

    ALTER DATABASE DBName SET PARTNER = 'TCP://dbm-pri.domain.com:6025'

  4. ran alter db set partner on principal server

    ALTER DATABASE DBName SET PARTNER = 'TCP://dbm-mir.domain.com:6025'

    Error:

    Msg 1418, Level 16, State 1, Line 1
    The server network address "TCP://dbm-mir.domain.com:6025" can not be reached or does not exist...

  5. tested netstat, ping, tracert and telnet on both Servers, all are successful

I've tried re doing this so many times now and I'm lost for ideas

Improve this question
5
  • Hi, when you say "granted CONNECT permission to the domain\user account", to which user did you grant the permissions ? Did you run the <script>grant connect</script> to the Service Account of both instances (on the source, to the service account of the mirror server and vice versa) ?
    – Jefferson B. Elias
    Nov 15, 2015 at 7:44
  • I gave permissions to the Service account which is also a domain account that I created specifically for the purpose. Yes, I did run the grant connect on both instances.
    – RCaballero
    Nov 16, 2015 at 9:57
  • Is there a firewall between both servers ? Are there VLANs configured ? Is there an antivirus running or any other process using the port ? Generally, you could see some useful informations in the error log.
    – Jefferson B. Elias
    Nov 16, 2015 at 13:08
  • I presume you resolved this eventually. What was the issue?
    – Martin Smith
    Jul 9, 2016 at 20:05
  • This is pretty old but one thing I'm not seeing is the encryption of the connection being defined. Try creating the endpoint with just AES (or both) and explicitly state it. Also test the service accounts passwords are correct on both sides and nothing else is listening on that port on either side. There doesn't seem to be any level 7 firewalls in between the nodes.
    – Ali Razeghi - AWS
    Feb 6, 2017 at 3:23

5 Answers 5

Reset to default
1

Have you tried using an IP address? I have seen an issue in R2 in the past whereby SQL Server was unable to resolve an FQDN but was fine with an IP address.

Improve this answer
0

You may have already attempted this, but I must ask: Have you attempted to initialize mirroring via the SSMS GUI?

In SSMS on principle server, right-click database --> Mirror --> Configure Security.

I'm curious if the same error message is generated from the GUI. Can you establish a connection to your mirror server from the principle server via SSMS?

Improve this answer
2
  • Yes I did, got the same error. And yes I can establish connection from mirror to principal and vice versa via SSMS only the mirroring feature is not working.
    – RCaballero
    Jul 28, 2015 at 10:45
  • did you verified, if you firewall is enabled at mirror server??
    – Rajesh Ranjan
    Aug 9, 2016 at 8:17
0

Sometime before even I faced the same issue while configuring database mirroring.

Generally SQL Server Database Mirroring Error 1418 occurs due to connection problem between endpoints.

Below checks can be followed in order to fix this issue:

  • Check TCP/IP port and ensure that it is enabled for both instances.
  • Ensure that the system Firewall is not blocking the SQL Server port.
  • Ensure that Database Mirroring endpoints/ports are used only by database mirroring process. If another process is listening on the port assigned to database mirroring endpoints, the database mirroring processes cannot communicate with each other.
  • Finally check the SQL Server Accounts used to run the SQL Server Service.
  • Ensure that the same SQL Server Accounts are used in both instances involved in the database mirroring.
  • It is recommended that we use domain accounts for all partners.
  1. Open the SQL Server Configuration Manager or Services.msc
  2. And ensure that the SQL Server and SQL Server Agent's services are running with the same credentials.
  3. Check the credentials used in the mirror database server.
  4. Ensure that the credentials used in the mirror database server are exactly similar to the principal server.
  5. If the user is not created then create a new one on the both servers with same credentials.
  6. Once the user is created on the servers> Login to the principal server > open SQL Server instance and add the new login under Security and map the user with the SQL Login. 7. Once the server user is mapped with the SQL instance; assign Sysadmin role to that login on the SQL Server.
  7. Follow the step 7 on the mirror server as well.

After all these checks the error 1418 must be resolved.

Improve this answer
-1

It could be a possibility port number 6025 might be used by another application try changing the port number .

Improve this answer
-1

I also had the same problem while implementing mirroring to one of my customers, the problem was SQL server Service account for both instances must be a domain name. Once again confirm that.

And try this too.

GRANT CONNECT ON ENDPOINT::Mirroring TO ALL
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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