smtzac (10/23/2014)
I already have a service account created in Logins: SQL\SVC.---CREATE ENDPOINT <endpoint_mirroring>
STATE = STARTED
AS TCP ( LISTENER_PORT = 7023 )
FOR DATABASE_MIRRORING (ROLE=PARTNER)-----
If I run above script, I think, my window authentication will be used.
So how to write query so SQL\SVC can run on both principle and mirror server?
Should I run below after the above script?
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [SQL\SVC];
GO
Ok, let me explain it in a little more detail (i'll assume you are not using a witness, since you haven't mentioned it)
I have server A with Instance A and the SQL Server service runs as account MyDomain\InstA
I have server B with Instance B and the SQL Server service runs as account MyDomain\InstB
My endpoint creation statements would look thus
On the Principal which is Instance A, I execute
CREATE ENDPOINT [db_mirroring]
STATE = STARTED
AS TCP ( LISTENER_PORT = 7023 )
FOR DATABASE_MIRRORING (ROLE=PARTNER)
--create the mirror instance login
CREATE LOGIN [MyDomain\InstB] FROM WINDOWS
GO
--Grant connect on the endpoint
GRANT CONNECT ON Endpoint::db_mirroring TO [Mydomain\InstB]
GO
On the mirror which is Instance B, I execute
CREATE ENDPOINT [db_mirroring]
STATE = STARTED
AS TCP ( LISTENER_PORT = 7023 )
FOR DATABASE_MIRRORING (ROLE=PARTNER)
--create the mirror instance login
CREATE LOGIN [MyDomain\InstA] FROM WINDOWS
GO
--Grant connect on the endpoint
GRANT CONNECT ON Endpoint::db_mirroring TO [Mydomain\InstA]
GO
Does this make sense?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉