kerberos delegation works but impersonation does not work, why??

Question

I finally got Kerberos delegation working to display a double-hop report on our SPF 2010 test server.

The report displays fine, gets the data.

The problems are:

1) I had only Windows authentication enabled for the Default Web Site and the SharePoint - 80 site -- additionally enabling asp.net impersonation for the authenticated user worked within the SPF server per se but not on any clients, which then refuse to let me see the reports, giving this error message:

• An error has occurred during report processing. (rsProcessingAborted)
  • Cannot create a connection to data source 'EMPOWERDATA'. (rsErrorOpeningConnection)
    • Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

      If I turn off asp.net impersonation the reports work but it's very clear by using Report Builder's ability to show the 'logged-in' user on the report that it's the sp_serviceapps account logged in and using the report when we really should see the existing domain user?? This is how the delegation is configured btw, this account has the spn and then the 2nd hop is to the SQL server containing the report data.

      I need to know how Impersonation should be configured -- I had it to 'authenticated user' -- should it be to a specific user??

    • My understanding is that 'authenticated user' is whoever is the Windows user using the SharePoint site, am I incorrect?? Or do we need to find/use some other way to set permissions etc. for viewing reports??

      Thank you, Tom

    
    
  
  

Answer 1

Hi Tom,

Thanks for your post.

I am trying to involve someone familiar with this topic to further look at this issue.

Best Regards,

Kelly Chen

Answer 2

Hi Tom,

Thanks for your post.

I am trying to involve someone familiar with this topic to further look at this issue.

Best Regards,

Kelly Chen

Thank you, I'm sure many people besides me would appreciate a good useful answer to this issue.

Thank you, Tom

Answer 3

I have this exact same issue also.  

I have created an application  page in SharePoint that connects to a Database (seperate to the SharePoint Farm but on the same domain) and displays the data in a gridview.  

Works fine if I use either a SQL Login in the connection string or if I use runwithelevatedprivileges in the code and grant SQL permissions to the application pool account.  

When we try and use Integrated Security=True and no elevated privileges I get Login failed for Anonymous user.

We have Kerberos enabled and all the SPN's set up. Why are the users credentials not being used to access the other Database?

Thanks Guys!

Answer 4

Works fine if I use either a SQL Login in the connection string or if I use runwithelevatedprivileges in the code and grant SQL permissions to the application pool account.  

When we try and use Integrated Security=True and no elevated privileges I get Login failed for Anonymous user.

What SQL permissions did you grant to the app pool account?? -- that made impersonation work properly??

We're willing to try this. :)

Thank you, Tom

Answer 5

Hi Tom

 To clarify, impersonation didn't work. The access to the dB was granted to the application pool service account. Which means if you can access the page you can access the database. We want SharePoint to impersonate as the user. Run with elevated Privileges makes the code run as the app pool account not the user.

Cheers

Greg

Answer 6

To clarify, impersonation didn't work. The access to the dB was granted to the application pool service account. Which means if you can access the page you can access the database. We want SharePoint to impersonate as the user. Run with elevated Privileges makes the code run as the app pool account not the user.

I know we want SharePoint to impersonate as the user. I thought you meant that by giving additional or specific SQL permissions to the app pool account, you had gotten impersonation to succeed, and that when these additional or specific SQL permissions were removed from the app pool account, impersonation did not succeed.

Am I missing something?? I probably am. :)

Thank you, Tom

Answer 7

Hi Tom

Today I've been working with the network guys and we've done all the steps in Russ Maxwells excellent post

http://blogs.msdn.com/b/russmax/archive/2009/10/20/configuring-kerberos-authentication-in-sharepoint-2010-part-1.aspx 

However its still not working for us.  May have to escalate a call to Microsoft

Cheers

Greg 

Answer 8

Update:

We appear to have found the problem,  we have duplicate SPN's using both the Farm account and the application pool account.  This causes Kerberos to fail.  By removing the duplicates and only using the application pool account for each web application Kerberos started working.

See the "Kerberos authentication and duplicate/missing SPN issues" section of this article:

http://technet.microsoft.com/en-us/library/gg502606.aspx

Note: It appeared to work gradually across users, maybe the browsers cache the SPN...? 

hope that helps someone

Greg

Answer 9

Update:

We appear to have found the problem,  we have duplicate SPN's using both the Farm account and the application pool account.  This causes Kerberos to fail.  By removing the duplicates and only using the application pool account for each web application Kerberos started working.

I used DelegConfig 2.x beta to test the Kerberos delegation etc. and it did not report any duplicate SPNs, in fact it found duplicate SPNs I think. Does it matter which server I check the SPNs from??

I needed SPNs for the SPF services account and for the SQL services account...

I'll try to check on this again...

Thank you, Tom

Answer 10

Hello Tom,

By the description, I assume you are running SharePoint and Report Server on different servers. Is your RS correctly setup to use Kerberos authentication? To me, it looks like Report Server is not able to receive end user credentials when using impersonation and hence you see the error Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Please look at the following articles and ensure the Kerberos configuration is correct -

• http://blogs.msdn.com/b/martinkearn/archive/2007/04/23/configuring-kerberos-for-sharepoint-2007-part-1-base-configuration-for-sharepoint.aspx
• http://blogs.msdn.com/b/martinkearn/archive/2007/04/27/configuring-kerberos-for-sharepoint-2007-part-2-excel-services-and-sql-analysis-services.aspx
• http://technet.microsoft.com/en-us/library/cc263449.aspx
• http://technet.microsoft.com/en-us/library/ee355094(v=ax.50).aspx

Also, can you please post relevant section for identity impersonate (after removing the specifics about the domain and username) from your web.config?

---

Please remember to click 'Mark as Answer' on the post that helps you or click 'Unmark as Answer' if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Regards,
Nishant Shah
Microsoft Online Community Support

Answer 11

Nishant,

SharePoint Foundation and Report Server are on the SAME server.

The report on the SharePoint server is talking to another separate database server.

DelegConfig has already been used to verify delegation etc. -- the data comes back correctly.

We have a field in the report to show the user running the report -- it always shows the SharePoint application pool user instead of the Windows Authentication user -- e.g. me if I am running the report on my computer.

The report only functions off-server when identity impersonation is turned OFF -- within the server itself impersonation works correctly.

Please re-read my initial post again...

Thank you, Tom

Answer 12

Tom,

In our experience most times this issue is a result of bad Kerberos configuration (http and MSSQLSvc SPNs). I understand you may have tested with DelegConfig however this being a Forum thread we have no way to look at any data / logs. So, please check -

• SharePoint Server's machine account and app pool identity account (of SharePoint Site hosting the RS webpart) are trusted for delegation.
• Ensure rsreportserver.config file has support for Kerberos enabled (Add RSWindowsNegotiate to the AuthenticationTypes section)
• Is RS 2008 service account set to Network Service or domain account?

Also, post the output of below commands from SPF 2010 server (after trimming out any environment specific info)

• setspn -L domain\account where domain\account is the app pool identity account (of SharePoint Site hosting the RS webpart)
• setspn -X

---

Please remember to click 'Mark as Answer' on the post that helps you or click 'Unmark as Answer' if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Regards,
Nishant Shah
Microsoft Online Community Support

Answer 13

Not sure what you mean by SharePoint Foundation's "machine account".

SPF's app pool (domain\sp_serviceapps) is trusted for delegation.

Reporting Services account is the same as SPF's app pool account, domain\sp_serviceapps

<Authentication>
<AuthenticationTypes>
<RSWindowsNegotiate/>
<RSWindowsKerberos/>
<RSWindowsNTLM/>
</AuthenticationTypes>

the above is from rsreportserver.config

setspn -x shows no duplicate spns

setspn -l lists for domain\sp_serviceapps like this:

HTTP/foo

HTTP/foo.domain.local

However the desired report data per se is on a server called mlsnet -- perhaps I need a SPN set for domain\sp_serviceapps on HTTP\mlsnet and HTTP\mlsnet.domain.local -- I will try this today...see if I get duplicate spn errors or not...

Any other suggestions??

Thank you, Tom

Answer 14

machine account = computer account. In addition to setting the SPNs for each of your service accounts, you also need to trust each of the computer accounts and some of the service accounts for delegation. 

• SharePoint Server(s) (Computer Account)
• SQL Server(s) (Computer Account)
• Farm Service Account (User Account)
• PortalAppPool (User Account)

Also, confirm if SQL Service account is correctly configured with MSSQLSvc SPNs.

• MSSQLSvc/mosssql:1433

• MSSQLSvc/mosssql.mydomain.com:1433

If your RS needs to authenticate against mlsnet server via HTTP service you need those SPNs. Sometimes setting the host header for the website as 'A Record' instead of 'CNAME' helps; also ensure authentication mode of RS is set to “Windows Authentication” and NOT “Trusted Account”...

---

Please remember to click 'Mark as Answer' on the post that helps you or click 'Unmark as Answer' if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Regards,
Nishant Shah
Microsoft Online Community Support

Answer 15

machine account = computer account. In addition to setting the SPNs for each of your service accounts, you also need to trust each of the computer accounts and some of the service accounts for delegation. 

• SharePoint Server(s) (Computer Account)
• SQL Server(s) (Computer Account)
• Farm Service Account (User Account)
• PortalAppPool (User Account)

Also, confirm if SQL Service account is correctly configured with MSSQLSvc SPNs.

• MSSQLSvc/mosssql:1433

• MSSQLSvc/mosssql.mydomain.com:1433

It would help a lot if you used SPF terminology instead of MOSS terms.

We have the following accounts:

SPF services and pools are all with sp_servicesapp except central admin which is somehow sp_farm

server FOO -- SPF server -- 

server MLSNET -- another SharePoint server which contains the SQL server from which server FOO is retrieving data using an *.rdl document

sql_services_prod -- the SQL services account on mlsnet which does have its correct MSSQL SPNs configured, does this account require HTTP SPNs??

sharepoint web services root is configured as LocalService if it is started

I don't like guessing at what must have SPNs and delegation trust...hard to know what's what where etc.

The MOSS terminology makes it hard for me to match up what we have in our environment with your responses...

Thank you, Tom

Answer 16

Above mentioned blog would clarify your doubts about SPNs if you had a chance to look at that, however if not useful then your question requires a more in-depth level of support which is not feasible thru Forums.
Please visit the below link to see the various paid support options that are
available to better meet your needs: http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone.
If you are a MSDN / TechNet subscriber, you can also contact our support by
using your free support incidents.

However, other members of the community may still have encountered the issue you're seeing,
and have a solution to offer!

---

Please remember to click 'Mark as Answer' on the post that helps you or click 'Unmark as Answer' if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Regards,
Nishant Shah
Microsoft Online Community Support