II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below
a) On primary server:
Error: 1474, Severity: 16, State: 1
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'
b) On mirror server:
Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'.
It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command
GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];
Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints
Hope someone can help me resolve this issue... Thank you very much..
What is the account that SQL server is running as?|||The account that SQL runs as is the one that it tries to login onto the other server as.|||Same issue here in our environment. Two database servers, one domain, one user, full permissions... Yet the connection is forcably terminated under an anonymous user.
Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||
In BOL there is a section that deals explicitly with setup errors for database mirroring.
Search for "troubleshooting database mirroring."
Thanks,
Mark
|||
I have exactly the same problem :
Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)
Source Logon
Message
Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.16.6.14]
That's from the error log of the mirror.
Both the principal and the mirror have sql running as the local service account for each machine respectively.
I've tried the wizard, I've tried TSQL, I've done all the troubleshooting steps you recommend. Can you please be very specific on what to do and what to put as logins when both principal and mirror run as each machines local service account.
Thanks!
Mark
|||
Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.
Once i did that it works just fine.
Hopefully, in future releases you'll make it easier to do that.
Thanks!
Mark
|||
Do you normally use Local System to run your SQL Server process?
Thanks,
Mark
|||Hello Mark,
Could you send me the documentation on how to do this (create certificates), I have read a few but having problems on the mirror node.|||i too would like to create certificates i have the same problem|||
I met the same problem.
This issue is due to the wrong login authority.when we excute the TSQL "ALTER DATABASE WorkDataBase SET PARTNER ='TCP://URL:7022'",this operating lead the SQL Server send a request to the mirror point with NT AUTHORITY\ANONYMOUS enven if the SQL Server service is running as the same domain user as the mirror point does.
And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.
if someone got other answers,please write here.
|||
Hello everybody.
After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!
Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.
0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR
1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.
2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.
3) MRRUSR is the MSSQLSERVER service account in both machines
4) Machine A is gonna be the PRINCIPAL, B the MIRROR.
5) Log on in my machine as MRRUSR
6) Open Sql server Studio. Connect to both machines using windows authenticacion.
7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data
8) Backup the new DB (Full backup) with .bak extension
9) Backup the new DB (Transaction log) with .trn extension
10) Copy the both files to a location in B machine.
11) Restore .bak into a new DB using NORECOVERY option
12) Restore .trn into the previous DB using NORECOVERY option
13) Go to Machine A, open a new query
14) Take a look to the endpoints
SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;
15) Delete existing ones (DROP ENDPOINT [NAME])
16) Create a new endopoint
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)
17) Start it
ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO
18) Do steps 13 to 17 in machine B.
19) Return to machine A. Query.
20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.
ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'
21) Repeat step 20 from MACHINE B pointing to MACHINE A
22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.
23) Lets test them: Add some new data in Machine A database.
24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.
25) Refresh the 2 databases in the UI you should see the new status and roles changed.
26) Open Machine B databse, the new data should be there.
Cheers!
Kelo
|||
Another one that has shorter steps (same idea)
http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005
|||
IMPORTANT! To establish any mirroring session (with or without certificates) you must use FQDN (Fully Qualified Domain Names) machinename.domain.com.Before going on, be sure that you can reach the 3 machines with a single PING. Also, is a good practice to ensure that you can reach a telnet call over the FQDN + the port you plan to use (5022 in this example)
FIX: Over the post above. When establishing a mirroring session using machine accounts (not certificates, so you are inside a DOMAIN) ensure your 3 mssqlservices are running under NETWORK SERVICE account
|||Hi all,
I'd like to add something to this however it's beyond the scope of a basic mirror. When adding a Witness to your mirror to allow for automatic failover you may experience the error on the Principle and Mirror stating
Database Mirroring login attempt by user 'Domain\SQLExpressMachineName$.' failed with error: 'Connection handshake failed. The login 'Domain\SQLExpressMachineName$.' does not have CONNECT permission on the endpoint.
This is deceiving as the Mirroring Wizard creates endpoints on all 3 hosts. Basically the error above is stating that the Witness server does now have access to the Master database endpoints on the Principle and Mirror, therefore you'll need to add the host name of the Witness Server running SQL Server Express. This is done using the GRANT tsql command
master
go
create login [Domain/SQLExpressMachineName$]
go
grant connect on endpoint::mirroring to [Domain/SQLExpressMachineName$]
I had to do this even though all SQL Services were running as a network service.
Cheers,
Damian
