I am running into a weird issue with a new SQL Reporting Services server I built. I installed SQL Reporting 2014 on Windows Server 2012 R2 and configured Kerberos, but the site is extremely slow. After some reconfiguration and log captures I have determined the issue has to do with the Kerberos setup but it is an exact replica of a Windows Server 2008 R2 server we currently have and it does not have these issues.

The error I see while using Wireshark is KRB Error: KRB5KDC_ERR_BADOPTION NT Status: STATUS_NO_MATCH. When I drill down the into the error I can see the kerberos string is testprjmnmtreports14.company.com, which is the URL we are using to access the site. I made sure to add that name as an SPN for the service account that is running SQL Reporting Services, however I still receive the error.

Then I tried configuring the site to run without a hostheader, so I accessed the site with the server name ECTSTSQLRS5 and the site works perfectly fine, no errors are reported either. So it seems I have isolated the issue down to Kerberos but I am not sure how to resolve it. Here is some more information about my environment:

• DNS/URL used: testprjmnmtreports14.company.com
• Server Name (FQDN): ECTSTSQLRS5.company.int
• AD Domain Name: company.int
• Server Version: Windows Server 2012 R2
• AD Functional Level: 2008 R2

I also have the following SPNs set for my SQL service account:

http/testprjmngmtreports14.company.com
http/testprjmngmtreports14
http/ECTSTSQLRS5.COMPANY.INT
http/ECTSTSQLRS5

As you can see I am trying to use a .com address but my AD domain is .int which I think is the issue, but I do not have the same problem on my other server that is running Windows Server 2008 R2. 

Has anyone see this issue before? What do I need to do to allow my new site on 2012 R2 to work with this DNS Alias?

Thanks,
Brandon

Hi

Quote from there; Kerberos errors in network captures

The most common scenario is a request for a delegated ticket (unconstrained or constrained delegation). You will typically see this on the middle-tier server trying to access a back-end server. There are several reasons for rejection:

1. The service account is not trusted for delegation

2. The service account is not trusted for delegation to the SPN requested

3. The users account is marked as sensitive

4. The request was for a constrained delegation ticket to itself (constrained delegation is designed to allow a middle tier service to request a ticket to a back end service on behalf on another user, not on behalf of itself).

It looks like I solved the issue, but am not sure of the long term effects of what I changed. 

Basically what I did was change the delegation settings on the AD service account i am using from "Trust this user for delegation to specified service only"/"Any authentication protocol" to "Trust this user for delegation to any service (Kerberos only)." Because I was unable to add the DNS name I wanted to use to the list services since there isn't an AD object I tried switching the setting to use any service.

What is the downside to making this change? We are using Kerberos to pass the identity of a user from a website to a SQL database. 

Hiya,

from the top of my head, what you would need in order to authenticate using Kerberos.

SPN:
HTTP/testprjmnmtreports14.company.com <IIS AppPoolAccount/SQL Reporting Service Account>
HTTP/testprjmnmtreports14 <IIS AppPoolAccount/SQL Reporting Service Account>

MSSQLSVC/ECTSTSQLRS5.company.int <SQL Reporting Service Account>
MSSQLSVC/ECTSTSQLRS5.company.int <SQL Reporting Service Account>

Delegation:
Done on your app pool account. Basically delegation is done when you need to pass the authentication ticket to another resource.
You should use the; Trust this user for delegation to specified service only -> Use any Authentication Protocol. Select your MSSQLSVC and the HTTP service. (Found when searching for your service account objects).

To my knowledge, there isn't really any downside your current configuration. Your using Unconstrained Delegation, rather than Constrained Delegation. Some application only support Constrained Delegation, as far as I know.

Configuration:
you need to change/verify that your reporting services server config file is configured to use Kerberos/Negotiate. RSReportServer.config file.

Reference:
http://blogs.technet.com/b/rob/archive/2011/11/23/enabling-kerberos-authentication-for-reporting-services.aspx

• Edited by Jesper Arnecke Friday, February 13, 2015 7:37 AM
• Proposed as answer by Vivian_WangMicrosoft contingent staff, Moderator Friday, February 13, 2015 8:18 AM

Hi,

In addition to others, you could refer to this article for more detail information about managing Kerberos Authentication Issues in a Reporting Services Environment

http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/SSRSKerberos.docx

Meanwhile, i would suggest you may ask in SQL Server forums:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

Regards.

The issue I run into when I try to add the services to the delegation tab is that "testprjmnmtreports14" doesn't exist because there is no computer object for with that name, ".company.com" is not the name of our AD domain, so I am not sure how I can add that service. 

I think I will need to continue unconstrained delegation if I am not able to figure out how to add this service or configure Windows Server 2012 R2 to ignore this error like Windows Server 2008 R2 does.

Hi,

How did you configure the SPNs?

Please refer to this article about implement Kerberos Delegation with SSRS

http://sqlmag.com/sql-server-reporting-services/implement-kerberos-delegation-ssrs

I am not sure if you could ignore this error, maybe you could ask in SQL server forums for technical support.

Regards.

The SPNs I set are:

http/testprjmngmtreports14.company.com
http/testprjmngmtreports14
http/ECTSTSQLRS5.COMPANY.INT
http/ECTSTSQLRS5

But then I tried using constrained delegation and that is what causing this error message. I haven't confirmed it yet but I think this issue is outside of SQL at this point. Once I use unconstrained delegation my site works properly.

Hi,

Sorry for the delay reply.

So if you use the Kerberos unconstrained delegation, it is working with no problem?

Computers that are running Windows Server 2008 support two modes of delegation: Kerberos unconstrained delegation and Kerberos constrained delegation.

The constrained delegation extension allows a service to obtain service tickets (under the delegated user's identity) to a restricted list of other services running on specific servers on the network after it has been presented with a service ticket, which may be a service ticket obtained through protocol transition.

If you use the Kerberos constrained delegation, you must then manually specify each service eligible for delegation as the article mentioned.

Regards.