Database Mirroring setup issue

Question

Database Mirroring setup issue

pshaship

SSCertifiable

Points: 6125
More actions
July 11, 2011 at 4:44 pm

#243876

Hi,
I'm trying to set up database mirroring using the database mirroring wizard in SQL Server 2008 R2 RTM x64 developer edition on Windows Server 2008 R2 EE x64 SP.
Primary Server: SRV1
Instance:SRV1/ins1
Mirror Server:SRV2
Instance:SRV2\ins1
I have selected all default configuration in the wizard to set up mirroring for AdventureWorks database .
But getting the below error:
TITLE: Database Properties
------------------------------
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:
Alter failed for Database 'AdventureWorks'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server network address "TCP://SRV1.ABC.COM: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. (Microsoft SQL Server, Error: 1418)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I'm able to telnet the port 5022 from both the servers SRV1 & SRV2.
I did turn OFF the Windows firewall too.
Do we need to do anything with MSDTC configuration?
What else do I need to verify to resolve the issue?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

Is ABC.com your actual domain name?

Database mirroring error
The server network address "TCP://SRV1.ABC.COM: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. (Microsoft SQL Server, Error: 1418)

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

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

pshaship SSCertifiable Points: 6125 More actions · Answer 2

No. ABC.COM is NOT the actual domain. I have replaced my actual domain name with ABC while posting in the forum.

seth delconte SSCertifiable Points: 6388 More actions · Answer 3

Here are some good related fixes... are you getting that error for your Principal server (I assume so, since I believe 5022 is the default port...).

http://blog.sqlauthority.com/2010/01/11/the-server-network-address-tcpsqlserver5023-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-microso/[/url]

http://blog.sqlauthority.com/2007/04/22/sql-server-fix-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/[/url]

_________________________________
seth delconte
http://sqlkeys.com

pshaship SSCertifiable Points: 6125 More actions · Answer 4

Thanks for the links.

Below are the error messages from the errorlog

On Principal server's error log:

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://SRV2.ABC.COM:5022'.

On Mirror Server's errorlog

Database Mirroring login attempt by user 'ABC\mssqlsrv.' failed with error: 'Connection handshake failed. The login 'ABC\mssqlsrv' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.10.10.13]

I tried to grant the connect permission to ABC\mssqlsrv but it's complaining that the login DOES NOT exist.
GRANT CONNECT ON ENDPOINT::Mirroring TO [ABC\mssqlsrv]
Msg 15151, Level 16, State 1, Line 1
Cannot find the login 'ABC\mssqlsrv', because it does not exist or you do not have permission.

Please advice..

seth delconte SSCertifiable Points: 6388 More actions · Answer 5

Have you restored the last transaction log backup on the mirror instance with NO RECOVERY?

http://www.sqlservercentral.com/Forums/Topic683162-146-1.aspx#bm684411

Also, check and see what login accounts your SQL services are using - apparently you need certificates if you are using Local Service Accounts:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/73fb15c0-9270-4cbf-a74e-544639e792da/[/url]

_________________________________
seth delconte
http://sqlkeys.com

Perry Whittle SSC Guru Points: 233846 More actions · Answer 6

pshaship (7/12/2011)
Thanks for the links.
Below are the error messages from the errorlog
On Principal server's error log:
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://SRV2.ABC.COM:5022'.
On Mirror Server's errorlog
Database Mirroring login attempt by user 'ABC\mssqlsrv.' failed with error: 'Connection handshake failed. The login 'ABC\mssqlsrv' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.10.10.13]
I tried to grant the connect permission to ABC\mssqlsrv but it's complaining that the login DOES NOT exist.
GRANT CONNECT ON ENDPOINT::Mirroring TO [ABC\mssqlsrv]
Msg 15151, Level 16, State 1, Line 1
Cannot find the login 'ABC\mssqlsrv', because it does not exist or you do not have permission.
Please advice..

add the login to the mirror instance and grant connect permissions to the endpoint and re try

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

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

pshaship SSCertifiable Points: 6125 More actions · Answer 7

Issue resolved:-)

Reason for the issue:

The SQL Server service account abc\mssqlsrv did not exit in logins on both Principal & Mirror

Solution:

Add the service account in logins on both the servers and grant the connect permission as below;

GRANT CONNECT ON ENDPOINT::Mirroring TO [abc\mssqlsrv]

Hope this helps someone!!

Perry Whittle SSC Guru Points: 233846 More actions · Answer 8

geenrally you dont want to use the sql server service account as the security principal for your endpoints. Create a base domain user, grant login to sql server and grant connect permissions to the end point

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

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

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 9

pshaship (7/12/2011)
Issue resolved:-)
Reason for the issue:
The SQL Server service account abc\mssqlsrv did not exit in logins on both Principal & Mirror
Solution:
Add the service account in logins on both the servers and grant the connect permission as below;
GRANT CONNECT ON ENDPOINT::Mirroring TO [abc\mssqlsrv]
Hope this helps someone!!

Thanks for sharing!

My blog