July 21, 2015 at 6:20 am
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
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
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
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
Database admin(DBA) (7/21/2015)
MessageDatabase 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
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
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
Please give me your inputs/comments on this issue.
Thanks for understanding!!
SQL server DBA
July 21, 2015 at 1:44 pm
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
Can you Please give me your suggestions/comments on this.Thanks in advance!!
SQL server DBA
July 22, 2015 at 3:17 am
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
Certificated Domain account.
SQL server DBA
July 22, 2015 at 3:31 am
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