Need to use Kerberos, but it still is using NTLM

Question

Need to use Kerberos, but it still is using NTLM

Marcia Q

SSCrazy

Points: 2648
More actions
March 2, 2011 at 10:28 am

#234594

I am having trouble with my SQL server not using Kerberos. I read Brian Kelly's article on sqlservercentral.com, but I STILL have problems...
Here's our setup:
SQL Server 2008 R2
Named instance using a static port
Windows 2008 R2.
The server is is in DomainX.
Our network admin manually registered the SPN for the DomainQ via this command:
setspn -A MSSQLSvc/servername.DomainX:port# DomainQ\SQLID
When I ran setspn -L DomainQ\SQLID, the spn was showing
However, the sql log still showed an error that it couldn't register the SPN. When I checked sys.dm_exec_connections, it showed NTLM authentication.
So then our network admin manually registered the SPN for the X domain via these 2commands:
setspn -A MSSQLSvc/servername.domainX:port# DomainX\SQLID
That gave a little bit of progress.
The sql log showed that it successfully registered the Service Principal Name!! It showed the instance name instead of the port number. Not sure if that matters, but I took that as a good sign.
However, when I checked sys.dm_exec_connections using SSMS, it showed NTLM authentication. (sigh)
We also ran the following: setspn -A MSSQLSvc/servername:port# DomainX\SQLID
Didn't help.
I don't know if a server restart is needed, but I did. That didn't help. Plus, the service didn't automatically restart.
I didn't see any obvious entries in the event viewer logs.
What else do I need to try to get it to use Kerberos? Are there Active Directory settings for the SQLID that need to change? Are there Kerberos settings that need to change? Is there something in the registry?
Any help would be appreciated.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

gbd77rc Ten Centuries Points: 1031 More actions · Answer 1

Hi

Are you running that query on the same box as SQL is installed? Try it on another box and see if you get the same effect. Have a look at this for extra troubleshooting tips.

http://blogs.msdn.com/b/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

Regards

Richard...

http://www.linkedin.com/in/gbd77rc

Marcia Q SSCrazy Points: 2648 More actions · Answer 2

Thanks for the reply.

The link you listed is a good one and was another site I used as a reference.

What we now have is that 1 out of 3 of our instances have the Kerberos auth.

On the other two instances, the connections from the client machines of IT operations staff are Kerberos, and the connections from our application servers are NTLM.

Due to time constraints, we've more or less given up on the Kerberos requirement. It bothers me that we weren't able to resolve it in full. Someday...

heymiky SSCarpal Tunnel Points: 4875 More actions · Answer 3

here are a couple of things that i've found when setting this up....

1. Use FQDN's when specifying your service object e.g. setspn -A MSSQLSvc/Server_Name.sub_domain.root_domain.org.com:port domain\SQL_Service_Accont

2. MSDTC Configuration

On each SQL server being used for replication, perform the following steps from Start/Run dcomcnfg

Expand the “Component Services” node, then the “My Computer” node, then right-Click and select “Properties”

Select the MSDTC Tab, and select the “Security configuration…” button.

Select Network DTC Access, Allow Remote Administration, Allow Inbound, Allow Outbound, Mutual Authentication Required.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

MS DTC doesn't play into this, so that configuration is completely coincidental.

How are the two domains related? Same forest? Different forests? Parent-child domain?

K. Brian Kelley
@kbriankelley

Slevin Mr or Mrs. 500 Points: 574 More actions · Answer 5

I wonder if this is because it is cached creds. Did you try to:

1. Open cmd prompt

2. type "klist.exe purge"

3. Establish a new connection

Additionally, you can install the Kerberos Configuration Tool: http://www.microsoft.com/en-us/download/details.aspx?id=39046. This will identify any issues with your SPNs and allow you to correct them.

A server reboot would also flush the credential cache. Hope this helps!

~Slevin

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

Marcia Q (3/2/2011)
I am having trouble with my SQL server not using Kerberos. I read Brian Kelly's article on sqlservercentral.com, but I STILL have problems...
Here's our setup:
SQL Server 2008 R2
Named instance using a static port
Windows 2008 R2.
The server is is in DomainX.
Our network admin manually registered the SPN for the DomainQ via this command:
setspn -A MSSQLSvc/servername.DomainX:port# DomainQ\SQLID
When I ran setspn -L DomainQ\SQLID, the spn was showing
However, the sql log still showed an error that it couldn't register the SPN. When I checked sys.dm_exec_connections, it showed NTLM authentication.
So then our network admin manually registered the SPN for the X domain via these 2commands:
setspn -A MSSQLSvc/servername.domainX:port# DomainX\SQLID
That gave a little bit of progress.
The sql log showed that it successfully registered the Service Principal Name!! It showed the instance name instead of the port number. Not sure if that matters, but I took that as a good sign.
However, when I checked sys.dm_exec_connections using SSMS, it showed NTLM authentication. (sigh)
We also ran the following: setspn -A MSSQLSvc/servername:port# DomainX\SQLID
Didn't help.
I don't know if a server restart is needed, but I did. That didn't help. Plus, the service didn't automatically restart.
I didn't see any obvious entries in the event viewer logs.
What else do I need to try to get it to use Kerberos? Are there Active Directory settings for the SQLID that need to change? Are there Kerberos settings that need to change? Is there something in the registry?
Any help would be appreciated.

use the latest version of SETSPN which you can get from any Windows 2008 R2 server with the AD domain services role installed. The latest SETSPN has a -S switch which checks for duplicates. Your AD admin must also enable for delegation the user account under which sql runs and ultimately the SPN is created against.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Slevin Mr or Mrs. 500 Points: 574 More actions · Answer 7

Actually, you don't need to enable delegation unless you are working with linked servers and windows authentication. The account does need the permission to write public information in active directory in order to create the SPNs.

Slevin

Robert Davis One Orange Chip Points: 28027 More actions · Answer 8

Is your service account in DomainQ or DomainX? Everything has to be in the same domain for Kerberos to work including the user connecting.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

peterjonk SSCommitted Points: 1655 More actions · Answer 9

Don't use the instance name in the SETSPN command. Just make sure the machine name (NETBIOS and FQDN) and port is correct.

This part was giving me some headaches too in the past.

__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net

NathanCampbell SSC Journeyman Points: 85 More actions · Answer 10

Marcia Q - Did you get it working? I just with through the same setup on my end and I can hopefully help. Just wanted to check with you in care you already solved it.