Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Configuring Kerberos Authentication


Configuring Kerberos Authentication

Author
Message
Leonel Umaña Araya
Leonel Umaña Araya
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 28
Few days ago, I had problems with connections using Kerberos.
Thanks Brian, your article help me a lot to understand all the behavior that involves SSPI


Leonel E. Umaña Araya
leo_umana@hotmail.com
Carlton Leach
Carlton Leach
SSC Eights!
SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)

Group: General Forum Members
Points: 883 Visits: 1285
Ludo Bernaerts (3/25/2011)
great article but I have a question.
I configured a sql instance like mentioned in the article and see all connections coming in with Kerberos auth.
However the connections coming from his own (sqlagent & OS) are still NTLM. What can be the cause of this?


1000 possible reasons...download kerbtray.exe from microsoft and see if tickets are getting passed successfully.

Carlton.
Rich Weissler
Rich Weissler
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2015 Visits: 954
Absolutely wonderful article. This is what Friday should be like!
Two discoveries, and I apologize if they were in the article or someone already pointed them out and I missed them.
1. Obviously, the cluster name should be used for the server name in a cluster situation.
2. NTLM seems to be used for local connections, even when Kerberos is functionally available.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 7660
The timing on this re-print couldn't be better. Thanks!!!


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6770 Visits: 1908
Ludo Bernaerts (3/25/2011)
great article but I have a question.
I configured a sql instance like mentioned in the article and see all connections coming in with Kerberos auth.
However the connections coming from his own (sqlagent & OS) are still NTLM. What can be the cause of this?


If this is on a cluster, then Kerberos is not guaranteed. A lot of connections will be via NTLM. Also, if you've only configured the SPNs with the ports, then Named Pipes isn't covered (or if you have a SQL Server 2005 instance, which doesn't include Kerberos support for Named Pipes) so if the local connections are being made that way, then you'll see NTLM also.

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
fdh4w245
fdh4w245
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 198
Hi,
page 14 of your presentation "Security Enhancements in SQL Server 2008" suggests that
"Kerberos possible without SPN registered in AD"

could you pls point me towards a resource explaining how this works.

Many thanks
Erdöl Biramen
Senior DBA
ALSTOM / Switzerland
Michael L John
Michael L John
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2051 Visits: 7385
In the article, it states:
For a named instance, we typically only require two commands, because there isn't a case where a client is just connecting to the name of the server. For instance, let's assume we have a named instance called Instance2 listening on port 4444 on that same server using that same service account. In that case we'd execute the following commands:

SETSPN -A MSSQLSvc/MyDBServer:4444 MyDomain\SQLServerService
SETSPN -A MSSQLSvc/MyDBServer.mydomain.com:4444 MyDomain\SQLServerService

Isn't this command incorrect? The command is the same as a default instance.

Shouldn't this be:
SETSPN -A MSSQLSvc/MyDBServer:MyInstance:4444 MyDomain\SQLServerService
SETSPN -A MSSQLSvc/MyDBServer.mydomain.com:MyInstance:4444 MyDomain\SQLServerService

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
jayakumar.rajendran
jayakumar.rajendran
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 3
Hi Brain,

I have a problem here Sad

I've configured everything correctly in my SCCM environment. And i used to connect the CAS database from a separate box(same domain) which has SSMS console installed in it. Now the problem is, whenever i try to run a query in CAS locally, it runs successfully. But when i connect the CAS database remotely and run the same query with the same login i used in CAS, it says the below error. This is happening from past 2 days only. :'(

Query ran :
select top 100 * from v_GS_WORKSTATION_STATUS

Error :

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
OLE DB provider "SQLNCLI11" for linked server "abc.domain.com" returned message "Invalid connection string attribute".


I checked the SQL connection authentication information it says authenticated by kerberos.

136 2015-01-30 17:50:29.277 2015-01-30 17:50:29.280 domain\user TSQL KERBEROS servername Microsoft SQL Server Management Studio

But, another wierd information is i can successfully run the below query.

select * from vSMS_R_System

Pleaseeeeeeee helppp me...... Sad

Regards,
Jay
larry.tenison 36927
larry.tenison 36927
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 0
Brian et. al., we are struggling to get SQL authentication with Kerberos running on a SQL 2014 named instance called SHAREPOINT, port number 5733. The default instance on this db server is occupied by Lync. I read your article -- very concise and helpful, thank you. We've tried everything we can think of but SQL still reports all connections are defaulting to NTLM.

One point of confusion is this -- after starting and restarting the SQL named instance, we got 2 SPN's automatically created, as follows:

MSSQLSvc/<hostname.domain.net:5733> <account> and
MSSQLSvc/<hostname.domain.net:SHAREPOINT> <account>

Your article suggests (like others I've read) that we also need an SPN for the NetBIOS name of the server and port number. In other words:
MSSQLSvc/<hostname:5733> <account> and
MSSQLSvc/<hostname:SHAREPOINT>

BUT no matter what we do or try, SETSPN rejects these two commands w/ a syntax error, saying that "MSSQLSvc/<hostname:5733> is an unknown parameter".

So, when using a NAMED INSTANCE for SHAREPOINT, do we need 2 or 4 SPN's on the service account? And what is causing the syntax error problem we keep seeing when specifying only the host name?

We are stumped and need some advice.
Thanks.
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