Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Mirror Error Msg 1418 Expand / Collapse
Author
Message
Posted Tuesday, April 24, 2012 3:27 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:04 AM
Points: 28, Visits: 312
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.



I don't always test my SQL scripts, but when I do, I test in Production.
Post #1289401
Posted Wednesday, September 26, 2012 5:00 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 8:57 AM
Points: 3,138, Visits: 1,060
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 »

Add to briefcase

Permissions Expand / Collapse