December 11, 2008 at 7:48 am
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[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 11, 2008 at 8:04 am
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
Consultant - Straight Path Solutions
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
December 11, 2008 at 8:05 am
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.
December 11, 2008 at 8:09 am
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.
December 11, 2008 at 8:27 am
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.
December 11, 2008 at 8:29 am
Jack Corbett (12/11/2008)
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.
This depends. By default, every computer has SPN records for:
HOST/NetBIOS Name
HOST/Fully Qualified Domain Domain
Or:
HOST/MyWebServer
HOST/MyWebServer.mydomain.com
If your web application is running under an application pool identity of Network Service, it already has the SPN in place, because the client will accept HOST/... in this case. However, you've got to make sure the web site is configured to support Kerberos authentication. It is configured to support NTLM by default (so long as you've checked Windows auth in the GUI and you *don't* have Anonymous auth checked). To toggle your web site to support Kerberos authentication, you've got to run a command at the command line on the server:
K. Brian Kelley
@kbriankelley
December 11, 2008 at 8:32 am
Awesome! Thanks Brian.
Jack Corbett
Consultant - Straight Path Solutions
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
December 11, 2008 at 8:35 am
Bradley Deem (12/11/2008)
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.
It should be able to renew with a new login, and that includes locking the PC. Here's a blog post that explains the issue:
Ticket expirations as a cause of Kerberos authentication failures [/url]
If you're having this problem within seven days, either someone changed the settings at the domain level or you've got a problem at the domain level.
K. Brian Kelley
@kbriankelley
December 11, 2008 at 8:38 am
Maybe I have misread your article, but it seems to me you are saying NTLM will not work on multiple hops. We have in fact had this working using constrained delegation with protocol transitioning. A user on a workstation could successfully connect to IIS as themselves and then onto a sql server as the same account (2 hops). Where we did run into a problem though was trying to hop from one AD Domain to a different, though trusted, domain.
Constrained delegation with protocol transition is setup in your AD on the server properties Delegation tab. Selecting "Trust this computer for delegation to specified services only" (this is the constrained delegation) and then selecting "Use any authentication protocol" (this is the protocol transitioning).
In the case above, it isn't enough just setting up the SPN and then allowing the calling server to delegate. You have to specifically setup which SPNs the server can delegate too.
EDIT: Our problem with crossing domainswas that Kerberos was the only method this hop from one domain to another so we ended up using Kerberos anyway.
December 11, 2008 at 8:43 am
K. Brian Kelley (12/11/2008)
It should be able to renew with a new login, and that includes locking the PC. Here's a blog post that explains the issue:
Ticket expirations as a cause of Kerberos authentication failures [/url]
If you're having this problem within seven days, either someone changed the settings at the domain level or you've got a problem at the domain level.
Thanks brian. This is exactly what's happening. Good to know that is how it's designed and not broken.
December 11, 2008 at 8:47 am
Kevin Rathgeber (12/11/2008)
Maybe I have misread your article, but it seems to me you are saying NTLM will not work on multiple hops. We have in fact had this working using constrained delegation with protocol transitioning. A user on a workstation could successfully connect to IIS as themselves and then onto a sql server as the same account (2 hops). Where we did run into a problem though was trying to hop from one AD Domain to a different, though trusted, domain.Constrained delegation with protocol transition is setup in your AD on the server properties Delegation tab. Selecting "Trust this computer for delegation to specified services only" (this is the constrained delegation) and then selecting "Use any authentication protocol" (this is the protocol transitioning).
In the case above, it isn't enough just setting up the SPN and then allowing the calling server to delegate. You have to specifically setup which SPNs the server can delegate too.
Constrained Delegation = Kerberos
SPN = Kerberos
NTLM is a completely different security protocol which is pre-Windows 2000. Windows systems will attempt to drop back to NTLM if they cannot perform authentication using Kerberos.
K. Brian Kelley
@kbriankelley
December 11, 2008 at 8:56 am
K. Brian Kelley (12/11/2008)
Kevin Rathgeber (12/11/2008)
Maybe I have misread your article, but it seems to me you are saying NTLM will not work on multiple hops. We have in fact had this working using constrained delegation with protocol transitioning. A user on a workstation could successfully connect to IIS as themselves and then onto a sql server as the same account (2 hops). Where we did run into a problem though was trying to hop from one AD Domain to a different, though trusted, domain.Constrained delegation with protocol transition is setup in your AD on the server properties Delegation tab. Selecting "Trust this computer for delegation to specified services only" (this is the constrained delegation) and then selecting "Use any authentication protocol" (this is the protocol transitioning).
In the case above, it isn't enough just setting up the SPN and then allowing the calling server to delegate. You have to specifically setup which SPNs the server can delegate too.
Constrained Delegation = Kerberos
SPN = Kerberos
NTLM is a completely different security protocol which is pre-Windows 2000. Windows systems will attempt to drop back to NTLM if they cannot perform authentication using Kerberos.
We worked directly with Microsoft and members their IIS team on this one (Brian Murphy-Booth and Christopher Haun to be exact). Every connection that was being made indicated an NTLM connection, not keberos. This was from the workstation through to IIS through to SQL Server.
There is also very good tool written by Brian that will help in determining your connection issues.
http://blogs.iis.net/brian-murphy-booth/archive/2007/3/9/1613802.aspx
December 11, 2008 at 8:58 am
Nicely written Brian.
After struggling through an SSRS setup a year ago, with a Microsoft Engineer's help, I feel like I understand it now, or at least understand it better than I did.
Thanks,
December 11, 2008 at 9:06 am
Kevin Rathgeber (12/11/2008)
We worked directly with Microsoft and members their IIS team on this one (Brian Murphy-Booth and Christopher Haun to be exact). Every connection that was being made indicated an NTLM connection, not keberos. This was from the workstation through to IIS through to SQL Server.There is also very good tool written by Brian that will help in determining your connection issues.
http://blogs.iis.net/brian-murphy-booth/archive/2007/3/9/1613802.aspx
That blog post talks about Kerberos authentication. Service Principal Names don't exist in NTLM, and neither does the concept of delegation. So Kerberos is involved somehow. You can use NTLM to the web server and then from the web server to SQL Server, but you have to either switch credentials, or re-authenticate at the web server. Otherwise, what you're saying goes directly against what Microsoft has published in other places and what is generally understood about NTLM. For instance:
How IIS authenticates browser clients
K. Brian Kelley
@kbriankelley
December 11, 2008 at 9:14 am
K. Brian Kelley (12/11/2008)
Kevin Rathgeber (12/11/2008)
We worked directly with Microsoft and members their IIS team on this one (Brian Murphy-Booth and Christopher Haun to be exact). Every connection that was being made indicated an NTLM connection, not keberos. This was from the workstation through to IIS through to SQL Server.There is also very good tool written by Brian that will help in determining your connection issues.
http://blogs.iis.net/brian-murphy-booth/archive/2007/3/9/1613802.aspx
That blog post talks about Kerberos authentication. Service Principal Names don't exist in NTLM, and neither does the concept of delegation. So Kerberos is involved somehow. You can use NTLM to the web server and then from the web server to SQL Server, but you have to either switch credentials, or re-authenticate at the web server. Otherwise, what you're saying goes directly against what Microsoft has published in other places and what is generally understood about NTLM. For instance:
Yes I agree it does. I understand where you are coming from....the funny part....the Microsoft guys told us not to look at what they had published. Stupidest thing I ever heard. We were trying to follow what they had posted time and again and were having problems which prompted our call to MS. Now If I can find the eMails they sent us on this issue - I will get back to this thread in more detail on what was indicated to us.
Viewing 15 posts - 16 through 30 (of 89 total)
You must be logged in to reply to this topic. Login to reply