locked
Problems on connection during mirroring (Error 1418) RRS feed

  • Question

  • Hi everyone, I am a newbie on setting up database mirroring and right now I am facing the connection problem during mirroring which is Error 1418 which kinda common problem. I just view through all the threads regarding this problems and BOL also. However, I just based on all the guide to setting it up and the result are still the same.

    I did tried on both instance names in my server for mirroring and displayed the same results as well. When I type this t-sql on mirror server :

    ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://TSUYOSHI:5022' -- which is the  principal server

    It shows connection ok to me. However, when I type this t-sql on the principal server :

    ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://HAN:5023' -- which is the  Mirror server

    It shows connection ok to me. However, when I type this t-sql on the principal server :

    Msg 1418, Level 16, State 1, Line 1

    The server network address "TCP://HAN:5023" 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.

    I just wondering that why mirror can get a connection to principal but principal can't? Is that any extra configuration especially security settings that needed to be set within the database (MS sql server 2005) ?

    I hope I am able to get any assistance from anyone at here and will appreciate on it. Thanx. Anyway, sorry for the inconvenience

    Best Regards, 

                              Hans

    Friday, November 3, 2006 10:25 AM

Answers

  • Hans, the issue you are experiencing is definately due to the address you are using for the server.  The other responses to your post denote one path to correct the issue.  However if you are not utilizing a Domain controller, the problem and resolution are much different than explained below.  You will need to use Certificates to communicate between servers (See "Using Certifiactes for Database Mirroring" in SQL Server 2005 Books Online.  The two detailed documents that procedure refernces give details and scripts that do work.  You may also be experiencing another issue.  It is simply poor coding on MS's part, but it can be worked around.  If you do not have a DNS server in your environment, which many people do not any longer due to 'poisoning' issues and such, then you will need to replace your server Names with their IP Addresses.  The code throws the error simply because it cannot find a '.' (dot) in the server name.  It doesn't even try to resolve it (yes that is dumb/stupid, go figure). In the next release of mirroring, (it's super new right now so we have to deal with these types of issues, worth the effort thought, don't get me wrong), I think much of the manual scripting will be replaced with a more robust configuration 'wizard'  The wizard interface as it exists today only works in very specific circumstances.  I would recommend avoiding it. 

     

    First I would try the quick way out, replace your server name with the IP "ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://TSUYOSHI:5022' " to ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://192.168.xx.xx:5022'

     

    If that doesn't work for you, go try the certificates method.  It's miserable, but works well.  I would argue that it is more secure than the domain authentication method anyways.  I can give you more details if this doesn't work. 

    Wednesday, April 25, 2007 1:01 PM

All replies

  • Anything in the errorlog? any service broker messages in the errorlog?

    Assuming you are using Windows authentication, make sure the service accounts of the sql services have connect privileges on the relevant endpoints and make sure the endpoint is started.

    Try using a fully qualified server name in the partner string, could be a dns issue. You could try using telnet to see if it raises an error when connecting to the endpoint port on the mirror from the principal server. Make sure a firewall isn't blocking communication.

    Use netstat -b -v to see if the endpoint ports are in use and if so which app is using it. If it's working right you should see sql server. Make sure that another app isn't using the port. Try another port to see if it makes any difference. You don't need to use 5022 as there is no default, no requirement here other than to use a prort number over 1024 which isn't in use by something else.

     

     

    Friday, November 3, 2006 10:49 AM
  • Here's the log file that shown in my sql server 2005.

    Date,Source,Severity,Message,Category,Event,User,Computer
    11/04/2006 12:38:56,spid21s,Unknown,Database mirroring has been terminated for database 'AdventureWorks'. This is an informational message only. No user action is required.,,,,
    11/04/2006 12:38:56,spid21s,Unknown,Error: 1443<c/> Severity: 16<c/> State: 2.,,,,
    11/04/2006 12:38:56,MSSQL$TSUYOSHI,Error,Database mirroring has been terminated for database 'AdventureWorks'. This is an informational message only. No user action is required.,Server,1073743267,,HAN

    It doesn't show any extra information by the way. And the information stated as below are the settings for my principal and mirror's endpoints and both seems are the same.

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

    a) (TSUYOSHI) Principal server's Information :

    i) sys.database_mirroring_endpoints

    name : Mirroring

    endpoint_id : 65536

    principal_id : 1 

    protocol : 2,

    protocol_desc : TCP

    type : 4

    type_desc : DATABASE_MIRRORING

    state : 0 

    state_desc : STARTED

    is_admin_endpoints : 0 

    role : 1 

    role_desc : PARTNER

    is_encryption_enabled : 1

    connection_auth : 3 

    connection_auth_desc : NEGOTIATE

    certificate_id =  0 

    encryption_algorithm = 1 

    encryption_algorithm_desc = RC4

    ii) sys.endpoints

    name : Mirroring 

    endpoint_id : 65536 

    principal_id : 1 

    protocol : 2 

    protocol_desc : TCP

    type : 4

    type_desc : DATABASE_MIRRORING

    state : 0 

    state_desc : STARTED 

    is_admin_endpoint : 0

    iii) sys.tcp_endpoints

    name : Mirroring 

    endpoint_id :65536 

    principal_id : 1 

    protocol : 2 

    protocol_desc : TCP 

    type : 4 

    type_desc : DATABASE_MIRRORING 

    state : 0 

    state_desc : STARTED 

    is_admin_endpoint : 0 

    port : 5022 

    is_dynamic_port : 0 

    ip_address : NULL

    b) (HAN) Mirror server's Information :

    i) sys.database_mirroring_endpoints

    name : Mirroring 

    endpoint_id : 65536 

    principal_id : 1 

    protocol : 2 

    protocol_desc : TCP 

    type : 4 

    type_desc : DATABASE_MIRRORING 

    state : 0 

    stated_desc : STARTED 

    is_admin_endpoint : 0 

    role : 3 

    role_desc : ALL

    is_encryption_enabled : 1 

    connection_auth : 3 

    connection_auth_desc : NEGOTIATE 

    certificate_id : 0 

    encryption_algorithm : 1 

    encryption_algorithm_desc : RC4

    ii) sys.endpoints

    name : Mirroring 

    endpoint_id : 65536 

    principal_id : 1 

    protocol : 2 

    protocol_desc : TCP

    type : 4

    type_desc : DATABASE_MIRRORING

    state : 0 

    state_desc : STARTED 

    is_admin_endpoint : 0

    iii) sys.tcp_endpoints

    name : Mirroring 

    endpoint_id :65536 

    principal_id : 1 

    protocol : 2 

    protocol_desc : TCP 

    type : 4 

    type_desc : DATABASE_MIRRORING 

    state : 0 

    state_desc : STARTED 

    is_admin_endpoint : 0 

    port : 5023 

    is_dynamic_port : 0 

    ip_address : NULL

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

    Is there any wrong for the settings? Both also shown 'started' for the endpoints. And there's no firewall settings as well.

    Extra questions :

    1) What's the 'Build-in Account' that is valid to prefer for log on in both  principal and mirror server? (Local System/Local Service/Network Service). I am using Local System for login into both servers by the way.

    2) What's the meaning of Service Accounts? Is it needed to be set? Can give  some example on it ?

    3) Is there any configuration need to be concern in preferred database's properties at principal server side ?  

    Sorry for the inconvenience again.Hope can solve it as soon as possible.Thx.

    Best Regards,

            Hans

    Saturday, November 4, 2006 6:24 AM
  • For the updates, I did tried on the telnet connection to test on the port whether is available between the principal and mirror server. As the results, both of the ports are work. However, it still unable to get mirroring in server 2005. Is there any additional tasks required?

     

    Best Regards,

                    Hans

    Monday, November 6, 2006 9:46 AM
  • Hi Hans,

    I know this post is old, but I just ran into this problem myself. Turned out that the service account on each SQL instance didn't have Connect permissions to the endpoint on the other instance.

    So on each instance I just ran:

    create login [domainName\otherSqlInstanceAccount] from windows
    grant connect on endpoint::mirrorEndpointName to [domainName\otherSqlInstanceAccount]

    FYI: Look at the app/errorlog on the mirror instance, not just the principal. You'll notice an event like this:

    Database Mirroring login attempt by user 'DomainName\PrincipalSqlAccount.' failed with error: 'Connection handshake failed. The login 'DomainName\PrincipalSqlAccount' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT: <IP Addr>]

    I'm still working through my lab, so I'm not sure if there are other things needed to do yet.

    Hope it helps,

    Trevor

    Sunday, January 7, 2007 10:06 PM
  • I had the same problem - and the problem is that the error returned is not useful.

     

    The REAL problem, is that the "second" database needs to be restored with "norecovery". Failure to do so results in the error message re: TCP and ports.

     

     

    Wednesday, April 18, 2007 5:52 AM
  • As Simon suggested, use the Fully qualified domain name. and that should fix it.

    Wednesday, April 18, 2007 11:10 AM
  • Bonjour,

     

    I just read your mail as I'm  looking for another probleme with mirroring and ASP.Net application.

    You should use the same port TCP. For now, they are different and should not be different.

     

    Best regards,

    Sandrine

    Thursday, April 19, 2007 10:02 AM
  • Hans, the issue you are experiencing is definately due to the address you are using for the server.  The other responses to your post denote one path to correct the issue.  However if you are not utilizing a Domain controller, the problem and resolution are much different than explained below.  You will need to use Certificates to communicate between servers (See "Using Certifiactes for Database Mirroring" in SQL Server 2005 Books Online.  The two detailed documents that procedure refernces give details and scripts that do work.  You may also be experiencing another issue.  It is simply poor coding on MS's part, but it can be worked around.  If you do not have a DNS server in your environment, which many people do not any longer due to 'poisoning' issues and such, then you will need to replace your server Names with their IP Addresses.  The code throws the error simply because it cannot find a '.' (dot) in the server name.  It doesn't even try to resolve it (yes that is dumb/stupid, go figure). In the next release of mirroring, (it's super new right now so we have to deal with these types of issues, worth the effort thought, don't get me wrong), I think much of the manual scripting will be replaced with a more robust configuration 'wizard'  The wizard interface as it exists today only works in very specific circumstances.  I would recommend avoiding it. 

     

    First I would try the quick way out, replace your server name with the IP "ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://TSUYOSHI:5022' " to ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://192.168.xx.xx:5022'

     

    If that doesn't work for you, go try the certificates method.  It's miserable, but works well.  I would argue that it is more secure than the domain authentication method anyways.  I can give you more details if this doesn't work. 

    Wednesday, April 25, 2007 1:01 PM
  • MPD.JHB is correct.  I had the same error. Once I restored the second database with NO Recovery option, the mirroring worked like a charm

     

    Friday, June 29, 2007 8:48 PM
  • I solved the problem by changing the way that SQL server (SQL server and Agent) services start, instead of local system account i used domain user account that had rights on both servers !

    Best regards
    Nikola
    Monday, July 16, 2007 9:28 AM