Sql-server – Kerberos authentication not working with Linked Servers in SQL Server 2012

kerberoslinked-serversql serversql-server-2012

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.

Best Answer

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.

Related Question