Kerberos issues with SSRS 2008

  • I’m having problems connecting to reporting services remotely using the fully qualified domain name or a DNS alias I have configured for SSRS. I cannot connect remotely using anything other than Ip Address. When I try I get presented with a login box which seems to refuse my credentials and the following error logged in the event log:

    A Kerberos Error Message was received:

    on logon session

    Client Time:

    Server Time: 10:40:45.0000 6/28/2011 Z

    Error Code: 0x29 KRB_AP_ERR_MODIFIED

    Extended Error:

    Client Realm:

    Client Name:

    Server Realm: xxxDomainxxxx

    Server Name: xxxService Account running SSSRSxxx

    Target Name:

    Error Text:

    File: 3

    Line: 576

    Error Data is in record data.

    I can connect on the server using IPAddress, FQDN:Port, Hostname:Port and DNSAlias:Port. FQDNS Alias:Port does not work on the server.

    I’m running Windows Server 2008 Release 2. Sql server 2008 sp2

    I have configured 4 SPN’s for reporting services:

    HTTP/hostname:port

    HTTP/FQDN:port

    HTTP/DNSAlias:Port

    HTTP/FQDNSAlias:Port

    I have configured multiple HTTP identities for report server website based on:

    IP address (there are 2 NICS but I have used the public facing one)

    FQDN

    DNS Alias

    HOSTNAME

    FQ DNS Alias

    Report server config has the following entries:

    <AuthenticationTypes>

    <RSWindowsNegotiate/>

    <RSWindowsNTLM/>

    </AuthenticationTypes>

    Does any body know why this may be occurring?

  • I forgot to add, Kerberos is working fine for single and double hop with Sql Server which is on the same box as SSRS

  • Have you tried with only NTML authentication?

    <AuthenticationTypes>

    <RSWindowsNTLM/>

    </AuthenticationTypes>

    Abhijit - http://abhijitmore.wordpress.com

  • Thanks Abhijit, NTLM only connects remotley for FQDN. DNSAlias and FQ DNS Alias. I will still need to configure for keberos though.

  • Why do you need to configure kerberos? I was going to make the same recommendation about using just NTLM. Using Windows Negotiate is a known cause of the problem you describe.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Im trying to load balance SSRS and I thought this was a prerequisite. Also I was under the impression that if I wanted to use integrated security for the SSRS data source and the source was on another server it would cause problems if kerberos was not used. Is this not the case?

  • I always recommend against using integrated security for SSRS. this means you have to give users permissions to access the database directly. Use a service account to access the SQL Server and use SSRS permissions to control access to reports.

    Since SSRS is connecting directly using the service account and not delegating user permissions, you don't need kerberos.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Hi Mark.

    This is a supported scenario, just need to go over the steps to make sure it's configured properly. While it's correct to say that using a single fixed account will work without Kerberos, this means that every user connects to the data source under the same set of credentials so you can't secure the data. That would be a particular problem if using SSAS as the source, where you would definitely want to secure the data based on roles, but it's also a problem with an RDBMS source if you wanted to implement data security with views and a user permissions table (or any other solution) because you have no idea who the user connecting to the database actually is.

    I do get the idea that you don't necessarily want to allow users direct access to DB tables, but you can easily allow access to views or procs instead and still have the benefits of per-user connections.

    Kerberos isn't that hard, honest! Here's a quick walkthrough.

    In this case you have one server with two NICs in it. I will set up some example addresses etc. to illustrate:

    Server name: ssrs.mydomain.com

    IP of NIC one: 192.168.0.100

    IP of NIC two: 10.0.1.100

    SSRS Report Manager address: /reports

    SSRS port: 8080

    SSRS service account: mydomain\svc-ssrs

    Ignoring Kerberos altogether, that would mean you could get to the Report Manager by going to any one of:

    http://ssrs:8080/reports

    http://ssrs.mydomain.com/reports

    http://192.168.0.100:8080/reports

    http://10.0.1.100:8080/reports

    Kerberos won't work in any of these cases because we haven't configured it. So, let's take the case that users on the 192.168.0.xxx subnet need to access it using Kerberos.

    First thing to do is create a DNS A record - perhaps you want to map the name "reports". Very simple, on the domain controller create that DNS entry and point it to 192.168.0.100.

    Next, create your SPNs. In this case you need to create four:

    setspn -S http/reports mydomain\svc-ssrs

    setspn -S http/reports.mydomain.com mydomain\svc-ssrs

    setspn -S http/reports:8080 mydomain\svc-ssrs

    setspn -S http/reports.mydomain.com:8080 mydomain\svc-ssrs

    Two things to note here: the SPNs have been created for the http service on the endpoint "reports", NOT ON THE SERVER NAME. And we have created the SPNs on both the default port and the specific port.

    With these created, you need to configure the service account to delegate. In Active Directory Users and Computers, open up the service account and go to the "Delegation" tab. Select the "Trust this user for delegation to any service(Kerberos only)" option and OK it. (This is unconstrained delegation which will work fine in this case).

    Then modify your rsreportserver.config file as you have already done.

    Finally, you need to set up SPNs for your data sources. Let's take the following as an example:

    Data source type: SQL Server

    Host: sql.mydomain.com

    Port: default

    Service account: mydomain\svc-sql

    You need to create the following SPNs:

    setspn -S MSSQLSvc/sql.mydomain.local mydomain\svcsql

    setspn -S MSSQLSvc/sql mydomain\svcsql

    setspn -S MSSQLSvc/sql:1433 mydomain\svcsql

    setspn -S MSSQLSvc/sql.mydomain.local:1433 mydomain\svcsql

    I usually create an SPN both with and without the port for SQL Server - it seems to be a requirement in some cases but not all and I've never got to the bottom of why.

    That should be all you need to do to make it work with a single server. If you want to load balance across two SSRS servers, you would run the service on both machines using the scale-out option in SSRS config applet, using the same service account and on the same port, and modify DNS so it points to both of those servers - but you wouldn't need to add any more SPNs or anything.

    If users on the other subnet need access as well, all you need to do in DNS is add another entry to point the address "reports" to the other IP address.

    Users then go to http://reports:8080/reports to access the service and it should use Kerberos as long as their browser recognises that address as being in the "Local Intranet" security zone (if not you need to add the address manually to that zone in the browser security settings).

    Duncan

  • Many thanks Duncan, I seem to have got it working following your steps.

    Using cnames instead of A records was probably a big part of the problem.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply