SQL Server Mirroring Issue

  • Hello,
    I have SQL server 2008 R2 Enterprise edition 64 bit in Principle and Mirror Server and SQL Server 2008 R2 Express edition(64bit) in witness server and Operating system is windows server 2008 R2 SP1 in all three servers.
    I am trying mirroring for the first time, I have created endpoints in All three servers i.e. Principle, Mirror and Witness servers with TransactSQL query.
    And set the endpoints started.
    I have taken the full backup and Transaction Logs backup of DB from Principle server and restored both to the Mirror server with "No Recovery Option".
    I started configuring mirroring and  my mirroring setup connected to all the endpoints, but when I am starting the mirroring it is giving me the error in Principle server:
    The server network address  "TCP://server.domain.com:5023 can not be reached or does not exist. check the network  address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)
    While in Principle server event logs I am getting Event Id 1474:
    Database mirroring connection error 4 'An error occurred while receiving data '10054(An existing connection was forcibly closed by the remote host)'.'for TCP://server.domain.com:5023
    In mirror server event logs I am getting:
    Database mirroring Login attempt by user domain/user failed with error: connection handshake failed. The login does not have CONNECT permission on the endpoint, STATE84
    While the User I am trying to access with is domain user with administrative privilege in active directory and sysadmin privilege in all three SQL server instances.
    For Information: I have All 3 protocols Enabled in all 3 servers i.e. Shared Memory, Named Pipes and TCP/IP.
    And I have SQL Server and SQL Server Browser services running in all the three Servers.
    I have ports open in my firewall for mirroring is 5022(principle), 5023(Mirror) , 5024(Witness) and 1433(TCP and UDP) between all three servers.
    When I am trying to connect Mirror and witness from Principle Server it is connecting perfectly from SSMS, when I am connecting Principle and Mirror from Witness server SSMS it's also connecting perfectly, but when I am trying to connect Principle and Witness from Mirror it is not connecting and giving me the error: SQL Network Interfaces, error:26-Error locating Server/Instance Specified
    When I am checking the traffic in Firewall  firewall is blocking 1434/UDP port between mirror  and  witness server. But we haven't opened 1434 in any of my servers, while Principle and mirror and Principle and witness are connecting perfectly.
    Please someone help.
    I am also attaching the snapshots for the errors.
    Sorry for the big post but i thought it will help pinpoint the issue

  • Can u check this without witness ? to narrow down to any issue with the witness .  Also , make sure you use the same user (preferably , AD account) at both the primary and secondary SQL servers as service accounts  and they have appropriate permissions.
  • Arsh - Tuesday, September 19, 2017 1:30 AM

    Can u check this without witness ? to narrow down to any issue with the witness .  Also , make sure you use the same user (preferably , AD account) at both the primary and secondary SQL servers as service accounts  and they have appropriate permissions.

    Thanks for the reply, I tried starting Mirroring without Witness Server also but the same error occurred in Principle and Mirror Server.
    And I am using same Domain User in all 3 servers and in all 3 SQL server Instances Domain User is assigned as SYSADMIN.
    I am getting error  in Mirror server as "Handshake Failed, the login does not have connect permission on the endpoint. State 84"
    But when I am trying to grant connect permission on the endpoint to the Domain User it sends the error "cannot grant, deny or resolve permission to sa, dbo, entity owner, information schema, sys, or yourself.
    I am stuck here badly.

  • Do you yourself have sufficient permissions?

  • Beatrix Kiddo - Tuesday, September 19, 2017 3:10 AM

    Do you yourself have sufficient permissions?

    Yes the account is member of Administrator group in active directory and SYSADMIN in SQL Server

  • rohitsinghchauhan71 - Monday, September 18, 2017 11:59 PM

    Hello,
    I have SQL server 2008 R2 Enterprise edition 64 bit in Principle and Mirror Server and SQL Server 2008 R2 Express edition(64bit) in witness server and Operating system is windows server 2008 R2 SP1 in all three servers.
    I am trying mirroring for the first time, I have created endpoints in All three servers i.e. Principle, Mirror and Witness servers with TransactSQL query.
    And set the endpoints started.
    I have taken the full backup and Transaction Logs backup of DB from Principle server and restored both to the Mirror server with "No Recovery Option".
    I started configuring mirroring and  my mirroring setup connected to all the endpoints, but when I am starting the mirroring it is giving me the error in Principle server:
    The server network address  "TCP://server.domain.com:5023 can not be reached or does not exist. check the network  address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)
    While in Principle server event logs I am getting Event Id 1474:
    Database mirroring connection error 4 'An error occurred while receiving data '10054(An existing connection was forcibly closed by the remote host)'.'for TCP://server.domain.com:5023
    In mirror server event logs I am getting:
    Database mirroring Login attempt by user domain/user failed with error: connection handshake failed. The login does not have CONNECT permission on the endpoint, STATE84
    While the User I am trying to access with is domain user with administrative privilege in active directory and sysadmin privilege in all three SQL server instances.
    For Information: I have All 3 protocols Enabled in all 3 servers i.e. Shared Memory, Named Pipes and TCP/IP.
    And I have SQL Server and SQL Server Browser services running in all the three Servers.
    I have ports open in my firewall for mirroring is 5022(principle), 5023(Mirror) , 5024(Witness) and 1433(TCP and UDP) between all three servers.
    When I am trying to connect Mirror and witness from Principle Server it is connecting perfectly from SSMS, when I am connecting Principle and Mirror from Witness server SSMS it's also connecting perfectly, but when I am trying to connect Principle and Witness from Mirror it is not connecting and giving me the error: SQL Network Interfaces, error:26-Error locating Server/Instance Specified
    When I am checking the traffic in Firewall  firewall is blocking 1434/UDP port between mirror  and  witness server. But we haven't opened 1434 in any of my servers, while Principle and mirror and Principle and witness are connecting perfectly.
    Please someone help.
    I am also attaching the snapshots for the errors.
    Sorry for the big post but i thought it will help pinpoint the issue

    did you setup mirroring via t-sql or via the gui?
    did you receive any errors whilst setting mirroring up?

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

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

  • Perry Whittle - Wednesday, September 20, 2017 4:41 AM

    rohitsinghchauhan71 - Monday, September 18, 2017 11:59 PM

    Hello,
    I have SQL server 2008 R2 Enterprise edition 64 bit in Principle and Mirror Server and SQL Server 2008 R2 Express edition(64bit) in witness server and Operating system is windows server 2008 R2 SP1 in all three servers.
    I am trying mirroring for the first time, I have created endpoints in All three servers i.e. Principle, Mirror and Witness servers with TransactSQL query.
    And set the endpoints started.
    I have taken the full backup and Transaction Logs backup of DB from Principle server and restored both to the Mirror server with "No Recovery Option".
    I started configuring mirroring and  my mirroring setup connected to all the endpoints, but when I am starting the mirroring it is giving me the error in Principle server:
    The server network address  "TCP://server.domain.com:5023 can not be reached or does not exist. check the network  address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)
    While in Principle server event logs I am getting Event Id 1474:
    Database mirroring connection error 4 'An error occurred while receiving data '10054(An existing connection was forcibly closed by the remote host)'.'for TCP://server.domain.com:5023
    In mirror server event logs I am getting:
    Database mirroring Login attempt by user domain/user failed with error: connection handshake failed. The login does not have CONNECT permission on the endpoint, STATE84
    While the User I am trying to access with is domain user with administrative privilege in active directory and sysadmin privilege in all three SQL server instances.
    For Information: I have All 3 protocols Enabled in all 3 servers i.e. Shared Memory, Named Pipes and TCP/IP.
    And I have SQL Server and SQL Server Browser services running in all the three Servers.
    I have ports open in my firewall for mirroring is 5022(principle), 5023(Mirror) , 5024(Witness) and 1433(TCP and UDP) between all three servers.
    When I am trying to connect Mirror and witness from Principle Server it is connecting perfectly from SSMS, when I am connecting Principle and Mirror from Witness server SSMS it's also connecting perfectly, but when I am trying to connect Principle and Witness from Mirror it is not connecting and giving me the error: SQL Network Interfaces, error:26-Error locating Server/Instance Specified
    When I am checking the traffic in Firewall  firewall is blocking 1434/UDP port between mirror  and  witness server. But we haven't opened 1434 in any of my servers, while Principle and mirror and Principle and witness are connecting perfectly.
    Please someone help.
    I am also attaching the snapshots for the errors.
    Sorry for the big post but i thought it will help pinpoint the issue

    did you setup mirroring via t-sql or via the gui?
    did you receive any errors whilst setting mirroring up?

    I have set Up mirroring using GUI but created endpoints using TSQL.
    No error occurred setting up mirroring, I got error while starting the mirroring
    The server network address  "TCP://server.domain.com:5023 can not be reached or does not exist. check the network  address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)

  • rohitsinghchauhan71 - Wednesday, September 20, 2017 4:50 AM

    Perry Whittle - Wednesday, September 20, 2017 4:41 AM

    rohitsinghchauhan71 - Monday, September 18, 2017 11:59 PM

    Hello,
    I have SQL server 2008 R2 Enterprise edition 64 bit in Principle and Mirror Server and SQL Server 2008 R2 Express edition(64bit) in witness server and Operating system is windows server 2008 R2 SP1 in all three servers.
    I am trying mirroring for the first time, I have created endpoints in All three servers i.e. Principle, Mirror and Witness servers with TransactSQL query.
    And set the endpoints started.
    I have taken the full backup and Transaction Logs backup of DB from Principle server and restored both to the Mirror server with "No Recovery Option".
    I started configuring mirroring and  my mirroring setup connected to all the endpoints, but when I am starting the mirroring it is giving me the error in Principle server:
    The server network address  "TCP://server.domain.com:5023 can not be reached or does not exist. check the network  address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)
    While in Principle server event logs I am getting Event Id 1474:
    Database mirroring connection error 4 'An error occurred while receiving data '10054(An existing connection was forcibly closed by the remote host)'.'for TCP://server.domain.com:5023
    In mirror server event logs I am getting:
    Database mirroring Login attempt by user domain/user failed with error: connection handshake failed. The login does not have CONNECT permission on the endpoint, STATE84
    While the User I am trying to access with is domain user with administrative privilege in active directory and sysadmin privilege in all three SQL server instances.
    For Information: I have All 3 protocols Enabled in all 3 servers i.e. Shared Memory, Named Pipes and TCP/IP.
    And I have SQL Server and SQL Server Browser services running in all the three Servers.
    I have ports open in my firewall for mirroring is 5022(principle), 5023(Mirror) , 5024(Witness) and 1433(TCP and UDP) between all three servers.
    When I am trying to connect Mirror and witness from Principle Server it is connecting perfectly from SSMS, when I am connecting Principle and Mirror from Witness server SSMS it's also connecting perfectly, but when I am trying to connect Principle and Witness from Mirror it is not connecting and giving me the error: SQL Network Interfaces, error:26-Error locating Server/Instance Specified
    When I am checking the traffic in Firewall  firewall is blocking 1434/UDP port between mirror  and  witness server. But we haven't opened 1434 in any of my servers, while Principle and mirror and Principle and witness are connecting perfectly.
    Please someone help.
    I am also attaching the snapshots for the errors.
    Sorry for the big post but i thought it will help pinpoint the issue

    did you setup mirroring via t-sql or via the gui?
    did you receive any errors whilst setting mirroring up?

    I have set Up mirroring using GUI but created endpoints using TSQL.
    No error occurred setting up mirroring, I got error while starting the mirroring
    The server network address  "TCP://server.domain.com:5023 can not be reached or does not exist. check the network  address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)

    Post details of these queries run at each instance in the mirror session

    selectname AS EndpointName

    , endpoint_id

    , SUSER_NAME(principal_id) AS OwnerName

    , protocol_desc

    , state_desc

    , role_desc

    , is_encryption_enabled

    , connection_auth_desc

    , encryption_algorithm_desc

    from sys.database_mirroring_endpoints

    select 'Server login [' + SUSER_NAME(sp.grantee_principal_id) + '] has ' +

    sp.permission_name collate Latin1_General_CI_AS + ' permission ' +

    CASE sp.state

    WHEN 'G' THEN 'Granted'

    WHEN 'D' THEN 'Denied'

    WHEN 'R' THEN 'Revoked'

    END +

    ' on mirroring endpoint [' + dme.name + ']'

    from sys.server_permissions sp

    inner join sys.database_mirroring_endpoints dme

    on sp.major_id = dme.endpoint_id

    where class = 105

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

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

  • Perry Whittle - Wednesday, September 20, 2017 5:07 AM

    rohitsinghchauhan71 - Wednesday, September 20, 2017 4:50 AM

    Perry Whittle - Wednesday, September 20, 2017 4:41 AM

    rohitsinghchauhan71 - Monday, September 18, 2017 11:59 PM

    Hello,
    I have SQL server 2008 R2 Enterprise edition 64 bit in Principle and Mirror Server and SQL Server 2008 R2 Express edition(64bit) in witness server and Operating system is windows server 2008 R2 SP1 in all three servers.
    I am trying mirroring for the first time, I have created endpoints in All three servers i.e. Principle, Mirror and Witness servers with TransactSQL query.
    And set the endpoints started.
    I have taken the full backup and Transaction Logs backup of DB from Principle server and restored both to the Mirror server with "No Recovery Option".
    I started configuring mirroring and  my mirroring setup connected to all the endpoints, but when I am starting the mirroring it is giving me the error in Principle server:
    The server network address  "TCP://server.domain.com:5023 can not be reached or does not exist. check the network  address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)
    While in Principle server event logs I am getting Event Id 1474:
    Database mirroring connection error 4 'An error occurred while receiving data '10054(An existing connection was forcibly closed by the remote host)'.'for TCP://server.domain.com:5023
    In mirror server event logs I am getting:
    Database mirroring Login attempt by user domain/user failed with error: connection handshake failed. The login does not have CONNECT permission on the endpoint, STATE84
    While the User I am trying to access with is domain user with administrative privilege in active directory and sysadmin privilege in all three SQL server instances.
    For Information: I have All 3 protocols Enabled in all 3 servers i.e. Shared Memory, Named Pipes and TCP/IP.
    And I have SQL Server and SQL Server Browser services running in all the three Servers.
    I have ports open in my firewall for mirroring is 5022(principle), 5023(Mirror) , 5024(Witness) and 1433(TCP and UDP) between all three servers.
    When I am trying to connect Mirror and witness from Principle Server it is connecting perfectly from SSMS, when I am connecting Principle and Mirror from Witness server SSMS it's also connecting perfectly, but when I am trying to connect Principle and Witness from Mirror it is not connecting and giving me the error: SQL Network Interfaces, error:26-Error locating Server/Instance Specified
    When I am checking the traffic in Firewall  firewall is blocking 1434/UDP port between mirror  and  witness server. But we haven't opened 1434 in any of my servers, while Principle and mirror and Principle and witness are connecting perfectly.
    Please someone help.
    I am also attaching the snapshots for the errors.
    Sorry for the big post but i thought it will help pinpoint the issue

    did you setup mirroring via t-sql or via the gui?
    did you receive any errors whilst setting mirroring up?

    I have set Up mirroring using GUI but created endpoints using TSQL.
    No error occurred setting up mirroring, I got error while starting the mirroring
    The server network address  "TCP://server.domain.com:5023 can not be reached or does not exist. check the network  address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)

    Post details of these queries run at each instance in the mirror session

    selectname AS EndpointName

    , endpoint_id

    , SUSER_NAME(principal_id) AS OwnerName

    , protocol_desc

    , state_desc

    , role_desc

    , is_encryption_enabled

    , connection_auth_desc

    , encryption_algorithm_desc

    from sys.database_mirroring_endpoints

    select 'Server login [' + SUSER_NAME(sp.grantee_principal_id) + '] has ' +

    sp.permission_name collate Latin1_General_CI_AS + ' permission ' +

    CASE sp.state

    WHEN 'G' THEN 'Granted'

    WHEN 'D' THEN 'Denied'

    WHEN 'R' THEN 'Revoked'

    END +

    ' on mirroring endpoint [' + dme.name + ']'

    from sys.server_permissions sp

    inner join sys.database_mirroring_endpoints dme

    on sp.major_id = dme.endpoint_id

    where class = 105

    Hello,
    I have attached the snapshot  of result

  • rohitsinghchauhan71 - Wednesday, September 20, 2017 5:40 AM

    Perry Whittle - Wednesday, September 20, 2017 5:07 AM

    rohitsinghchauhan71 - Wednesday, September 20, 2017 4:50 AM

    Perry Whittle - Wednesday, September 20, 2017 4:41 AM

    rohitsinghchauhan71 - Monday, September 18, 2017 11:59 PM

    Hello,
    I have SQL server 2008 R2 Enterprise edition 64 bit in Principle and Mirror Server and SQL Server 2008 R2 Express edition(64bit) in witness server and Operating system is windows server 2008 R2 SP1 in all three servers.
    I am trying mirroring for the first time, I have created endpoints in All three servers i.e. Principle, Mirror and Witness servers with TransactSQL query.
    And set the endpoints started.
    I have taken the full backup and Transaction Logs backup of DB from Principle server and restored both to the Mirror server with "No Recovery Option".
    I started configuring mirroring and  my mirroring setup connected to all the endpoints, but when I am starting the mirroring it is giving me the error in Principle server:
    The server network address  "TCP://server.domain.com:5023 can not be reached or does not exist. check the network  address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)
    While in Principle server event logs I am getting Event Id 1474:
    Database mirroring connection error 4 'An error occurred while receiving data '10054(An existing connection was forcibly closed by the remote host)'.'for TCP://server.domain.com:5023
    In mirror server event logs I am getting:
    Database mirroring Login attempt by user domain/user failed with error: connection handshake failed. The login does not have CONNECT permission on the endpoint, STATE84
    While the User I am trying to access with is domain user with administrative privilege in active directory and sysadmin privilege in all three SQL server instances.
    For Information: I have All 3 protocols Enabled in all 3 servers i.e. Shared Memory, Named Pipes and TCP/IP.
    And I have SQL Server and SQL Server Browser services running in all the three Servers.
    I have ports open in my firewall for mirroring is 5022(principle), 5023(Mirror) , 5024(Witness) and 1433(TCP and UDP) between all three servers.
    When I am trying to connect Mirror and witness from Principle Server it is connecting perfectly from SSMS, when I am connecting Principle and Mirror from Witness server SSMS it's also connecting perfectly, but when I am trying to connect Principle and Witness from Mirror it is not connecting and giving me the error: SQL Network Interfaces, error:26-Error locating Server/Instance Specified
    When I am checking the traffic in Firewall  firewall is blocking 1434/UDP port between mirror  and  witness server. But we haven't opened 1434 in any of my servers, while Principle and mirror and Principle and witness are connecting perfectly.
    Please someone help.
    I am also attaching the snapshots for the errors.
    Sorry for the big post but i thought it will help pinpoint the issue

    did you setup mirroring via t-sql or via the gui?
    did you receive any errors whilst setting mirroring up?

    I have set Up mirroring using GUI but created endpoints using TSQL.
    No error occurred setting up mirroring, I got error while starting the mirroring
    The server network address  "TCP://server.domain.com:5023 can not be reached or does not exist. check the network  address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)

    Post details of these queries run at each instance in the mirror session

    selectname AS EndpointName

    , endpoint_id

    , SUSER_NAME(principal_id) AS OwnerName

    , protocol_desc

    , state_desc

    , role_desc

    , is_encryption_enabled

    , connection_auth_desc

    , encryption_algorithm_desc

    from sys.database_mirroring_endpoints

    select 'Server login [' + SUSER_NAME(sp.grantee_principal_id) + '] has ' +

    sp.permission_name collate Latin1_General_CI_AS + ' permission ' +

    CASE sp.state

    WHEN 'G' THEN 'Granted'

    WHEN 'D' THEN 'Denied'

    WHEN 'R' THEN 'Revoked'

    END +

    ' on mirroring endpoint [' + dme.name + ']'

    from sys.server_permissions sp

    inner join sys.database_mirroring_endpoints dme

    on sp.major_id = dme.endpoint_id

    where class = 105

    Hello,
    I have attached the snapshot  of result from Mirror server instance.

    Should I attach the result from Principle SQL Server Also??

    Please help.🙂

  • rohitsinghchauhan71 - Wednesday, September 20, 2017 5:41 AM

    rohitsinghchauhan71 - Wednesday, September 20, 2017 5:40 AM

    Perry Whittle - Wednesday, September 20, 2017 5:07 AM

    rohitsinghchauhan71 - Wednesday, September 20, 2017 4:50 AM

    Perry Whittle - Wednesday, September 20, 2017 4:41 AM

    rohitsinghchauhan71 - Monday, September 18, 2017 11:59 PM

    Hello,
    I have SQL server 2008 R2 Enterprise edition 64 bit in Principle and Mirror Server and SQL Server 2008 R2 Express edition(64bit) in witness server and Operating system is windows server 2008 R2 SP1 in all three servers.
    I am trying mirroring for the first time, I have created endpoints in All three servers i.e. Principle, Mirror and Witness servers with TransactSQL query.
    And set the endpoints started.
    I have taken the full backup and Transaction Logs backup of DB from Principle server and restored both to the Mirror server with "No Recovery Option".
    I started configuring mirroring and  my mirroring setup connected to all the endpoints, but when I am starting the mirroring it is giving me the error in Principle server:
    The server network address  "TCP://server.domain.com:5023 can not be reached or does not exist. check the network  address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)
    While in Principle server event logs I am getting Event Id 1474:
    Database mirroring connection error 4 'An error occurred while receiving data '10054(An existing connection was forcibly closed by the remote host)'.'for TCP://server.domain.com:5023
    In mirror server event logs I am getting:
    Database mirroring Login attempt by user domain/user failed with error: connection handshake failed. The login does not have CONNECT permission on the endpoint, STATE84
    While the User I am trying to access with is domain user with administrative privilege in active directory and sysadmin privilege in all three SQL server instances.
    For Information: I have All 3 protocols Enabled in all 3 servers i.e. Shared Memory, Named Pipes and TCP/IP.
    And I have SQL Server and SQL Server Browser services running in all the three Servers.
    I have ports open in my firewall for mirroring is 5022(principle), 5023(Mirror) , 5024(Witness) and 1433(TCP and UDP) between all three servers.
    When I am trying to connect Mirror and witness from Principle Server it is connecting perfectly from SSMS, when I am connecting Principle and Mirror from Witness server SSMS it's also connecting perfectly, but when I am trying to connect Principle and Witness from Mirror it is not connecting and giving me the error: SQL Network Interfaces, error:26-Error locating Server/Instance Specified
    When I am checking the traffic in Firewall  firewall is blocking 1434/UDP port between mirror  and  witness server. But we haven't opened 1434 in any of my servers, while Principle and mirror and Principle and witness are connecting perfectly.
    Please someone help.
    I am also attaching the snapshots for the errors.
    Sorry for the big post but i thought it will help pinpoint the issue

    did you setup mirroring via t-sql or via the gui?
    did you receive any errors whilst setting mirroring up?

    I have set Up mirroring using GUI but created endpoints using TSQL.
    No error occurred setting up mirroring, I got error while starting the mirroring
    The server network address  "TCP://server.domain.com:5023 can not be reached or does not exist. check the network  address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)

    Post details of these queries run at each instance in the mirror session

    selectname AS EndpointName

    , endpoint_id

    , SUSER_NAME(principal_id) AS OwnerName

    , protocol_desc

    , state_desc

    , role_desc

    , is_encryption_enabled

    , connection_auth_desc

    , encryption_algorithm_desc

    from sys.database_mirroring_endpoints

    select 'Server login [' + SUSER_NAME(sp.grantee_principal_id) + '] has ' +

    sp.permission_name collate Latin1_General_CI_AS + ' permission ' +

    CASE sp.state

    WHEN 'G' THEN 'Granted'

    WHEN 'D' THEN 'Denied'

    WHEN 'R' THEN 'Revoked'

    END +

    ' on mirroring endpoint [' + dme.name + ']'

    from sys.server_permissions sp

    inner join sys.database_mirroring_endpoints dme

    on sp.major_id = dme.endpoint_id

    where class = 105

    Hello,
    I have attached the snapshot  of result from Mirror server instance.

    Should I attach the result from Principle SQL Server Also??

    Please help.🙂

    Output from Principle SQL Server Instance is also Same,
    and Output from Witness server Instance is also same while role_desc is WITNESS

  • Hello,

    It was a silly mistake which created such havoc and wasted my 3 days, Actually I am having SQL server 2008 R2 Enterprise edition and SQL server Express edition both installed in my Principle and Mirror servers. In my mirror server Endpoint was redirecting to SQL Server EXPRESS Instance while the endpoint was created in SQL Server Enterprise edition Instance. I got to know about by doing port query. When I have stopped my SQL Server Express service in Mirror server the port 5023 was not listening, while when I stopped MSSQLServer service from SQL Server Configuration Manager it was still showing listening.

    That means my Endpoint is redirected to SQL Server Express edition Instance. So I stopped SQL Server Express Edition in both my Principle and Mirror Server, Dropped endpoints in all three servers, and again created Endpoints in SQL Server 2008 R2 Enterprise Edition.

    Now configured mirroring in Principle server, all instances connected and Mirroring started.

    It worked.

    Sorry for troubling you guys.

    Thanks a lot.

  • rohitsinghchauhan71 - Wednesday, September 20, 2017 11:28 PM

    That means my Endpoint is redirected to SQL Server Express edition Instance. So I stopped SQL Server Express Edition in both my Principle and Mirror Server, Dropped endpoints in all three servers, and again created Endpoints in SQL Server 2008 R2 Enterprise Edition.

    Now configured mirroring in Principle server, all instances connected and Mirroring started.

    It worked.

    Sorry for troubling you guys.

    Thanks a lot.

    There you go . When faced with errors while reattempting its best to first clean up the endpoints created in the previous attempt.. making it a clean slate...GUI will make the endpoints during configuration.. so need to do this through TSQL.

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

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