8

I am setting up a DEV/TEST environment using 2 SQL Servers running SQL Server 2012 on Windows Server 2012. We are moving from SQL Server 2005 on Windows Server 2008, where we already have this up-and-running correctly.

In SQL Server 2012, Kerberos authentication is not working.

Each server has it's own Active Directory account that has the "Write Service Principal Names" and "Read Service Principal Names" rights granted through Active Directory Users and Computers. Whenever I connect to the SQL Server 2005 servers, and run:

SELECT net_transport, auth_scheme 
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID;

I see:

net_transport    auth_scheme
TCP              KERBEROS

When I perform the same query against my new SQL Server 2012 instances, I see:

net_transport    auth_scheme
TCP              NTLM

If I use SetSPN -Q MSSQLSvc/* to query Active Domain for Service Principal Names, I see both the 2005 and 2012 servers listed, exactly the same way other than the name of the server.

For instance:

MSSQLSvc/SERVERa2005.domain.inet
MSSQLSvc/SERVERa2005domain.inet:1433
MSSQLSvc/SERVERb2005.domain.inet
MSSQLSvc/SERVERb2005domain.inet:1433
MSSQLSvc/SERVERa2012.domain.inet
MSSQLSvc/SERVERa2012domain.inet:1433
MSSQLSvc/SERVERb2012.domain.inet
MSSQLSvc/SERVERb2012domain.inet:1433

What else do I need to do to enable Kerberos Authentication to SQL Server 2012? Books Online seems to have nothing else to say, except that the SPN's have to be setup. Which clearly, they are. SQL Server Error logs on both 2012 machines say:

2012-12-10 14:55:47.630 The SQL Server Network Interface library 
                            successfully registered the Service Principal Name (SPN)
                            [ MSSQLSvc/SERVERa2012.domain.inet ] for the SQL Server
                            service. 

2012-12-10 14:55:47.630 The SQL Server Network Interface library 
                            successfully registered the Service Principal Name (SPN) 
                            [ MSSQLSvc/SERVERa2012.domain.inet:1433 ] for the SQL 
                            Server service. 

2012-12-10 14:55:47.590 SQL Server is attempting to register a Service 
                            Principal Name (SPN) for the SQL Server service. 
                            Kerberos authentication will not be possible until a 
                            SPN is registered for the SQL Server service. This is an
                            informational message. No user action is required.
Improve this question
2
  • 2
    My favorite Kerberos failures are caused by computers whose clocks have drifted "too far" apart. Make sure that they are synchronized with the domain's official time.
    – Darin Strait
    Dec 10, 2012 at 23:06
  • 1
    Also make sure the timezone is correct.
    – Dave Markle
    Dec 11, 2012 at 3:37

2 Answers 2

Reset to default
8

Dealing with Active Directory is always so much fun. The single most important thing here is to realize you are dealing with distributed data that can take time to propagate across your network.

The SQL Servers in question had their name changed as part of an upgrade procedure; we replaced an existing machine (SQL01) running SQL Server 2005 with a new machine (SQL03) running SQL Server 2012. SQL03 was the name of the new machine when I initially set it up in the domain. SQL01 had an existing SPN associated with a single domain account we used for several SQL Servers running 2005. Since it is a best practice to only run a single machine under any given domain account, I created a new account and configured SQL03 to run with that account name. After taking the original SQL01 out of service, and renaming SQL03 to SQL01, there was an SPN conflict.

I used the SetSPN.exe utility to delete the conflicting SPN (on the old domain account) - and it still didn't work. At this point, I did nothing further and moved on to other items. When I came back around 30 minutes later, KERBEROS authentication was working. I simply needed to wait for the SPN change to propagate among our domain controllers.

I used SetSPN -L DOMAIN\Account and compared that output with SetSPN -Q MSSQLSvc/Machine.domain.inet:1433 to find the duplicate SPNs, and then used SetSPN -D MSSQLSvc/Machine.domain.inet:1433 DOMAIN\Account to remove the old SPNs.

Improve this answer
2

Additional reading on troubleshooting Kerberos errors can be found at the following links

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.