September 25, 2014 at 8:48 am
Use this to check:-
SELECT
e.name AS [Endpoint Name],
p.STATE AS [State],
CONVERT(SYSNAME, SUSER_NAME(p.grantor_principal_id)) AS [Grantor],
p.TYPE AS [Permission],
CONVERT(SYSNAME, SUSER_NAME(p.grantee_principal_id)) AS [Grantee]
FROM
sys.server_permissions p
INNER JOIN
sys.endpoints e ON p.major_id = e.endpoint_id
WHERE
e.name = 'Mirroring'
GO
September 25, 2014 at 8:50 am
Can you try with IP and port# instead of serverne, port
I had similar issue and using IP worked.... There was some issue with FQDN.
Regards,
SQLisAwe5oMe.
September 25, 2014 at 8:52 am
Ok, this might be the problem. If I take away the where clause I get this:
Endpoint Name State Grantor Permission Grantee
TSQL Local Machine G sa CO public
TSQL Named Pipes G sa CO public
TSQL Default TCP G sa CO public
TSQL Default VIA G sa CO public
September 25, 2014 at 8:57 am
SQLisAwE5OmE: Yes I began using only ip address and port number. Then I tried the FQDN to see if that was the problem.
September 25, 2014 at 8:57 am
So there's not an endpoint setup. Here's the code to create one:-
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5023 )
FOR DATABASE_MIRRORING (
AUTHENTICATION = WINDOWS KERBEROS,
ENCRYPTION = SUPPORTED,
ROLE=ALL);
GO
September 25, 2014 at 9:05 am
There should be an endpoint installed.
SELECT * FROM sys.tcp_endpoints where type_desc = 'DATABASE_MIRRORING'
This query returns this set:
name endpoint_id principal_id protocol protocol_desc type type_desc state state_desc is_admin_endpoint port is_dynamic_port ip_address
Mirroring 65541 262 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 5023 0 NULL
September 25, 2014 at 9:52 am
OK, so the endpoint is there but the user doesn't have permissions to connect
September 26, 2014 at 3:33 am
This command: GRANT CONNECT ON ENDPOINT....
results in the following error: Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Should I use a domain user or local sql server user?
I've tried both, and always get the same error.
September 26, 2014 at 3:56 am
oskargunn (9/26/2014)
This command: GRANT CONNECT ON ENDPOINT....results in the following error: Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Should I use a domain user or local sql server user?
I've tried both, and always get the same error.
You must use a domain account for the sql server services, grant the service account connect to the endpoint.
At this point i would recommend dropping and re creating the endpoiint from the beginning
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 26, 2014 at 4:01 am
The primary site is running on Windows 2008 and the Mirror site is running on Windows 2012.
Could it be that that is an issue ?
September 26, 2014 at 4:07 am
Try Perry's recommendation and see if that allows the mirroring to be setup.
The Windows version should not be an issue.
September 26, 2014 at 4:10 am
On the Principal drop the endpoint
--drop endpoint
DROP ENDPOINT PrincipalEndpointName
On the principal, create the windows login for the mirror instances svc account
--Create login
CREATE LOGIN [yourdomain\themirrorsvcuser] FROM WINDOWS
Create the endpoint
CREATE ENDPOINT PrincipalEndPoint
STATE=STARTED
AS TCP ( LISTENER_PORT = 5023 )
FOR DATABASE_MIRRORING
(AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = PARTNER) -- or ALL )
Now grant connect to the mirror svc account you created above
GRANT CONNECT on ENDPOINT::PrincipalEndPoint TO [UKTRADING\Mirrorsvc_User]
Once done, do the reverse.
Create the endpoint on the mirror, add the principals svc account as a sql server login and grant i connect on the endpoint
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 5, 2014 at 6:19 am
I got the same error ,I run on sql server 2012
I acn't telnet with
telnet servername:5022
it give me an error that couldn't reach server on port 23
November 5, 2014 at 6:50 am
Correct syntax is
telnet servername 5022
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply