Score:1

Does NT SERVICE\MSSQLSERVER require Kerberos authentication if the sql instance is on the local machine?

sg flag

I had a bizarre issue today where NT SERVICE\MSSQLSERVER was being denied login as a service on a domain joined computer. I also noticed group policy was not being applied via gpupdate /force. I disconnected the computer from the domain, deleted the AD Object, re-created the AD Object, and re-joined the domain. The computer pulled policy successfully, and SQL Express worked. I know the GPO's don't have anything to do with it because I scoured through them to see if there was a policy allowing the virtual account login as a service and there was nothing of the sort. There are only three GPOs being applied now and they are not related. I'm struggling to make sense how this fixed the SQL problem. The only thing that would make sense, even though the SQL instance is local, the computer still needed a kerberos token to login as the service. When I rejoined the domain, it fixed the connection to the DC allowing authentication. Does that make sense?

Thanks folks.

cn flag
`I know the GPO's don't have anything to do with it because I scoured through them to see if there was a policy allowing the virtual account login as a service and there was nothing of the sort`. You should have checked the system security policies instead of group policy. Also you probably destroyed the information needed to assess the problem. Just because something may "not be understood" doesn't mean it is an Active Directory problem or Group Policy problem.
sg flag
@GregAskew, I agree with your philosophy. In a situation like this, what would you have checked with local security policy? Just see what was enabled? Or did you actually have something specific in mind. Thanks.
cn flag
If it can't logon the "logon as a service" or "deny logon as a service" rights should be checked. There should also be a substatus code for the logon failure.
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.