Configuring Kerberos Authentication

  • Kevin Rathgeber

    SSC Veteran

    Points: 270

    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.

  • K. Brian Kelley

    SSC Guru

    Points: 114445

    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

  • K. Brian Kelley

    SSC Guru

    Points: 114445

    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

  • SQL Thetan

    Say Hey Kid

    Points: 664

    Is it the SSPI which attempts to fall back to NTLM if Kerberos authentication fails?

  • Carlton Leach

    SSCarpal Tunnel

    Points: 4092

    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..

  • james.menke

    Newbie

    Points: 3

    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.

  • K. Brian Kelley

    SSC Guru

    Points: 114445

    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

  • K. Brian Kelley

    SSC Guru

    Points: 114445

    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

  • dave-dj

    SSChampion

    Points: 11230

    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)

  • K. Brian Kelley

    SSC Guru

    Points: 114445

    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

  • dave-dj

    SSChampion

    Points: 11230

    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)

  • dave-dj

    SSChampion

    Points: 11230

    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&gt;

    <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)

  • dave-dj

    SSChampion

    Points: 11230

    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.

    http://www.microsoft.com/DownLoads/details.aspx?FamilyID=99b0f94f-e28a-4726-bffe-2f64ae2f59a2&displaylang=en

    which I found on another useful site: http://callumhibbert.blogspot.com/2009/02/kerberos-delegation-and-sql-reporting.html

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • jswong05

    Hall of Fame

    Points: 3503

    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
    😛

  • K. Brian Kelley

    SSC Guru

    Points: 114445

    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