Can anyone get me out of SSPI / Kerberos / NTLM hell ??

This is to do with SQL Server 2005, but I think it is a more general issue.
We have two SQL Servers, but starting with bog-standard Domain accounts and
both running in Windows Authentication mode. Either account it trust for
delegation, nor are the machines, as we do not need delegation and don't
which to turn it on.
When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
connection and we can see that is it using NTLM for authentication.
When trying to attach to SQL Server #2, using TCP/IP, get an error, "Cannot
generate SSPI context". When trying to attach using Named Pipes, get a
connection and again we can see that it is using NTLM for authentication.
SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
tell) IP Security configured.
We are using Active Directory 2003, running in Native Mode.
What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL Server
#2.
All ideas most welcomeAre the two machines in the same OU ?
There are policy settings that can impact whether NTLM, v1, v2,
are available, and also policies that control the digital communications
signing requirements. If the two are in different OU, or if there are
any GPOs that have their application security group filtered, or if
these policies are left to the local policies to set and they are there
set differently, any one of those could explain your situation.
Assuming that you have looked at the SQL network server and
client library settings and these are in agreement, and that as far as
you can tell the two SQL Server services are otherwise configured
in same/similar way, then it could well be different policy settings.
You could try using resultant set of policy to see the effective
settings on the two servers and compare, particularly the policy
settings in the innermost Security section of Computer policy that
impact NTLM level and signing, and impact network server and
network client behaviors.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome|||The machines are both in the same OU. So I'm guessing that the same GPO is
being applied to both machines.
I have just found the "RSoS" snap-in. Had no idea the thing existed.
Could you let me have the names of the policies that effect Kerberos and
NTLM ?
Thanks
"Roger Abell [MVP]" wrote:

> Are the two machines in the same OU ?
> There are policy settings that can impact whether NTLM, v1, v2,
> are available, and also policies that control the digital communications
> signing requirements. If the two are in different OU, or if there are
> any GPOs that have their application security group filtered, or if
> these policies are left to the local policies to set and they are there
> set differently, any one of those could explain your situation.
> Assuming that you have looked at the SQL network server and
> client library settings and these are in agreement, and that as far as
> you can tell the two SQL Server services are otherwise configured
> in same/similar way, then it could well be different policy settings.
> You could try using resultant set of policy to see the effective
> settings on the two servers and compare, particularly the policy
> settings in the innermost Security section of Computer policy that
> impact NTLM level and signing, and impact network server and
> network client behaviors.
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
>
>|||I suspect Server #2 has an SPN registered for the SQL Server, but the
account running the server has no priviledges over that SPN. Check what SPNs
are registered for the two machines. Use a tool like setspn.exe (available
for download from microsoft download center).
This article is very usefull on troubleshooting these kind of issues:
http://www.microsoft.com/technet/pr...y/tkerberr.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Al" <Al@.discussions.microsoft.com> wrote in message
news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
> This is to do with SQL Server 2005, but I think it is a more general
> issue.
> We have two SQL Servers, but starting with bog-standard Domain accounts
> and
> both running in Windows Authentication mode. Either account it trust for
> delegation, nor are the machines, as we do not need delegation and don't
> which to turn it on.
> When trying to attach to SQL Server #1, with Windows using TCP/IP, get a
> connection and we can see that is it using NTLM for authentication.
> When trying to attach to SQL Server #2, using TCP/IP, get an error,
> "Cannot
> generate SSPI context". When trying to attach using Named Pipes, get a
> connection and again we can see that it is using NTLM for authentication.
> SQL Server #2 does have TCP/IP enabled. It does not have (as far as I can
> tell) IP Security configured.
> We are using Active Directory 2003, running in Native Mode.
> What would allow SQL Server #1 to allow NTLM over TCP/IP, but not SQL
> Server
> #2.
> All ideas most welcome|||Thanks Remus, you were spot on with that.
We hadn't noticed that when we bounced the SQL instance that it was logging
that it couldn't unregister the SPN.
Not 100% sure how we got into that state, but not important.
Thanks again,
Al
"Remus Rusanu [MSFT]" wrote:

> I suspect Server #2 has an SPN registered for the SQL Server, but the
> account running the server has no priviledges over that SPN. Check what SP
Ns
> are registered for the two machines. Use a tool like setspn.exe (available
> for download from microsoft download center).
> This article is very usefull on troubleshooting these kind of issues:
> http://www.microsoft.com/technet/pr...y/tkerberr.mspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:50D9C968-5AB6-4D1C-9D7D-5E8BD538785F@.microsoft.com...
>
>