locked
SQLServer Service Account Permissions In AlwaysOn Configuration RRS feed

  • Question

  • Hello,

    Many of the articles are not clear about permissions to be given to the SQLServer service account which will have admin rights to the SQL server instances on a two node Win2k12 failover cluster . The AO in question is based on SQL2k12 Ent version. My question are as follows:

    a. Whether we need to add the concerned SQL server service account to the local administrators group on both the nodes?

    b. Do we need to give "Create Computer Objects" and "Read All Properties permissions" inside AD Users and Computers for that sql server service account or this permission has to be given to cluster name account?

    c. What other permissions we need to grant in addition to having sys admin rights to the db engine?

    Will appreciate your early response.

    Thanks.

    Victor


    Victor

    Tuesday, April 18, 2017 10:42 PM

Answers

  • The Listener name will create a new virtual computer object (VCO) in Active Directory that is totally different from your Windows Server Failover Cluster (commonly known as the Cluster Name Object or CNO). So, you will have two DNS entries - one for the CNO and one for the Listener/VCO. Client applications need to connect to SQL Server by using either the Listener name/VCO or the SQL Server instance name - not the CNO. Listener name is preferred since the redirection to the primary replica is transparent.

    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    • Proposed as answer by Davy_Wang Tuesday, April 25, 2017 9:22 AM
    • Marked as answer by vr123 Tuesday, April 25, 2017 1:30 PM
    Wednesday, April 19, 2017 8:56 PM

All replies

  • 1) Yes, for the install.

    2) a domain user should have rights to create the cluster and register it on the AD. If your AD is an upgrade from Windows 2003 you may need to prestage the accounts.

    https://technet.microsoft.com/en-us/library/dn466519%28v=ws.11%29.aspx?f=255&MSPPError=-2147217396

    3) no, but you use the term AO which could refer to an Always On Availability group. If this is the case many administrators use a domain account to run the sql server services under. If you want to run it under a virtual account (local service) you will need to grant the machine accounts to the hadr_endpoint. ie on Node 1 do this:

    GRANT CONNECT ON ENDPOINT::[HADR_endpoint] TO Node2$

    do the reverse on Node2.


    Wednesday, April 19, 2017 1:05 AM
  • Thank you.

    Victor


    Victor

    Wednesday, April 19, 2017 2:54 AM
  • Hi Victor,

    Any update on the issue, was the issue resolved? If the issue has been resolved, please mark the corresponding replies as answer as it would benefit others when they are reading this thread. If not, could you please provide more information so we can have a better understanding about the issue?

    If you have any other questions, please let me know.

    Regards,
    Davy

    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Wednesday, April 19, 2017 7:20 AM
  • I do have one more related question. Is the Listener DNS name and ip address are the same as with the VNN or the cluster name? I think, both should be different. The client connections to the SQL servers can use the VNN/clustername or the Listener DNS name?

    Thanks for help!

    Victor


    Victor

    Wednesday, April 19, 2017 8:41 PM
  • vnn=listener. The windows cluster will have a name, but you will not be using it within sql server.
    Wednesday, April 19, 2017 8:49 PM
  • The Listener name will create a new virtual computer object (VCO) in Active Directory that is totally different from your Windows Server Failover Cluster (commonly known as the Cluster Name Object or CNO). So, you will have two DNS entries - one for the CNO and one for the Listener/VCO. Client applications need to connect to SQL Server by using either the Listener name/VCO or the SQL Server instance name - not the CNO. Listener name is preferred since the redirection to the primary replica is transparent.

    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    • Proposed as answer by Davy_Wang Tuesday, April 25, 2017 9:22 AM
    • Marked as answer by vr123 Tuesday, April 25, 2017 1:30 PM
    Wednesday, April 19, 2017 8:56 PM
  • Perfect. Thanks and this thread can be treated as closed.

    Victor


    Victor

    Wednesday, April 19, 2017 9:22 PM
  • Oops....sorry, should have asked one last question. If the two AO nodes are in two different subnets (may be two different geographical locations), which subnet's ip address should we assign?

    Also, we have one listener per two node cluster right?

    Thanks.

    Victor


    Victor

    Wednesday, April 19, 2017 9:28 PM
  • Forgot to add one more question: Once we create a new Listener, do we need to add it inside the Cluster Manager gui or does the wizard inside the gui creates it?

    Thanks.

    Victor


    Victor

    Wednesday, April 19, 2017 9:41 PM
  • You use SQL Server Management Studio to create your listener.

    You should not be using failover cluster manager to manage your AG at all.

    • Proposed as answer by Davy_Wang Tuesday, April 25, 2017 9:23 AM
    Thursday, April 20, 2017 12:56 AM
  • Hilary,

    Thanks for your input and yes, I am not going to be using CM for any failover. Can you please answer this question:

    If the two AO nodes are in two different subnets (may be two different geographical locations), which subnet's ip address should we assign? As long as the address is routable from the concerned sql server nodes, it should be OK right?

    Victor


    Victor

    Thursday, April 20, 2017 1:32 AM