March 11, 2009 at 9:05 am
Hi all,
I am trying to set up Database Mirroring between 2 instances (principal and mirror) Using Windows Authentication
but when i try to establish mirror i get this error:
Error: 1418 - Microsoft SQL Server - The server network address 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.
and in SQL Logs it writes:
On principal server:
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://CLICKTALE-MIROR:5002'.
On mirror server:
Logon,Unknown,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.'.
I created two virtual machines with MSSQL with sp2 (ver.:9.00.3042) on my computer.
Some info on current state:
- principal and mirror server instance are not behind a firewall
- other processes don't use port that i used for mirror (checked it with: netstat -abn)
- used telnet to see if I could get onto each server via the port
- mirror database restored with "NO RECOVERY"
- checked that end-points are in start state
What should i do to solve this error?
Thanks,
rafi
March 11, 2009 at 10:22 am
Hi there,
A couple of things that werent clear to me while reading:
A: are both your instances local (ie INSTANCE1 and INSTANCE2) and are they named or default?
B: Did you try to script or use the wizard.
When you are mirroring to a local instance the ports are slightly different (because the listener port cant be the same between 2 local instances) however if you use the wizard is does all of thisi for you.
Also you must ensure when you prepare the mirror that you restore the database (with RESTORE NON REVOVERY) and then you need to restore one log backup in the same way).
Ensure that the AD user you are using to access both the pricipal and the mirror has relevant access to them both (i tend to use the sql engine user).
Give it a go and let us know. 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 11, 2009 at 10:47 am
Hi Adam,
Thanks for responding.
Those are two SQL server instances on two different computers (virtual). They have the default name (nothing). Therefore ports don't seem to be the problem.
"Ensure that the AD user you are using to access both the pricipal and the mirror has relevant access to them both (i tend to use the sql engine user)."
It is important to mention, that the servers are not in a domain. They are stand alone. We are not sure how to configure them both for authentication. We tried certificated, but it didn't work.
If setting up Database Mirroring Using Certificates then in SQL logs you get the following error on principal:
"Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed.
The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89.'. "
there is no error on mirror server when trying the process with certificates.
Thanks,
Rafi and Arik.
March 11, 2009 at 10:58 am
Ok, we can get to the certification later.
For now run the scrip below on both servers. It will create a user called 'MirrorUser' with a password of 'mirror'. I am not actually sure what server roles the user needs (mine have sysadmin because they are the service account), so you could try SYSADMIN and work backwards, or try with only public server access and work forwards.
Either way (once you get the right server access level) if you set the principal and mirror account with the details you should be ok.
Let me know.
-- Login: MirrorUser
CREATE LOGIN [MirrorUser] WITH PASSWORD = 0x01006073E9663466713B5CA4359BA2E92D453E8D9C56F61DFC99 HASHED, SID = 0x031FBAD29C27C240A37497405AFABE24, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 11, 2009 at 11:16 am
ok, created on both servers but got the same error, tried with only public role and then with sysadmin, also tried to give grant connection to that login but it didn't help.
-- Login: MirrorUser
CREATE LOGIN [MirrorUser] WITH PASSWORD = 0x01006073E9663466713B5CA4359BA2E92D453E8D9C56F61DFC99 HASHED,
SID = 0x031FBAD29C27C240A37497405AFABE24, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
GO
CREATE ENDPOINT Mirroring_Endpoint
STATE=STARTED AS TCP (LISTENER_PORT=5001)
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint
TO MirrorUser
GO
-- Specify the partner from the principal server
ALTER DATABASE [AdventureWorks] SET PARTNER = N'TCP://CLICKTALE-MIROR:5001';
GO
March 11, 2009 at 11:21 am
raful_k (3/11/2009)
ok, created on both servers but got the same error, tried with only public role and then with sysadmin, also tried to give grant connection to that login but it didn't help.-- Login: MirrorUser
CREATE ENDPOINT Mirroring_Endpoint
STATE=STARTED AS TCP (LISTENER_PORT=5001)
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
You are missing the authorization clause in the End points? SQL Server needs to know how to authenticate between two servers?
Try this ...
CREATE ENDPOINT Mirroring_Endpoint
AUTHORIZATION Domain\SQLServiceAccount
STATESTARTED AS TCP LISTENER_PORT=5001)
FOR DATABASE MIRRORING (Authentication=Windows, Roll=Partner)
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 11, 2009 at 11:36 am
Ok,
you are right when i re-created my end-point on principal server it created me
/****** Object: Endpoint [Mirroring_Endpoint] Script Date: 03/11/2009 19:30:08 ******/
CREATE ENDPOINT [Mirroring_Endpoint]
AUTHORIZATION [CLICKTALE-PRINC\Rafi]
STATE=STARTED
AS TCP (LISTENER_PORT = 5001, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
while on mirror server it created:
/****** Object: Endpoint [Mirroring_Endpoint] Script Date: 03/11/2009 19:32:44 ******/
CREATE ENDPOINT [Mirroring_Endpoint]
AUTHORIZATION [CLICKTALE-MIROR\Rafi]
STATE=STARTED
AS TCP (LISTENER_PORT = 5001, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
which are not same.
So i tried set up by using certificate but got this error in SQL log:
Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed.
The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89.'.
but it strange because i see both certificates (one of principal and one of mirror servers) on master db.
Thanks for answer,
rafi
March 11, 2009 at 11:57 am
Please ref http://technet.microsoft.com/en-us/library/ms191140(SQL.90).aspx on how to use certificates with Mirroring...
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 12, 2009 at 8:22 am
ok, thanks to link, i created again certificates as Microsoft suggested and it worked.
rafi
March 12, 2009 at 8:29 am
*cheers* :w00t: :w00t:
Now you get to deal with other fun parts of mirroring hehe; make sure you implement T-Log backups with full db backups on both servers. SQL Server is smart enough that it will only do backup when the databae is active on the server. If not it will ignore it 🙂
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply