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..
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.
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)
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.
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.
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.
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);
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.
- Proposed as answer by Ajay.G Monday, February 07, 2011 11:17 AM
Another one that has shorter steps (same idea)
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
- Proposed as answer by masosoma Monday, September 20, 2010 11:40 AM
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
create login [Domain/SQLExpressMachineName$]
grant connect on endpoint::mirroring to [Domain/SQLExpressMachineName$]
I had to do this even though all SQL Services were running as a network service.
Thanks for the tip, struggled with that all day. Soon as I added domain\machinename$ in the 3 blanks in the wizard it started working right away! Must be necessary when running under the Network Services account since I suppose the connection originates from the machine account.
Here's the raw SQL that's needed for creating the SQL login and grant:
CREATE LOGIN [domain\machinename$] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
grant connect on endpoint::mirroring to [domain\machinename$]
(assuming your endpoint is named "mirroring")
Thanks for the help!
- Proposed as answer by NebSnow Monday, August 16, 2010 7:51 PM
Part of the problem that everyone seems to have with mirroring is the fact that using Local System and Network Services accounts for SQL Server services is not recommended if you want SQL 2005 to communicate with other clients and servers. It is recommended that one or more domain user accounts be setup. I typically create three separate accounts as the security requirements for each service are different. Look under preparing to install SQL Server 2005 in BOL for detailed info.
I have the above problems too….
After try and error and some readings, i fixed the problem……
Hope this help everyone:
- Proposed as answer by ddimm Wednesday, January 06, 2010 9:24 PM
Thank you Kelo for your clear jotted points I was able to configure the database Mirroring along with witness server configured.
I used same domain service for all the servers(Principal, Mirror & Witness)
I also followed this http://technet.microsoft.com/en-us/library/cc917680.aspx to configure it with the encryption disabled
Here is a shocker; I forgot how long I’ve been working on this mirroring error 1418 – two, maybe three months... I was considering migrating corporate database from Oracle 11 to SQL 2008 but since mirroring is choppy to setup I can’t justify purchasing SQL 2008. I even have a DVD on SQL 2008 and the instructor points out error 1418 and he can’t fix it either. Microsoft and SQL 2008 needs more structure.
1. Make sure that you are using the same username and password to run the SQL Server service on both machines. - It is because SQL Server connect to each other with NT Auth. that using same username and password can prevent a lot of problems. - Using Configuration Manager to set the user for running the service. 2. Make sure that the username for running SQL Server service got all the rights. - Use "Surface area configuration" to assign the rights to the current user. 3. Make sure that you can connect to both machines with Windows Auth - To check it, just open SQLManagement Studio, connect to both server with Windows Auth - Update: I find out that SQL Server 2005 talk with each other with "Named Pipes" connection. You MUST enable "Named Pipes" on all servers! Check the following link for more details: http://alan328.com/SQL2005_Database_Mirroring_Tutorial.aspx
I had this problem as well. After double and triple checking the end points were setup, and the firewall had the proper rules and the permissions were correct. Compared everything to another instance of SQL that was mirroring.
I noticed that I hadn't restored the database on server $2 (the mirror) properly. Make sure you use the "NO RECOVERY" option. And the database should appear in the "restoring" state in the GUI on the mirror server.
For some reason because I did a full restore With Recovery the first time to server #2 when i tried to "Start Mirroring" it would give me the error:
Error: 1418 - Microsoft SQL Server - The server network address "SERVER #1" can not be reached or does not exist. Check the network address name and reissue the command. The server network endpoint did not respond because the specified server network address cannot be reached or does not exist.
Which was 100% mis-leading and incorrect (especially since Server 1 was the server reporting the errror that it couldn't contact itself.
Once I did the correct type of restore on Server #2 I was able to easily configure mirroring following the wizard in Management Studio on Server #1.
TLDR: Be Sure you used the "Restore with NO RECOVERY" option / command. If the other troubleshooting steps didn't resolve the issue.
- Proposed as answer by ddimm Wednesday, January 06, 2010 9:24 PM
I think before you setup the database mirroring between the workgroup server, you need to read the following article first.
Even i followed above step... all the steps working fine but got error while executing step 20 i.e
ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'every thing
from above post.
I am using 2 systems under same domain for DB mirroring.
Principal sys. - OS - Windows server 2008 R2 standard & SQL server 2008 R2.
Mirror sys. - OS - Windows XP sp3 & SQL server 2008 R2.
Following are the check list I have done :
1. mssqlservices are running under NETWORK SERVICE account
2. Filestream for TSQL is not enabled
3. firewalls OFF.
4. I am a local admin in both machines & also is a sysadmin user in both sqlservers.
5. I am using Windows authentication in both system.
6. DB backup with full recovery model & DB restore to a mirror database with WITH NORECOVERY option
7. Since endpoint is already present by Mirroring name so i have not deleted it instead i have used alter command as follows.
alter ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 7023, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)
8. Same in mirror too only Listener port is different. i.e 5022
9. When I issue this command from mirror sys. its working fine
ALTER DATABASE RajDB
SET PARTNER ='TCP://PC32.MYDOMAIN.SOFT:7023'
but from principal sys.
ALTER DATABASE RajDB
SET PARTNER ='TCP://U15349479.MYDOMAIN.SOFT:5022'
then its throwing an error -- L
Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://U15349479.MYDOMAIN.SOFT:5022" 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.
In error log - Database mirroring has been terminated for database 'RajDB'. This is an informational message only. No user action is required.
Please help me to completing this task, working on this from past 1 month. Even I have gone though all the post but no solution.
I am looking ahead of your valuable supports.
Thanks & regards, Vincent
Hi Vincent. You should set in GUI setup Db Mirror (in section Service Account) one name of servis who use SQL Server Service (In my Server "DOMAIN\sql_server_service" - I set in Primary_Server, Secondary_Server, and Witness as service sql account) in 3 field, the same account... I had the same error!!! Michael...
I've performed a test on "Database Mirroring" on both test machine on test database named "Mirror" .
Server 1 : MSSQL.local
Server 2 : Backup.local
I've added IPs on both servers host file so that they can resolve the servers name to the corresponding IPs among each others.
I've tested on the setup and found error as below when started "Mirroring"
Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://backup.local:7024'.
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://backup.local:7024'.
Both servers firewall are turned off and I'm able to telnet to port 7024 between both servers.
I dont think i quite understand your request, ""I've performed a test on "Database Mirroring" on both test machine on test database named "Mirror"" .
1. Are your machines on the same domain?
2. Do you mean to say you tried to do the mirroring from both machines, first a, pointing to b, then b pointing to a?
Can you make your scenario more clearer
Just remember that error could be also that your encryption is different between partners, otherwise flip the Recovery model before using the backups on the mirror partner. Please see the full details to rid yourself of this error (which trapped me twice too!! dammit):
SQL Server MVP (2010)
Yes, that works too, but sort of defeats the purpose of using DNS (a host name is easier to work with, no?).
If your server moves physically by IP, then you have a broken mirror - which may not be an issue if your servers barley ever change IPs, but just be aware that is a consequence of using IP address workaround.