Post reply

Database Mirroring Setup Failure.

  • November 28, 2010 at 10:41 am

    #227660

    When the Start Mirroring Option is pressed, the below error is logged in the Windows Event viewer.

    I am running the SQL Server services in the Local Account.

    Any suggestion to fix the issue would be helpful.

    Database Mirroring login attempt by user 'WORKGROUP\SOURAV-PC$.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\NETWORK SERVICE' does not have CONNECT permission on the endpoint. State 84.'.

    I have the following login accounts in my local system:

    Principal Server (Syslogins)

    ======================

    NT AUTHORITY\SYSTEM

    NT SERVICE\MSSQLSERVER

    Sumona-PC\Administrator

    Sumona-PC\sourav

    Sumona-PCimki

    NT SERVICE\SQLSERVERAGENT

    ##MS_PolicyEventProcessingLogin##

    ##MS_AgentSigningCertificate##

    distributor_admin

    Mirror_login

    users

    sqllogin

    adven_login

    Mirror Server (Syslogins)

    =======================

    NT AUTHORITY\SYSTEM

    NT SERVICE\MSSQL$INSTANCE2

    Sumona-PC\sourav

    NT SERVICE\SQLAgent$INSTANCE2

    NT AUTHORITY\LOCAL SERVICE

    Mirror_login

    Regards

    Sourav

    Thanks.

  • November 28, 2010 at 11:34 am

    #1256054

    Sourav-657741 (11/28/2010)

    The login 'NT AUTHORITY\NETWORK SERVICE' does not have CONNECT permission on the endpoint. State 84.'.

    The message above seems fairly descriptive, can you provide more details as to the security settings you used for the mirroring session?

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • November 29, 2010 at 12:59 am

    #1256130

    Hi,

    As per your error I think you are trying to configure mirror on Workgroup environment. Please check BOL for configuring Mirroring in Workgroup.

    http://blogs.msdn.com/b/suhde/archive/2009/07/13/step-by-step-guide-to-configure-database-mirroring-between-sql-server-instances-in-a-workgroup.aspx

    Ram
    MSSQL DBA

  • November 29, 2010 at 1:15 am

    #1256136

    Hi,

    if you are trying to set up mirroring in a workgroup environment, you must do following:

    1. Create same user on all involved pc's (same username with same password).

    2. Let all involved sql server instances run under this created user.

    3. Set up the mirroring and set this created user as user who should have rights on the endpoint.

    That's it.

    Good luck. 😉

    Greets pitcher

    [font="Arial"]Kind regards,

    Patrick Fiedler
    Consultant

    Fiedler SQL Consulting

    www.fiedler-sql-consulting.de
    [/font]

  • November 29, 2010 at 2:49 am

    #1256150

    Thanks Reo. This is the exact doc I was looking for. Quire relevant in my context.

    Thanks.

  • November 29, 2010 at 7:53 am

    #1256262

    Hi sourav,

    Do one thing you add NTAUTHORITY\NETWORK SERVICE as a login in both principal and mirror server with SYSADMIN server role

    end point will start communicate.

    Hope this will solve ur problem

    Best Regards,

    Bhargav.

  • June 19, 2011 at 8:36 am

    #1340867

    Hey guys,

    This is my first post on SQLServerCentral (which I love).

    Anyway, I had crazy trouble getting mirroring going on domain and machine accounts. I'm studying for 432 so I had to get this thing going. I opted to demote my VM from a DC (Windows Server 2008) and get rid of the certificate authority which was causing all kinds of problems.

    I had it working in the domain but couldn't get it working with machine accounts. You must have a "sqluser" account on each instance with sysadmin role granted of course. Also, you must grant connect permissions to that login under securable-->endpoints-->"mirroring" (your endpoint name).

    My server also has a DNS role with a basic zone configured with an entry for my VM machine.

    Finally got it.

  • July 10, 2011 at 2:03 pm

    #1350854

    Most of the information provided on the Web in this regard is quite misleading. People have talked about that the mirroring does not start since FQDN (the fully qualified domain name) are not usable in workgroup environment and have suggested that IP addresses should be used instead but this trick has nothing to do with the solution to problem. Others have suggested to restore with no recovery but actually the problem always occurs prior to reaching this step. The real problem is due to the Windows Authentication mode that is always chosen when we use GUI to create the mirroring endpoints no matter what and here is where the problem starts. I have found the following link as a very good and practical solution and have myself implemented the database mirroring in the workgroup environment by following the steps provided in the tutorial.

    http://blogs.msdn.com/b/suhde/archive/2009/07/13/step-by-step-guide-to-configure-database-mirroring-between-sql-server-instances-in-a-workgroup.aspx

    This is very well tailored tutorial. The only thing to care about is the Start_Date statement which should be current and you should also not be misled by the "Use Master" statement since it does not mean that you are mirroring the "Master" database. In fact, System databases can't be mirrored. You will have to use the "Master" database to create and implement certificate for mirroring. Moreover you can also specify an Expiry_Date as by default certificate is leased for one year. By using Expiry_date statement by putting a coma after the Start_Date, you can create certificate that has been leased for less than or more than one year. Go ahead and enjoy the outcome at the end.

    cheers,

    Mahmood A. Sheikh.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply