Configuring Kerberos Authentication

Question

Configuring Kerberos Authentication

Viewing 15 posts - 76 through 89 (of 89 total)

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

jswong05 Hall of Fame Points: 3553 More actions · Answer 1

The Microsoft publication is great. But after I set SPN for the service account, I went to AD (based on page 6, Figure 2), the registered SPN entry is not there. There is no way to grant delegation.

Anybody has any experiences on this?

We had to use designated slq login on linkserver as workaround. (that does not meet single sign on principle)

by the way, after SPN registering, the entry will only show up under SETSPN -L domain\serviceaccountuser

not under SETSPN -L hostname

Is that normal?

Jason
http://dbace.us
😛

spongemagnet SSCrazy Points: 2812 More actions · Answer 2

I recently had a problem where a SQL Server was falling back to NTLM. The solution was rather simple, and in case its of help (I don't think I've seen it mentioned in this yet) - check for duplicate SPN's.

setspn -Q MSSQLSvc/servername.blah:1433

Showed me that another accountname had the same SPN registered. After deleting the duplicate SPN, querying sys.dm_exec_connections reported any new connections using Kerberos auth scheme.

-Ken

danny.thomas 57541 Grasshopper Points: 16 More actions · Answer 3

For sql service domain accounts that are NOT domain admins you can configure the service account(s) to create its own SPN on startup so you dont have to worry about the correct syntax for creating the SPN yourself (and also whether or not to create multiple SPNs for clustered instances). If you have 50, 100, 200 sql servers do you really want to be manually configuring/administrating SPNs ?

For each sql service account (which can be running 1 + N sql services) ask the domain administrator to ensure delegation is selected in the domain user account settings. Also you will need to request (or do this yourself CAREFULLY if you have privileges) that your domain admin make the following changes in AD :

Using ADSIEDIT for the sql service account (s) grant permissions to SELF for the following properties :

Read servicePrincipalName

Write servicePrincipalName

This way the SQL server instance will create its own correctly formatted SPN at startup, sql restart is required using this method. Also sql server will not perform any maintenance on this or other SPNs so if you have a large server base you should consider listing the current SPNs to determine if any are redundant or no longer required.

Ludo Bernaerts Ten Centuries Points: 1036 More actions · Answer 4

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?

moojjoo SSC Eights! Points: 957 More actions · Answer 5

Brian you should contact http://www.devproconnections.com/ and have this article printed. What a great piece of writting.

I ran your T-SQL on a SharePoint Content DB ---- WSS_Content DB and I was suprised to see auth_scheme is KERBEROS which is great news.

However, my question is I did not configure that, so I am assuming this is something SharePoint 2010 does. Thank you so much for the article.

I will keep this in my Favorites for SQL Server when configuring SSRS. You made the start of my day nice with my cup of coffee.

Leonel Umaña Araya SSC Veteran Points: 257 More actions · Answer 6

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 SSCarpal Tunnel Points: 4202 More actions · Answer 7

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 Hall of Fame Points: 3286 More actions · Answer 8

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 SSC Guru Points: 100851 More actions · Answer 9

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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

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

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
@kbriankelley

fdh4w245 SSC Enthusiast Points: 151 More actions · Answer 11

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 One Orange Chip Points: 27055 More actions · Answer 12

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 SSC Rookie Points: 37 More actions · Answer 13

Hi Brain,

I have a problem here 🙁

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.

1362015-01-30 17:50:29.2772015-01-30 17:50:29.280domain\userTSQLKERBEROSservernameMicrosoft SQL Server Management Studio

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

select * from vSMS_R_System

Pleaseeeeeeee helppp me...... 🙁

Regards,

Jay

larry.tenison 36927 SSC Rookie Points: 45 More actions · Answer 14

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.

Johnsmith Grasshopper Points: 10 More actions · Answer 15

This reply has been reported for inappropriate content.

We provide Technical Help to our user by a diagnosis of their computer and other devices. And if there is an issue to be solved, we give out the solution. This helps the user to avoid any existing issue.

Attachments:

You must be logged in to view attached files.