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;
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.
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.