SQL Server Mirroring Issue

Question

SQL Server Mirroring Issue

rohitsinghchauhan71

SSC-Addicted

Points: 403
More actions
September 18, 2017 at 11:59 pm

#328404

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

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

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

Arsh SSCertifiable Points: 6354 More actions · Answer 1

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.

rohitsinghchauhan71 SSC-Addicted Points: 403 More actions · Answer 2

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.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

Beatrix Kiddo

SSC-Dedicated

Points: 32407

September 19, 2017 at 3:10 am

#1959912

Do you yourself have sufficient permissions?

rohitsinghchauhan71 SSC-Addicted Points: 403 More actions · Answer 4

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

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" 😉

rohitsinghchauhan71 SSC-Addicted Points: 403 More actions · Answer 6

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)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

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" 😉

rohitsinghchauhan71 SSC-Addicted Points: 403 More actions · Answer 8

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 SSC-Addicted Points: 403 More actions · Answer 9

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 SSC-Addicted Points: 403 More actions · Answer 10

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

rohitsinghchauhan71 SSC-Addicted Points: 403 More actions · Answer 11

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.

Arsh SSCertifiable Points: 6354 More actions · Answer 12

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.