Database Mirroring Setup Failure.

Question

Database Mirroring Setup Failure.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
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.

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

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

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" 😉

Reo Hall of Fame Points: 3774 More actions · Answer 2

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

Patrick_Fiedler SSCommitted Points: 1904 More actions · Answer 3

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]

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

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

Thanks.

bhargavgampala Old Hand Points: 333 More actions · Answer 5

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.

seanka SSC Rookie Points: 47 More actions · Answer 6

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.

mas_sm Valued Member Points: 73 More actions · Answer 7

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.