3

I'm trying to get Kerberos authentication to work with a named SQL 2008 instance. I've got it working if I connect to the instance as machine_name\instance_name. However, we have multiple A records in DNS for the same host. When I try to connect to the same instance as other_name\instance_name (where "other_name" is another A record that points to the same IP address as "machine_name"), it falls back to NTLM authentication. I've added the following SPN records:

  • MSSQLSvc/other_name:port service_account
  • MSSQLSvc/other_name:instance_name service_account
  • HOST/other_name machine_name
  • HOST/other_name.fqdn machine_name

I don't know where to go from here. Any ideas?

Ben Thul
  • 2,969
  • 16
  • 23
  • What is the error info in your SQL Server ERRORLOG?? Can you try running your SQL Server under LocalSystem/Network Service then the SPN's are registered automatically. – Guido van Brakel Jun 05 '11 at 15:58
  • I'm not seeing anything in either the Windows event log or the SQL server log. I don't have the option of running under the Network Service account. – Ben Thul Jun 06 '11 at 16:16
  • Are TCP/IP or NP enabled in the SQL Server Protocols? See also here: http://blogs.msdn.com/b/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx – Guido van Brakel Jun 06 '11 at 16:57
  • Did you register the aliased SPNs under the service account or under your account? – Remus Rusanu Jun 06 '11 at 17:52
  • The service account. I got good at it because I had to manually register a bunch to get Kerberos authentication working for these servers in the first place. :) – Ben Thul Jun 06 '11 at 18:35

2 Answers2

1

This might be helpful: Understanding Kerberos and NTLM authentication in SQL Server Connections

Guido van Brakel
  • 942
  • 5
  • 10
  • That was one of the documents that I used to set up Kerberos authentication in the first place. The part that gets me is "nslookup, type the ipaddress, should get FQDN, or type FQDN should return ipaddress". FQDN->IP works, however IP->FQDN will only ever return one of the server's names. Does that mean that that you can't use Kerberos authentication with all of two or more names for a server? – Ben Thul Jun 06 '11 at 18:39
1

From what I've heard from AD admins in the past is that Kerberos doesn't work well with aliases. I have a feeling your best bet is to find all of the machines that reference the alias names and reconfigure them to use the machine name that is registered in Active Directory. You may be able to get lucky and drop SQL Server client network aliases on the machines that you can't figure out how to reconfigure. The client network aliases can point to the server name registered in AD and I have a feeling that will work as well. Good luck.

Ra Osolage
  • 173
  • 2
  • 2
  • 10
  • BTW - You're probably better off asking this question of the Active Directory gurus. I think you're problem is directly related to how Kerberos tickets are generated and handled. – Ra Osolage Jun 07 '11 at 01:33