April 12, 2019 at 10:30 pm
Recently I updated some virtual accounts to AD accounts and in some cases (not all) i ran into the login failure:
The target principal name is incorrect. Cannot generate SSPI context.
So we deleted the SPN's that were in AD under the computer account and recreated them under the service account.
On another server i now get the error at startup that it cant create the SPN but whatever i can still login.
Now i'm looking at the auth_scheme on 38 servers and in 8 cases its Kerberos and the other 30 are NTLM.
My question is what should it be? Why in one case am i not able to login until the SPN's are fixed but in other cases it doesn't seem to matter?
April 14, 2019 at 7:10 pm
It doesn't necessarily need to be one or the other. Kerberos would be preferred but the connections should fall back to NTLM if Kerberos fails. If on start up it can't created the SPN, the service account likely doesn't have the permissions to do so. And if it attempted to create the SPNs after they were already created it could be that the SPNs aren't configured correctly. So a few different issues. And yes, it can be a painful headache. At this point, you might want to try the Kerberos Configuration Manager for SQL Server- if nothing else to check the configurations
https://www.microsoft.com/en-us/download/details.aspx?id=39046
Sue
April 16, 2019 at 7:16 pm
I've been running setspn -q and -l to see what we have.
I'll install the kerberos config mgr.
Thanks for the info!
April 17, 2019 at 3:00 pm
hi
NTLM will be used for local connections via shared memory
KERBEROS is for TCP connections
at least I haven't seen the opposite for years
check:
select net_transport,auth_scheme,count(1) [sessions]
from sys.dm_exec_connections
group by net_transport,auth_scheme
April 17, 2019 at 4:48 pm
if there are no SPNs it will always be NTLM regardless of how you connect, local or remote.
You only get a kerb connection if a valid SPN exists.
Right now when i connect to 40 servers and check auth_scheme on all of them i only get Kerberos on about 10 of them. Those 10 all have SPNs setup for the service account or use Local system accounts (those also have SPNs that get created automatically) and the other 30 are NTLM.
After being hip deep in this SPN Kerberos stuff i'm finally understanding it. I even solved the double hop issue today. Unfortunately when you impersonate it wont use (current security context) you still need to map the user. Ugh
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply