Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need to use Kerberos, but it still is using NTLM Expand / Collapse
Author
Message
Posted Wednesday, March 2, 2011 10:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 31, 2014 8:22 AM
Points: 365, Visits: 694
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.



Post #1072061
Posted Friday, March 23, 2012 8:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 28, 2013 5:31 AM
Points: 201, Visits: 270
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
Post #1271716
Posted Friday, March 23, 2012 4:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 31, 2014 8:22 AM
Points: 365, Visits: 694
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...
Post #1272163
Posted Thursday, March 29, 2012 1:39 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:19 AM
Points: 728, Visits: 774
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.

Post #1274908
Posted Tuesday, April 3, 2012 9:35 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, August 18, 2014 8:24 AM
Points: 6,634, Visits: 1,871
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1277417
Posted Tuesday, November 19, 2013 11:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, August 16, 2014 4:24 PM
Points: 80, Visits: 351
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
Post #1515704
Posted Wednesday, November 20, 2013 9:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 6,306, Visits: 13,605
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"
Post #1516108
Posted Wednesday, November 20, 2013 10:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, August 16, 2014 4:24 PM
Points: 80, Visits: 351
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
Post #1516133
Posted Sunday, November 24, 2013 1:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:57 PM
Points: 1,616, Visits: 1,543
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1517117
Posted Sunday, January 26, 2014 2:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:45 AM
Points: 40, Visits: 458
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
Post #1534823
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse