May 19, 2009 at 4:36 pm
While on the whole Kerberos subject here...Have you ever run into a problem where it appears that Kerberos appears to just stop working (not sure if it is reverting back to NTLM or not)? What happens is we can have our impersonation/delegation working fine and then all of a sudden you start getting the generic basic login screen that IE presents. It is like the web site does not receive the proper credentials (or any for that matter). The only solution we have come across so far as is to reboot the computer and then everything works fine again.
May 20, 2009 at 8:50 am
Kevin Rathgeber (5/19/2009)
While on the whole Kerberos subject here...Have you ever run into a problem where it appears that Kerberos appears to just stop working (not sure if it is reverting back to NTLM or not)? What happens is we can have our impersonation/delegation working fine and then all of a sudden you start getting the generic basic login screen that IE presents. It is like the web site does not receive the proper credentials (or any for that matter). The only solution we have come across so far as is to reboot the computer and then everything works fine again.
I've seen cases like this where, for whatever reason, the computer system stops getting the Kerberos ticket from the DC. Different reasons. Usually you also see errors in the System event log where the computer is having trouble talking to the DC.
K. Brian Kelley
@kbriankelley
May 20, 2009 at 8:51 am
Taylor (5/19/2009)
Great article!Any chance of another great article for Kerberos Delegation?
At some point, yes. Generating the screenshots is what is problematic since I'm no longer a domain admin (took a transfer to go back to being a development DBA).
K. Brian Kelley
@kbriankelley
May 20, 2009 at 9:10 am
Is it the SSPI which attempts to fall back to NTLM if Kerberos authentication fails?
June 25, 2009 at 5:38 pm
Hi Brian,
Further to this article: what services are required to be running on the SQL server?
I presume Kerberos Key Distribution Centre service has to be activated on the SQL server itself, is this correct? also is there anything else that needs to be running?
Cheers,
Carlton..
July 16, 2009 at 7:09 am
Your article tipped me off to find my solution.
I had the same exact issue as listed in this forum.
I changed the SQL Service to run as a domain administrator - Started then Stopped the service. I then changed the SQL Service to run as the original windows user that I had intended. Apparently that gave the service enough rights to generate the correct SPN.
July 16, 2009 at 7:29 am
Carlton Leach (6/25/2009)
Hi Brian,Further to this article: what services are required to be running on the SQL server?
I presume Kerberos Key Distribution Centre service has to be activated on the SQL server itself, is this correct? also is there anything else that needs to be running?
Cheers,
Carlton..
The KDC only needs to run on the domain controllers. Actually, nothing out of the ordinary for a standard member server needs to run on the SQL Server.
K. Brian Kelley
@kbriankelley
July 16, 2009 at 7:30 am
james.menke (7/16/2009)
Your article tipped me off to find my solution.I had the same exact issue as listed in this forum.
I changed the SQL Service to run as a domain administrator - Started then Stopped the service. I then changed the SQL Service to run as the original windows user that I had intended. Apparently that gave the service enough rights to generate the correct SPN.
The SPN would have been registered under the domain admin, which means it would likely be wrong. This would be a good thing to check with SETSPN -L *service account* to see.
K. Brian Kelley
@kbriankelley
March 19, 2010 at 6:29 am
Hi Brian,
Great article - Thanks.
I'm having a few issue getting this working however (although I had it working earlier this morning, I've now broken it - not sure how).
As far as I can see I've set up everying that's required, but something is clearly missing.
I'll try and give you as much information as I can.
Configuration:
- My solution invovles 2 servers SQLTEST01 and SQLTEST02.
- Both servers are trusted for delegation withing ActiveDirectory
- both have the following services running under a domain account DOMAIN\SQLTest.svc
- SQL Server (Default Instance)
- SSRS (default Instance)
- IIS 6.0
- ReportServer & Report Manager Use an application pools, running as DOMAIN\SQLTest.svc
- Website security is set to Integrated Windows Authentication
SSRS > Database Connection
I'm using SSRS with mirrored database, so I use a DNS record to point users to the correct Database server, which will hold the Principle database, which will float between the servers.
ServicePrincipalNames
I have that all working - but it creates this 'double-hop' scenario, from SSRS
So I have setup the following SPN records for the service account DOMAIN\SQLTest.svc:
Registered ServicePrincipalNames for CN=SQL Test,OU=Service Accounts,OU=Tilbury,
C=MyDomain,DC=net:
MSSQLSvc/scats_test:1433
MSSQLSvc/iris_test:1433
http/sqltest01
http/sqltest02
http/iristestrs
http/scatstestrs
http/sqltest01.MyDomain.net
http/scatstestrs.MyDomain.net
http/iristestrs.MyDomain.net
http/sqltest02.MyDomain.net
MSSQLSvc/scats_test.MyDomain.net:1433
MSSQLSvc/iris_test.MyDomain.net:1433
The scats_test and iris_test are for the 'Principle' server, which could be either SQLTEST01 or SQLTEST02.
to simply the SSRS side, I and login on to SQLTEST02 Report Server, which has the following DNS References:
- scatstestrs.MyDomain.net
- iristestrs.MyDomain.net
When running a report, where I have set the RS Data Source to use Windows Integrated Security, I get the Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' - so it's not using kerberos. I can't spot why though.
Your help - or that of anyone else who has it working would be appreciated. :ermm:
Some additional information to add:
It would appear, if I connect to my report server on SQLTEST02 using http://sqltest02/Reports/ Kerberos connection works, but if I use http://iristestrs/Reports/ i get the Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
What do I need to do to get the SPN for iristestrs working?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 19, 2010 at 12:35 pm
Are the IIS servers configured to use Negotiate,NTLM for NTAuthentication providers? Because you're working with SQLTest02 is not necessarily a sign that Kerberos is being used. Because the web server and the SQL Server are the same physical box, NTLM will work in that case. So if IIS is not accepting Kerberos authentication but only NTLM, you'd work in the first case but not the second.
One easy way to tell is to connect to the server SQLTest02 and then check the Windows security log. How you connected should be logged. Also, the use of KerbTray here would be invaluable. If you aren't seeing the tickets pop up, you aren't connecting via Kerberos.
K. Brian Kelley
@kbriankelley
March 22, 2010 at 5:26 am
Hi Brian.
I've checked both servers are using configured for Kerberos, both SQLTest01 and SQLTest02 return the following NTAuthenticationProviders:
NTAuthenticationProviders : (STRING) "Negotiate,NTLM"
I have some more information, which I hope is helpful:
1. When I checked it first thing this morning, the 'live' reporting services was running on SQLTest01.
I could logon and run reports successfully using Kerberos authentication, using all of the following:
- sqltest01
- sqltestrs.forthprots.net ('live' report server dns entry)
- iristestrs.forthprots.net ('live' report server dns entry)
- scatstestrs.forthprots.net ('live' report server dns entry)
All of these tested successfully, from my laptop logged on as my standard domain account.
I then failed-over the report server to run SQLTest02. I could run reports if I connected to sqltest02/reports, which successfully authenticated in Kerberos, but not using
- sqltestrs.forthprots.net ('live' report server dns entry)
- iristestrs.forthprots.net ('live' report server dns entry)
- scatstestrs.forthprots.net ('live' report server dns entry)
After a period of time, this then worked?? is this a ticket issue?
(I purged this tickets on the server)
2. Having switched back to sqltest01, to try and find out what is different, SQLTest 01 no longer worked in kerberos authentication?
On the sqltest01 server I purged the tickets, using kerbtray and if I logged on to sqltest01/reports, I can see a ticket pop up for MSSQLSvc/iris_test.forthports.net:1433 (my sql server data source).
Back on my laptop, when I connect to sqltest01/reports and run a report, it run the report, using kerberos authentication, but for my admin account DL.Admin(but I'm logged on as my standrard account - DL.Standard)??
I logged on to the server sqltest01 as DL.Admin, but why is it picking up when I access the report server from my laptop?
I noticed though, that a new ticket didn't appear in kerbtray. (note the report I running, runs the query from your article, to define what time of connect I have with the database)
Let me know if I not using the kerbtray right, but what I do, to test tickets are being issued, is on the machine I access report server from, I purge the tickets, and then connect.
confused and dazed - Dave
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 23, 2010 at 5:10 am
I have noticed one other problem as well, running on SQLTEST02.
After switching Reporting Services to run on SQLTest02 and connecting to it using one of my DNS entries:
- sqltestrs/reports
- iristestrs/reports
- scatsrs/reports
I get the Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
If I logon via sqltest02/reports I can run the report and it authenticates using kerberos.
I can then go back to using one of:
- sqltestrs/reports
- iristestrs/reports
- scatsrs/reports
and the report will run successfully, even after closing my web browser and launching a new web browser session?
It would appear I get a ticket for HTTP/sqltest02.forthports.net - which would possible explain why it works, but why is it not working for my dns entries?
I had to configue Reporting Services to RSWebApplication.config file <ReportServerUrl> to:
<UI>
<ReportServerUrl>http://sqltest02/ReportServer</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>
as the reporting services website is not the default website. (I also setup the host headers)
Is the config file part of the reason why it only generates a ticket for sqltest02 and not my dns references, that I have created http SPN's for?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 30, 2010 at 9:41 am
I've now resolved the issue.
For anyone else who has issues configuring Kerberos authentication, take a look at this white paper, which helped me to get to the bottom of my issues.
which I found on another useful site: http://callumhibbert.blogspot.com/2009/02/kerberos-delegation-and-sql-reporting.html
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
July 27, 2010 at 10:44 am
Harold Buckner (12/11/2008)
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.
Well written article. I followed the instruction. Set up SPN on where I can see correct SPN using SETSPN -L
My linkserver between the two hosts (SQL2005) using windows authentication (impersonation) is still getting same error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Jason
http://dbace.us
😛
July 27, 2010 at 1:20 pm
jswong05 (7/27/2010)
Harold Buckner (12/11/2008)
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.
Well written article. I followed the instruction. Set up SPN on where I can see correct SPN using SETSPN -L
My linkserver between the two hosts (SQL2005) using windows authentication (impersonation) is still getting same error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I didn't cover Kerberos delegation. Both SQL Servers will need to be configured for SQL Server Authentication. Also, the initial SQL Server contacted will need to be set up to use Kerberos delegation:
How to Implement Kerberos Constrained Delegation with SQL Server 2008
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 61 through 75 (of 89 total)
You must be logged in to reply to this topic. Login to reply