locked
Best practices SQL domain accounts and permissions RRS feed

  • Question

  • Is it best practice to run the MSSQL2012 service using an active directory domain account instead of the default (local computer service account)?

    My understanding of Windows Server security and general security is that I should give the AD user only the exact permissions necessary (instead of just giving it blanket permissions like putting it into the Domain Admins Security Group). What are the actual permissions required for said AD user account for running the service and SQL to be able to correctly run?

    One of the errors I keep getting is that the servicenameprotocol isn't registering, I've been through all the KB's and none of the answers solve my problem (I've done all the manual setSPN.exe commands, I've given permission on the 'read serviceprincipalname' and 'write serviceprincipalname' fields). When I give the service account Domain Admin permissions, the error goes away therefore I know that this is a permissions issue for the AD user account.

    Cheers,

    Jeff



    Monday, June 29, 2015 1:36 AM

Answers

  • Hello,

    If the service account does not have permissions you can try a manual registration as explained on the following article:

    https://msdn.microsoft.com/en-us/library/ms191153(v=sql.110).aspx


    About the permissions required to register a SPN, please read the following resource:

    https://msdn.microsoft.com/en-us/library/ms191153(v=sql.110).aspx#Permissions


    Usually we choose a domain account as SQL Server service account if the SQL Server needs access to domain resources.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    Monday, June 29, 2015 2:50 AM
  • In the spirit of being as restrictive as possible, I suggest you don't assign any permissions at all for this account. The SQL Server installation program (or if you change it after installation: SQL Server Configuration Manager) will take care of this for you.

    Alberto already covered the "register SPN" topic.

    I usually use Virtual Accounts (if you are on SQL 2012 at least) since you don't even have to create an account. I.e., local accounts.

    If the account *need* domain privileges (like permissions on a share), then of course you use a domain account. In my experience, it is pretty rare that the database engine need such permissions, while it is more common that Agent need domain privileges.


    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, June 29, 2015 7:00 PM

All replies

  • Hello,

    If the service account does not have permissions you can try a manual registration as explained on the following article:

    https://msdn.microsoft.com/en-us/library/ms191153(v=sql.110).aspx


    About the permissions required to register a SPN, please read the following resource:

    https://msdn.microsoft.com/en-us/library/ms191153(v=sql.110).aspx#Permissions


    Usually we choose a domain account as SQL Server service account if the SQL Server needs access to domain resources.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    Monday, June 29, 2015 2:50 AM
  • In the spirit of being as restrictive as possible, I suggest you don't assign any permissions at all for this account. The SQL Server installation program (or if you change it after installation: SQL Server Configuration Manager) will take care of this for you.

    Alberto already covered the "register SPN" topic.

    I usually use Virtual Accounts (if you are on SQL 2012 at least) since you don't even have to create an account. I.e., local accounts.

    If the account *need* domain privileges (like permissions on a share), then of course you use a domain account. In my experience, it is pretty rare that the database engine need such permissions, while it is more common that Agent need domain privileges.


    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, June 29, 2015 7:00 PM
  • Hi Alberto, Tibor,

    I definitely need to use a domain account. Virtual accounts or domain administrator permissions are not suitable.

    If I add the account to the Domain Admins group, setspn.exe allows for manual registration. Automatic registration of the SPN when starting the MSSQLSERVER service also works.

    When I remove the account from the Domain Admins group, this all stops working.

    What permission are used in the Domain Admins group that allow for SPN registration? Why can't I just add these permissions to a specific group and make the account a member?


    Monday, July 13, 2015 4:06 AM
  • Here's one promising article I found after a quick search: http://serverfault.com/questions/330876/permissions-to-create-an-spn

    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, July 13, 2015 6:48 AM
  • Hi Tibor,

    I have added the domain account as a member of 'spngroup'

    In active directory, I have edited the security permissions on spngroup to allow Validated Write to Service Principle Name for all descendant computer objects.

    I have also granted permissions to read and write servicePrincipleNames properties for all descendant computer objects.

    Regardless, I still have this issue.

    note: When selecting the 'apply to' field in the advanced security settings windows, descendant user objects does not have options to grant these 3 permissions, they only show up if the 'apply to' field is descendant computer objects.

    This indicates to me that what I want is impossible under the current system. If I don't grant the user account admin permissions, then I must use a local service account on the server.

    Thoughts?


    Tuesday, July 14, 2015 12:01 AM
  • Sorry, Jeff, but I'm not enough of an AD persons to comment further. I suggest you try in an appropriate Windows/AD group...

    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, July 14, 2015 5:16 PM
  • Thanks Tibor, I have discovered that the solution to my problem is a Managed Service Account.


    Tuesday, July 14, 2015 11:18 PM