Post reply

Database Mirroring ERROR in SQL server 2012

  • July 21, 2015 at 6:20 am

    #300337

    The database mirroring already setup and was running fine earlier.

    Now suddenly the database status showing as principal server (Principal, Disconnected) and Mirror server (Mirror, Disconnected, In recovery).

    I know that Service accounts(SQL and agent) need to be the same on both boxes.

    observed on Principal server sql server services running on NT service\MSSQLSERVER account and Mirror server sql server services running on Domain account).

    but here they configured as one domain account(i.e,its not related to service accounts)

    i have started and stop the endpoint it didn't help for me and then i have Deleted and re-create the database mirroring endpoints on both servers.

    Now im getting the below error message.:

    Message

    Database Mirroring login attempt by user '.' failed with error: 'Connection handshake failed. The login does not have CONNECT permission on the endpoint. State 84.'.

    Same error on principal and mirror but accounts are different.

    I need to do reconfigure the mirroring (it wil work) or becase all the partners should run under the same domain accounts(i need to chane the accounts on Both prinicpal and mirror server)

    Please let us know your suggestions on this.

    Thanks in advance!!

    Thanks & Regards,

    DBA

    SQL server DBA

  • July 21, 2015 at 8:07 am

    #1813855

    Database admin(DBA) (7/21/2015)

    Now suddenly the database status showing as principal server (Principal, Disconnected) and Mirror server (Mirror, Disconnected, In recovery).

    Have you checked the logs, do you see any errors there?

    Database admin(DBA) (7/21/2015)

    I know that Service accounts(SQL and agent) need to be the same on both boxes.

    Erm, actually no they don't.

    Database admin(DBA) (7/21/2015)

    observed on Principal server sql server services running on NT service\MSSQLSERVER account and Mirror server sql server services running on Domain account).

    but here they configured as one domain account(i.e,its not related to service accounts)

    i have started and stop the endpoint it didn't help for me and then i have Deleted and re-create the database mirroring endpoints on both servers.

    Now im getting the below error message.:

    Message

    Database Mirroring login attempt by user '.' failed with error: 'Connection handshake failed. The login does not have CONNECT permission on the endpoint. State 84.'.

    Same error on principal and mirror but accounts are different.

    I need to do reconfigure the mirroring (it wil work) or becase all the partners should run under the same domain accounts(i need to chane the accounts on Both prinicpal and mirror server)

    Please let us know your suggestions on this.

    Thanks in advance!!

    Thanks & Regards,

    DBA

    NT service\MSSQLSERVER is a local account, you'll need to use certificated logins if you don't use domain accounts

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

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

  • July 21, 2015 at 9:25 am

    #1813883

    Thank you for your prompt reply.

    From Principal server error log information below:

    Message

    Database Mirroring login attempt by user 'xxx\xxxSQL.(Domain account i.e,SQL server account running on this account)' failed with error: 'Connection handshake failed. The login 'xxx\xxxSQL' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: Ip address]

    From Mirror server error log information as below:

    Message

    Database Mirroring login attempt by user 'XXX\XXXX$.' failed with error: 'Connection handshake failed. The login 'XXXX\XXXXSQL$' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: Ip address}

    How do i know the which account(or configured) using for DB mirroring ?

    below script i found it: select SUSER_NAME(principal_id),namefrom sys.database_miroring_endpoints

    How to find the certficate logins in the sql server ?

    because Principal server is running on NT service\MSSQLSERVER.

    Please help me on this issue.

    Thanks in advance!!

    SQL server DBA

  • July 21, 2015 at 9:27 am

    #1813884

    I used my Domain account to try to configure the DB mirroring,i received below error message:

    The server network address "TCP:/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)

    SQL server DBA

  • July 21, 2015 at 9:52 am

    #1813895

    Database admin(DBA) (7/21/2015)

    Message

    Database Mirroring login attempt by user 'xxx\xxxSQL.(Domain account i.e,SQL server account running on this account)' failed with error: 'Connection handshake failed. The login 'xxx\xxxSQL' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: Ip address][/code]

    If this is on the Principal, which I suspect it is, the account needs connect permission to the endpoint on the principal and also on the mirror server.

    Database admin(DBA) (7/21/2015)

    From Mirror server error log information as below:

    Message

    Database Mirroring login attempt by user 'XXX\XXXX$.' failed with error: 'Connection handshake failed. The login 'XXXX\XXXXSQL$' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: Ip address}[/code]

    Now, this is trying to connect as the computer account for the mirror server. Configure the mirror instance to use a domain account and ensure this account has

    • connect permission on the endpoint on the mirror
    • a login created on the principal
    • connect permission on the endpoint on the principal granted to the login created above

    Database admin(DBA) (7/21/2015)

    How do i know the which account(or configured) using for DB mirroring ?

    below script i found it: select SUSER_NAME(principal_id),namefrom sys.database_miroring_endpoints

    How to find the certficate logins in the sql server ?

    because Principal server is running on NT service\MSSQLSERVER.

    try this

    selectsper.permission_name +

    case sper.state

    WHEN 'G' Then ' granted '

    END +

    ' to [' + sp.name COLLATE Latin1_General_CI_AS + '] on endpoint [' +

    me.name + '], permission granted by [' + sp2.name COLLATE Latin1_General_CI_AS + ']'

    from sys.server_permissions sper

    inner join sys.server_principals sp on sper.grantee_principal_id = sp.principal_id

    inner join sys.server_principals sp2 on sper.grantor_principal_id = sp2.principal_id

    inner join sys.database_mirroring_endpoints me on sper.major_id = me.endpoint_id

    where sper.class = 105

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

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

  • July 21, 2015 at 10:54 am

    #1813916

    Prinicipal server error log:

    Database Mirroring login attempt by user 'xxx\xxxSQL.' failed with error: 'Connection handshake failed. The login 'xxx\xxxSQL' does not have CONNECT permission on the endpoint. State 84.'

    Please note:

    this is the domain account (and SQL server services running under this account)

    Mirror server Error log information:

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

    Please note this mirror server services running under NT service\MSSQLSERVER

    Please see my comments inline below.

    If this is on the Principal, which I suspect it is, the account needs connect permission to the endpoint on the principal and also on the mirror server.--> this is service account(domain account) but im not using this account to configure the morroring.

    as you suggested i found the certficate login(sysadmin permissions) and i have try to set up the DB mirroring using this account but still im getting the below error.( i have tested telnet servername 5022 its working fine on both principal and mirror server)[/u]

    The server network address "TCP://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)

    Now, this is trying to connect as the computer account for the mirror server. Configure the mirror instance to use a domain account and ensure this account has

    connect permission on the endpoint on the mirror -- Yes the account have full sysadmin permissions

    a login created on the principal --yes

    connect permission on the endpoint on the principal granted to the login created above --yes

    Please help me how to resolve this issue.

    So as i understand we need to set up both service accounts as same on both boxes.

    Please correct me if I am wrong

    SQL server DBA

  • July 21, 2015 at 12:45 pm

    #1813944

    Please give me your inputs/comments on this issue.

    Thanks for understanding!!

    SQL server DBA

  • July 21, 2015 at 1:44 pm

    #1813955

    I am really out of ideas,I might be missing something on Mirror server,please kindly help me on this issue.

    Thanks for understanding!!

    Thanks in advance!!

    SQL server DBA

  • July 22, 2015 at 3:15 am

    #1814033

    Can you Please give me your suggestions/comments on this.Thanks in advance!!

    SQL server DBA

  • July 22, 2015 at 3:17 am

    #1814034

    Database admin(DBA) (7/21/2015)

    Yes the account have full sysadmin permissions

    Which account

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

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

  • July 22, 2015 at 3:21 am

    #1814035

    Certificated Domain account.

    SQL server DBA

  • July 22, 2015 at 3:31 am

    #1814037

    FYI....Script End point below.

    CREATE ENDPOINT [Mirroring]

    STATE=STARTED

    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE

    , ENCRYPTION = REQUIRED ALGORITHM RC4)

    GO

    SQL server DBA

Viewing 12 posts - 1 through 11 (of 11 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy