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


Configuring Kerberos Authentication


Configuring Kerberos Authentication

Author
Message
Harold Buckner
Harold Buckner
SSC Eights!
SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)

Group: General Forum Members
Points: 971 Visits: 420
Great article Brian. I've been working with Kerberos Authenication for a while and I had to scour the internet looking for something that explained it like this.

One thing we have problems with is a user can log in to their PC and get a ticket. Authenicate to the SQL servers using Kerberos fine, but if for some reason their ticket expires, ( Maybe locking their workstaion instead of loging off over night)their ticket does not automaticlly renew and then they start getting failed logins. The only fix we have found is having the user log off and then back in. Then the ticket gets renewed.

I'm sure there is something wrong, but how to identify it and then relay it to the network admins is going to be a bear. Do you have any recommendations to point me in a direction?


Thanks
mark.wojciechowicz@gmail.com
mark.wojciechowicz@gmail.com
SSC-Enthusiastic
SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)

Group: General Forum Members
Points: 195 Visits: 611
Hi Brian, Great Article. I just spent the last week setting Kerberos up so this is really synchronicity that this topic is showing up today. You explain it more clearly than any article I have seen out there.

I know that you did not intend to cover delegation as a topic for this article, but for the folks who are working on this now, you can configure delegation by going to AD, finding the computer record for the server that will be doing the delegation and check the box for allowing delegation.

Also, if you are setting up a web server, the web.config file needs to be set to use windows authentication and allow impersonation. The impersonation will allow the server to pass your credentials to the next server.

It most situations where you are just dealing with serving reports, a generic id to connect to the server will work fine, but when you are refining your security model on SQL server to use windows authentication this is critical. Also, if you are having users insert and update records through your web ap, it is critical to have their correct credentials for auditing.

Thanks again for explaining this concept so well, its making a lot more sense to me. I got into a discussion with another developer over using Kerberos or LDAP and I think this artical hits upon some key concerns.
LeeFAR
LeeFAR
SSC Eights!
SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)SSC Eights! (995 reputation)

Group: General Forum Members
Points: 995 Visits: 327
Good work Brian. This explanation helps not only in the SQL Server world, but anywhere where Kerberos is required. At first glance and try, Kerberos is a pain to setup. But this article is one of the better ones out there explaining how.



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (23K reputation)

Group: Moderators
Points: 23730 Visits: 1917
barb.wendling (12/11/2008)
Very interesting article, very helpful and clearly written. I just set up a Domain/User to run SQL 2000 on a Win2K server had had to reattach the server to the domain and reboot for all settings to take effect and allow Windows Authentication to work using SSMS to connect to server. Does your approach require rebooting?


If you're just setting up Kerberos authentication, rebooting shouldn't be required. The catch is you have to wait for the SPNs to replicate to all the domain controllers as part of the normal replication cycles.

K. Brian Kelley
@‌kbriankelley
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (23K reputation)

Group: Moderators
Points: 23730 Visits: 1917
Jack Corbett (12/11/2008)
Great article Brian. Will there be a follow-up on setting up Kerberos delegation? I could use it.


I'll look at writing that up. I earned my wings on that due to Microsoft CRM 3.0. Boy that one hurt. If you're running CRM, SSRS, and the SQL Server all on the same box, you don't have to worry about any of that. But when you're not, for instance, you're trying to scale out like we were, it can become a nightmare. Same is true when you do a load-balanced SSRS web farm.

K. Brian Kelley
@‌kbriankelley
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11948 Visits: 3764
Brian, thanks for taking the time to write about this. It's so important and, at the same time, such a pain to understand and implement properly.

I have recently come across the problem while trying to make reporting services talk to an analysis services instance through Windows integrated security. In the past we have circumvented the double-hop authentication issue by configuring reporting services (and linked servers) to talk to the destination datasource through SQL authentication. However, in doing so we have compromised security. Also, with Analysis Services, SQL authentication does not work, so now we are forced to make Kerberos work.

You have really felt the pulse of the community with this one.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42421 Visits: 14925
K. Brian Kelley (12/11/2008)
Jack Corbett (12/11/2008)
Great article Brian. Will there be a follow-up on setting up Kerberos delegation? I could use it.


I'll look at writing that up. I earned my wings on that due to Microsoft CRM 3.0. Boy that one hurt. If you're running CRM, SSRS, and the SQL Server all on the same box, you don't have to worry about any of that. But when you're not, for instance, you're trying to scale out like we were, it can become a nightmare. Same is true when you do a load-balanced SSRS web farm.



That'd be great. Here's a question for anyone on the thread. TO get my web application to use Windows Authentication I assume I need to get SPN setup on the Web Server AND the SQL Server? In addition to what mark says here:

Also, if you are setting up a web server, the web.config file needs to be set to use windows authentication and allow impersonation. The impersonation will allow the server to pass your credentials to the next server.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Bradley Deem
Bradley Deem
SSC Eights!
SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)

Group: General Forum Members
Points: 951 Visits: 1248
Harold Buckner (12/11/2008)
Great article Brian. I've been working with Kerberos Authenication for a while and I had to scour the internet looking for something that explained it like this.

One thing we have problems with is a user can log in to their PC and get a ticket. Authenicate to the SQL servers using Kerberos fine, but if for some reason their ticket expires, ( Maybe locking their workstaion instead of loging off over night)their ticket does not automaticlly renew and then they start getting failed logins. The only fix we have found is having the user log off and then back in. Then the ticket gets renewed.

I'm sure there is something wrong, but how to identify it and then relay it to the network admins is going to be a bear. Do you have any recommendations to point me in a direction?


Thanks



I have the EXACT same problem with Kerberos. Resulting in the NT AUTHORITY\ANONYMOUS LOGON. It does work though, because I'm able to connect from a Web Server to the SQL server using Kerberos and from the Web Server to SSRS on another server. It just breaks like Harold described above. Log off/Log on to resolve.
Harold Buckner
Harold Buckner
SSC Eights!
SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)

Group: General Forum Members
Points: 971 Visits: 420
Bradley Deem (12/11/2008)

I have the EXACT same problem with Kerberos. Resulting in the NT AUTHORITY\ANONYMOUS LOGON. It does work though, because I'm able to connect from a Web Server to the SQL server using Kerberos and from the Web Server to SSRS on another server. It just breaks like Harold described above. Log off/Log on to resolve.



I'm glad I'm not the only one.
MG-148046
MG-148046
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3651 Visits: 2825
Jack Corbett (12/11/2008)
Great article Brian. Will there be a follow-up on setting up Kerberos delegation? I could use it.


I second the motion !!!!:D

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
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