October 30, 2012 at 2:28 pm
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?
October 30, 2012 at 8:13 pm
Hi,
Preferably use domain service account on all 3 servers.
October 31, 2012 at 2:51 am
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" 😉
October 31, 2012 at 11:13 am
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?
October 31, 2012 at 11:20 am
On a side note, how can I determine which accounts have connect permissions to the endpoint?
November 2, 2012 at 8:39 am
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.
November 2, 2012 at 1:18 pm
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?
November 2, 2012 at 1:26 pm
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" 😉
November 2, 2012 at 2:05 pm
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.
November 2, 2012 at 2:25 pm
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" 😉
November 2, 2012 at 3:36 pm
While the accounts you supply in the GUI will be used for the actual mirroring transport, the 'owner' of endpoint(s) usually becomes the user that set up the mirror.
It is best to set up mirroring with a script. That way you can control ALL aspects of the security related to the setup.
Alternately you can go in after the fact and change the owner of the endpoint(s).
November 5, 2012 at 7:48 am
Perry Whittle (11/2/2012)
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
That returns the user that was used to connect to the pincipal, mirror and witness when mirroring was set up using the wizard. Per arnipetursson, it looks like that user becomes the "owner" of the endpoint.
But what I'd really like to know is what credentials, or what user is connecting the 3 instances? I'm guessing it is the user the SQL Service is running under if you leave those 3 text boxes blank, but I have not found a way to prove that yet or a way to change it. Anyone have any other ideas?
Even looking at sys.dm_exec_sessions, I don't see a login_name or host_name for mirroring endpoint connection. Both of those values are NULL even though I know mirroring is working and data is being transfered. Does anyone have any ideas of how I could see that mirroring connection information?
November 5, 2012 at 8:22 am
scogeb (11/5/2012)
That returns the user that was used to connect to the pincipal, mirror and witness when mirroring was set up using the wizard.
Yes, that's what I'm expecting 😉
Are the SQL server services all running under the same account?
Just out of interest, the wizard directs you to enter the service account details for machines in a domain, why aren't you specifying them?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 5, 2012 at 9:14 am
Perry Whittle (11/5/2012)
Yes, that's what I'm expecting 😉
Are the SQL server services all running under the same account?
Just out of interest, the wizard directs you to enter the service account details for machines in a domain, why aren't you specifying them?
Yes, all in the same domain, all 3 running under the same account.
That is how "they did it here". The next one I set up, I'm going to try putting in the service accounts and see what happens. I just find it odd that it's so difficult to find out what credentials are being used for mirroring. I mean, if you come into a new environment where mirroring is already set up on a bunch of servers, how can you find out what user is being used for mirroring?
November 5, 2012 at 1:21 pm
OK, just tested it myself. If your mirror instances all run as the same service account leaving the boxes blank is fine, you won't see any GRANTs as the service account automatically has access to the endpoint.
When following security best practice and using a separate account for each service you must specify them or else mirroring will not start after the endpoints have been created. See above for my scenario using separate service accounts and the resulting endpoint information
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply