Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

What credentials are used for mirroring connections between partners? Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2012 2:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:08 AM
Points: 64, Visits: 315
I know when I configure security for a new mirror, the wizard prompts me for credentials to connect to the mirror and witness. I don't think those credentials are used for the actual mirroring communications. What account is used for that?
Post #1379025
Posted Tuesday, October 30, 2012 8:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:39 PM
Points: 21, Visits: 563
Hi,

Preferably use domain service account on all 3 servers.
Post #1379134
Posted Wednesday, October 31, 2012 2:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:08 AM
Points: 5,203, Visits: 11,157
scogeb (10/30/2012)
I know when I configure security for a new mirror, the wizard prompts me for credentials to connect to the mirror and witness. I don't think those credentials are used for the actual mirroring communications. What account is used for that?

Early on in the wizard, you will be prompted to connect and this account should have permissions to create and manage users and grant permissions.

Later, the wizard prompts for the actual accounts that the services run under, these accounts are granted connect permission to the database mirroring endpoints, this is done under the account specified above.


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1379203
Posted Wednesday, October 31, 2012 11:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:08 AM
Points: 64, Visits: 315
Perry Whittle (10/31/2012)Early on in the wizard, you will be prompted to connect and this account should have permissions to create and manage users and grant permissions.

Later, the wizard prompts for the actual accounts that the services run under, these accounts are granted connect permission to the database mirroring endpoints, this is done under the account specified above.


So what account is used if you leave the textboxes empty on the Service Accounts page of the wizard?
Post #1379452
Posted Wednesday, October 31, 2012 11:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:08 AM
Points: 64, Visits: 315
On a side note, how can I determine which accounts have connect permissions to the endpoint?
Post #1379457
Posted Friday, November 02, 2012 8:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:08 AM
Points: 64, Visits: 315
Ok, after some testing, it looks like if you leave those 3 textboxes empty, it uses the account that the SQL Service is started by. Can anyone confirm? I've been searching MS knowledgebase and am unable to find that bit of info.
Post #1380413
Posted Friday, November 02, 2012 1:18 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:08 AM
Points: 64, Visits: 315
Well, from what I can tell, Microsoft really wants to make it difficult to determine what credentials are used for the mirroring connections. Even in sys.dm_exec_connections the session_id is null so I can't see which user is actually connecting. Does anyone know where this information can be found?
Post #1380567
Posted Friday, November 02, 2012 1:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:08 AM
Points: 5,203, Visits: 11,157
The mirroring wizard states

Database Mirroring Wizard
For SQL server accounts in the same domain or trusted domains, specify the service accounts below. If the accounts are non-domain accounts or the accounts are in untrusted domains, leave the boxes empty.


In my scenario i have the following

Instance=HANODE1\Principal
using service account MyDomain\hanode1-sql

Instance=HANODE2\Mirror
using service account MyDomain\hanode2-sql

After running the wizard, supplying the instance names and creating a mirror session i use the following to query endpoint connection details
select sp.name, spe.permission_name 
from sys.server_principals sp inner join
sys.server_permissions spe
on sp.principal_id = spe.grantee_principal_id
where spe.grantee_principal_id > 4 and sp.type <> 'R'
and spe.class_desc = 'endpoint'

I get the following results

Against Principal

Name                       Permission_name
MyDomain\hanode2-sql CONNECT

Against the Mirror

Name                       Permission_name
MyDomain\hanode1-sql CONNECT



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

"Ya can't make an omelette without breaking just a few eggs"
Post #1380573
Posted Friday, November 02, 2012 2:05 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:08 AM
Points: 64, Visits: 315
That is intersting. When I run that query, I get no results. When I take out the where clause, I see that the only class_desc of Endpoint I have are principal_id = 2 and name of public. Mirroring is working great though.
Post #1380582
Posted Friday, November 02, 2012 2:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:08 AM
Points: 5,203, Visits: 11,157
scogeb (11/2/2012)
That is intersting. When I run that query, I get no results. When I take out the where clause, I see that the only class_desc of Endpoint I have are principal_id = 2 and name of public. Mirroring is working great though.


what does this return

select SUSER_NAME(principal_id), name
from sys.database_mirroring_endpoints



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

"Ya can't make an omelette without breaking just a few eggs"
Post #1380589
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse