No announcement yet.

SQL Authentication Question NTLM or Kerberos?

  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Authentication Question NTLM or Kerberos?

    Some weeks ago I posted here with a question on DNS with some "phantom" domain controllers appearing in nslookup queries. I was concerned that our DNS was corrupt and that this was causing an issue with our SQL Server 2005 application.
    To cut a long story short, the DNS problem was fixed but the SQL problem was not.
    We run a SQL based ERP system with a separate accounts system also SQL. Recently we migrated to a new domain controller. Since that time, we have been having problems with the ERP system. Intermittently users get a "cannot generate SSPI context" error. The accounts system has been unaffected. The differences between the systems are these.
    The ERP system uses the older SQL 7.0 ODBC driver with Windows authentication on the server side. The accounts system uses the SQL Native Client and SQL authentication on the server side. Consequently SQL is set to mixed mode authentication. SQL runs on a member server and was not changed in any way during the migration.
    I've looked at numerous articles about the SSPI error, connection and authentication types. Querying our server, I understand that the authentication type is NTLM. There are also SPNs set for the server and every machine on the network in Active Directory.
    What I don't understand is this. Should our authentication be Kerberos? If it should, how do I change it. Also, if it does change, how will this affect the application that requires SQL authentication?
    Any help would be gratefully received.
    Incidentally, we have three servers, one DC running Windows 2003, SQL 2005 Server on Windows 2003 and a Windows 2000 server running ISA. All the clients are XP SP2.