Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question
2  
Can you run setspn –L domain\serviceaccount from the command line and see if it matches the -Q output. –  Mark Storey-Smith Dec 10 '12 at 21:53
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 '12 at 23:06
1  
However, Darin, your point of checking all the machines led me to look at the event log on the DC. It says there are "There are multiple accounts with name MSSQLSvc/mgsqldev01.magellan.inet:1433 of type DS_SERVICE_PRINCIPAL_NAME." I will delete the duplicates and update my post. –  Max Vernon Dec 10 '12 at 23:12
2  
For reference the best thing to do is download and read Troubleshooting Kerberos Errors. –  Remus Rusanu Dec 10 '12 at 23:34
1  
Also make sure the timezone is correct. –  Dave Markle Dec 11 '12 at 3:37
show 9 more comments

1 Answer 1

up vote 4 down vote accepted

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 like @Mark Storey-Smith asked in his comment to the question 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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