Database Mirror Error Msg 1418

  • 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.

Viewing 2 posts - 1 through 1 (of 1 total)

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