SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to use Kerberos, but it still is using NTLM


Need to use Kerberos, but it still is using NTLM

Author
Message
Marcia Q
Marcia Q
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
Points: 519 Visits: 699
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.
gbd77rc
gbd77rc
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 272
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
Marcia Q
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
Points: 519 Visits: 699
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
heymiky
SSC Eights!
SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)

Group: General Forum Members
Points: 921 Visits: 845
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
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (10K reputation)

Group: Moderators
Points: 10132 Visits: 1917
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
Slevin
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 385
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
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19650 Visits: 17239
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
Slevin
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 385
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
Robert Davis
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2720 Visits: 1623
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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
peterjonk
peterjonk
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 618
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search