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

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

Sorry, Jeff, but I'm not enough of an AD persons to comment further. I suggest you try in an appropriate Windows/AD group...

Thanks Tibor, I have discovered that the solution to my problem is a Managed Service Account.