April 24, 2012 at 3:27 pm
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.
September 26, 2012 at 5:00 am
Please check the following URL:
http://blogs.msdn.com/b/grahamk/archive/2008/12/08/database-mirroring-error-1418-troubleshooter.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply