February 2, 2011 at 8:05 am
OK- I have a "usual" domain account I use for the SQL Service, and it has the domain right needed to create the SPN- never have any problems- kerberos authentication works.
for one particular application, they insist on using another account for the service which does not have createserviceprinciplename and readserviceprinciplename rights.
So, it should work and fall back to NTLM authentication- right? but it doesnt. Instead any attempts to connect with windows authentication fail with errors like "Cannot generate SSPI context".
Why is NTLM authentication not working?
Thanks for any info, tips, advice, etc.
February 2, 2011 at 8:12 am
In the case a Account is locked in windows i got this error "Cannot generate SSPI context" Maybe is your case
February 2, 2011 at 8:24 am
Not locked, but thanks...
February 2, 2011 at 8:29 am
In Active Directory ?
February 2, 2011 at 8:31 am
Yes- checked AD.
The problem is that it is not falling back to NTLM authentication. or NT LM is not working for some reason.
February 2, 2011 at 8:37 am
here is another clue: when I use fully qualified domain name (e.g. servername.domain.com) I can connect without error. When I use the shortname (servername) I get "can no generate sspi context" error message.
February 2, 2011 at 8:40 am
You might need to flush the credential cache. This link has some good information about troubleshooting NTML and Kerberos:
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
February 2, 2011 at 9:16 am
Just rebooted all three servers to clear the cache... same result. That article is good- I've seen it before- but it deals mostly with Kerberos problems. In this case, I'm acknowledging that Kerberos isnt working and I'm ok w/ that. I want it to fall back to NTLM and work, but it's not.
Here's something even weirder: on two of the three servers, after reboot, I tried connecting with just servername,and at first got the error. I then went back to FQDN and connected successfully. After that I could connect with short name... on the third server, just always gives "sspi context" error.
I'm on SQL 2005 x64 SP4. Gonna try applying CU1 and see if maybe I'm looking at a bug.
February 2, 2011 at 10:16 am
In the SQL Server error log, do you get an error message with regards to a failed logins?
What SPNs currently exist on the SQL server in question?
February 2, 2011 at 11:15 am
I figured it out. Here is what happened:
The app folks changed the SQL Service account, but did so using "services.msc" not SQL configuration manager. I believe that caused an invaild SPN to remain registered from the original acct which did have the required permissions to create the SPN.
I resovled the problem by changing the service account back to the original account, rebooting the server, then changing to the new account using sql config mgr. After that, windows authentication works w/ NTLM.
February 2, 2011 at 12:02 pm
Did they provide any reason for their modification of a service account ?
This is actually one of the ways many devs / app managers abuse their given authority and break their own glasses by doing stuff they are not supposed to do.
And then complain they aren't being granted high level authority.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 2, 2011 at 12:57 pm
This application is a little different that our normal support model. The application group is a bit more knowledgeable and works closely with the database. They accept some additional responsbility for the database and so we give them more leeway than is normal.
Their offical reason is that "all there testing and development was done using that specific account", but in reality, there should be no difference whatsoever.
They should have just left the account which is set up to dynamically create SPNs and they would have had no problem... if they REALLY needed that account they could have asked me to change the account and I would have done it for them the right way.
February 2, 2011 at 1:57 pm
Indeed, that's the way it should be.
There shouldn't be a need to alter a service account because of an application behaviour.
IMO that would mean their app needs sysadmin, which is certainly a huge nono !
Double check they didn't open xp_cmdshell, clr, .... unless your db admins team agreed to.
We've also had such a situation. It can be a hell to figure out what else they messed up and to revert the situation.
When doing so, they will blame you for their stuff not working because you closed a hole.
They should have provided an overview of their needs, so you as a firefighter can take all that into account in case you have to rebuild this server due to a crash.
You should be able to document all exceptions on a per instance bases, or take a inventory on a regular basis.
If this is the first time, you can call it "sh*t happens, we'll correct it, but don't let it happen again" and be constructive to get it all up and running smoothly.
This will certainly pay off in the long run.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 2, 2011 at 1:59 pm
NJ-DBA (2/2/2011)
I figured it out. Here is what happened:The app folks changed the SQL Service account, but did so using "services.msc" not SQL configuration manager. I believe that caused an invaild SPN to remain registered from the original acct which did have the required permissions to create the SPN.
I resovled the problem by changing the service account back to the original account, rebooting the server, then changing to the new account using sql config mgr. After that, windows authentication works w/ NTLM.
Actually, what probably happened is that they changed from the local system account (which has permissions to create SPN records) to the domain account which does not. This would leave the now invalid SPN record which causes the error.
One thing you should realize is that the FQDN and server name entries in SPN are different records. If you want Kerberos to work for all types of access, you need to create an SPN record for each.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 2, 2011 at 2:13 pm
No- it was never local system. It was first a domain account with the correct rights to dynamically create SPNs, then another domain account which did not have those rights. Basically the same scenario as changing from LocalSystem, but with a properly priveledged domain account.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply