Database Mirror Error Msg 1418

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Database Mirror Error Msg 1418

Database Mirror Error Msg 1418

Rate Topic

Display Mode

Topic Options

Author

Message

KTD

Posted Tuesday, April 24, 2012 3:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 9, Visits: 178

Hi, I have setup many mirrored databases before but this time has been very frustrating. I did search this error all day and nothing I have found has resolved my issue.
Of course I restore the full and all transaction logs leaving it in recovery.

I hope I can make this make sense. This is what I have checked.

First the security. Both use the same domain account.
I made both accounts SA and ADmin on the box to eliminate that as an issue.
Before that I had granted it Connect.

GRANT CONNECT on ENDPOINT::Mirroring TO xxx\xxx
GO

Confirmed by

SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR,
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDER BY Permission,grantor, grantee;
GO

Both Endpoints exist and the port is open and listening confirmed with netsstat and telnet to the port.

select * from sys.database_mirroring_endpoints

I also made sure again they were started even though it reports they were.

ALTER ENDPOINT Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR database_mirroring (ROLE = ALL);
GO

I checked the SPN existed on both servers and it list them.

Setspn.exe -L <ComputerName>

So I start the mirror and it fails

First on the mirror. This works

ALTER DATABASE xxxx
SET PARTNER =
'TCP://xxxxx:5022'

GO

Then

ALTER DATABASE xxxx
SET PARTNER =
'TCP://xxxxx:5022'
GO

This fails with the error 1418

Any help is appreciated.

Post #1289401

vyas

Posted Wednesday, September 26, 2012 5:00 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:02 AM
Points: 3,131, Visits: 1,056

Please check the following URL:

http://blogs.msdn.com/b/grahamk/archive/2008/12/08/database-mirroring-error-1418-troubleshooter.aspx

Post #1364567

« Prev Topic | Next Topic »

Permissions