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 ««12345»»»

Configuring Kerberos Authentication Expand / Collapse
Author
Message
Posted Thursday, December 11, 2008 6:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 5, 2013 9:59 AM
Points: 307, Visits: 385
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
Post #617835
Posted Thursday, December 11, 2008 6:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:34 PM
Points: 17, Visits: 572
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.
Post #617841
Posted Thursday, December 11, 2008 7:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:18 AM
Points: 207, Visits: 200
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.


Post #617892
Posted Thursday, December 11, 2008 7:39 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, September 15, 2014 8:57 AM
Points: 6,634, Visits: 1,872
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, 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 #617904
Posted Thursday, December 11, 2008 7:40 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, September 15, 2014 8:57 AM
Points: 6,634, Visits: 1,872
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, 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 #617906
Posted Thursday, December 11, 2008 7:48 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 1,862, Visits: 3,607
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.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #617920
Posted Thursday, December 11, 2008 8:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:43 PM
Points: 11,321, Visits: 13,112
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

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
Post #617936
Posted Thursday, December 11, 2008 8:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 554, Visits: 1,197
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.
Post #617937
Posted Thursday, December 11, 2008 8:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 5, 2013 9:59 AM
Points: 307, Visits: 385
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.
Post #617940
Posted Thursday, December 11, 2008 8:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 1,815, Visits: 2,191
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.

Post #617965
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse