Application down after SQL Service logon account change

Question

Application down after SQL Service logon account change

iLearnSQL

Say Hey Kid

Points: 705
More actions
October 5, 2020 at 12:00 am

#3791125

Comments posted to this topic are about the item Application down after SQL Service logon account change

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 1

The conclusion is that when you are changing the SQL Service logon account to a domain account, make sure that the SPN's are registered for the respective domain account. Also, you may download tool Microsoft Kerberos Configuration Manager for SQL Server. This is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server.

No... The conclusion you should have reached is this... NEVER take it upon yourself to make untested changes to a production environment.

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

Thanks for the article.

Question, 1. how can you know the applilcation is using kerberos? In my environment, I always use domain account instead of local system account for the SQL service.But mostly I didnot register SPN. The application works.

2. ALso does that mean local account does not need register SPN, it already has it by default?

Thanks,

K Brake Grasshopper Points: 11 More actions · Answer 3

Thanks

We are working on a very similar problem. The Kerberos Configuration tool is also showing two delete actions in the script it generates. My domain admins are not willing to execute them without more information. The script has the two adds:

MSSQLSvc/servername.domainname to account domainname\accountname

MSSQLSvc/servername.domainname:1433 to account domainname\accountname

Then two deletes:

MSSQLSvc/servername.domainname from account domainname\servername$

MSSQLSvc/servername.domainname:1433 from account domainname\servername$

Their concern is that the deletions may affect other servers which also use that domain account to run SQL service. We opened a support issue with Microsoft and they couldn't tell us that the deletes would not affect other servers.

Does anyone know what the entries are with servername appended by $?

When I install SQL Server, remote windows authentication always works and I normally use the domain account to run the service. I am not a domain admin and I have not had to get them to run adds after an install. So now I am not sure why that works from the install but not if we change the account later.

UPDATE. We never had SPN registration in our build plans hence all of our servers use NTLM. Turns out changing the service account to a domain account and not manually registering the SPN looks to have broken NTLM for remote connections. From what I can gather since connections always try kerberos first and take NTLM when they don't find the SPN everything is usually fine. When the connection finds a invalid SPN then depending on the application NTLM can fail as well. This was a single purpose SQL Server to host our monitoring repositories. Our work around was to change the one app which connected remotely to use SQL authentication.

This reply was modified 3 years, 6 months ago by K Brake. Reason: Update

james.of.rivendell SSC-Addicted Points: 425 More actions · Answer 4

Changing the account used to run sql service should be a change in itself. The correct course of action is to inform the management orsecurity team your finding during the maintenance window and plan and test the account change for a later time.