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...
> > 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
>
>|||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/prodtechnol/windowsserver2003/technologies/security/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 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/prodtechnol/windowsserver2003/technologies/security/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
>
>|||This one has me in hell too and SPN is my issue as well, but I'm trying to
find a less manual solution because this one keeps popping up.
We're running SQL Server 2005 cluster (active/active). SQL server services
on both nodes is running under a domain account that has been added to the
Domain Admins group. Sporadically, whenever we fail over or reboot the
cluster, the SPN for one of the cluster nodes is lost killing all my apps
that rely on Kerberos authentication. Manually adding an SPN for the SQL
server service account resolves the issue, but this always incurs down time.
Why is the SPN not being created automatically? Permission issue? I've read
that this is only a problem when running the SQL service under a domain
account. Shouldn't being part of the Domain Admin group supply sufficient
permissions to enable the domain account to create the SPN on service startup?
Any help with this is GREATLY appreciated.
James
"Al" wrote:
> 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 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/prodtechnol/windowsserver2003/technologies/security/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
> >
> >
> >|||You would think that it should do the trick, having the account belonging to
the Domain Admin Group. I'm assuming that the SQLserver/service only tries to
register itself when it's running as one of the local system accounts.
When running a failover cluster, I don't think you should be registering the
SPN for both nodes individually. I think you should be registering only the
cluster instance name. Or have I misunderstood what you're been doing to
resolve the problem.
Al
"BeezelDub" wrote:
> This one has me in hell too and SPN is my issue as well, but I'm trying to
> find a less manual solution because this one keeps popping up.
> We're running SQL Server 2005 cluster (active/active). SQL server services
> on both nodes is running under a domain account that has been added to the
> Domain Admins group. Sporadically, whenever we fail over or reboot the
> cluster, the SPN for one of the cluster nodes is lost killing all my apps
> that rely on Kerberos authentication. Manually adding an SPN for the SQL
> server service account resolves the issue, but this always incurs down time.
> Why is the SPN not being created automatically? Permission issue? I've read
> that this is only a problem when running the SQL service under a domain
> account. Shouldn't being part of the Domain Admin group supply sufficient
> permissions to enable the domain account to create the SPN on service startup?
> Any help with this is GREATLY appreciated.
> James
>
>
> "Al" wrote:
> > 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 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/prodtechnol/windowsserver2003/technologies/security/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
> > >
> > >
> > >|||When Kerberos is failing, a setspn -L for the SQL service domain account
lists...
MSSQLSvc/cluster1.domain.com:1433
MSSQLSvc/cluster1.domain.com
MSSQLSvc/cluster2.domain.com
It's missing the SPN for the host node of the databases that are all of a
sudden unaccessible via Kerberos.
Manually setting the SPN...
setspn -A MSSQLSvc/cluster2.domain.com:1433 sqladmin
Fixes the issue immediately.
To my knowledge, cluster1 has not had this SPN problem but then again, there
are no apps requiring Kerberos hitting databases on that node. In handling
this issue in the past, only cluster2 loses the SPN for whatever reason.
It's nice knowing the fix, but its not THE FIX. Certainly there is some
magic checkbox somewhere that will allow this SPN to be set automatically or
not be lost in the first place.
Thanks for working with me on this!
"Al" wrote:
> You would think that it should do the trick, having the account belonging to
> the Domain Admin Group. I'm assuming that the SQLserver/service only tries to
> register itself when it's running as one of the local system accounts.
> When running a failover cluster, I don't think you should be registering the
> SPN for both nodes individually. I think you should be registering only the
> cluster instance name. Or have I misunderstood what you're been doing to
> resolve the problem.
>
> Al
> "BeezelDub" wrote:
> > This one has me in hell too and SPN is my issue as well, but I'm trying to
> > find a less manual solution because this one keeps popping up.
> >
> > We're running SQL Server 2005 cluster (active/active). SQL server services
> > on both nodes is running under a domain account that has been added to the
> > Domain Admins group. Sporadically, whenever we fail over or reboot the
> > cluster, the SPN for one of the cluster nodes is lost killing all my apps
> > that rely on Kerberos authentication. Manually adding an SPN for the SQL
> > server service account resolves the issue, but this always incurs down time.
> >
> > Why is the SPN not being created automatically? Permission issue? I've read
> > that this is only a problem when running the SQL service under a domain
> > account. Shouldn't being part of the Domain Admin group supply sufficient
> > permissions to enable the domain account to create the SPN on service startup?
> >
> > Any help with this is GREATLY appreciated.
> >
> > James
> >
> >
> >
> >
> > "Al" wrote:
> >
> > > 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 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/prodtechnol/windowsserver2003/technologies/security/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
> > > >
> > > >
> > > >