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

• Edited by AgilityJeffG Monday, June 29, 2015 1:49 AM

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

• Edited by Alberto MorilloMVP, Moderator Monday, June 29, 2015 2:51 AM
• Proposed as answer by Shanky_621MVP, Moderator Monday, June 29, 2015 4:29 AM
• Marked as answer by Lydia ZhangMicrosoft contingent staff, Moderator Monday, July 06, 2015 8:16 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.

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?

• Edited by AgilityJeffG 2 hours 54 minutes ago

Here's one promising article I found after a quick search: http://serverfault.com/questions/330876/permissions-to-create-an-spn

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?

• Edited by AgilityJeffG Monday, July 13, 2015 4:12 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?