Logo 
Search:

Sharepoint Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

Problem integrating SharePoint with Reporting Services across different servers using Kerberos

  Asked By: Joseph    Date: Nov 23    Category: Sharepoint    Views: 2090

Apologies for the long post but wanted to include as much detail as possible...

Edit - Sorry just realised I hadnt mentioned we are using SQL Server 2005 SP3

We have a problem with our SharePoint Reporting Services integration, we are using Kerberos authentication and our SharePoint installation and SQL Server databases are on separate servers. I have listed details of our setup below and also the symptoms we are experiencing....

Setup details

ServerA (SharePoint Server) Windows Server 2003 R2 Enterprise x64 Edition SP2

ReportServerIntegrated (web application) - http://reports.FQDN:6666/reportserverintegrated
REPORTS (web site)
TCP Port = 6666
ReportServerAppPool (application pool)
Admin\fadmin (app pool identity)

Sharepoint – portal80 (web site) – http://portal
TCP Port = 80
SharePoint – portal80 (application pool)
Admin\fadmin (app pool identity)

Sharepoint Central Administration v3 (web site) - http://ServerA:10011/
TCP Port = 10011
SharePoint Central Administration v3 (application pool)
Admin\dbadmin (app pool identity)

Reporting Services also installed on ServerA but set up to use databases on ServerB

Windows Services
SQL Server Reporting Services – Running as Admin\dbadmin
Windows SharePoint Services Administration – Running as Admin\dbadmin
Windows SharePoint Services Search – Running as Admin\fadmin
Windows SharePoint Services Timer – Running as Admin\dbadmin
Windows SharePoint Services Tracing – Running as Admin\fadmin
Windows SharePoint Services VSS Writer – Running as Admin\dbadmin
Office Document Conversions Launcher Service – Running as Local System
Office Document Conversions Load Balancer Service – Running as NT AUTHORITY\Local Service
Office SharePoint Server Search – Running as admin\fadmin

Note - We tried changing the 2 office services above from using system accounts to using admin\fadmin and admin\dbadmin but this didnt help.

ServerB (SQL Server) Windows Server 2008 Enterprise x64 SP2

SharePoint content databases
SharePoint Config databases
Report Server databases (ReportServer_Integrated and ReportServer_IntegratedTempDB)

Admin\dbadmin has db_owner rights on all above databases.
Admin\fadmin has db_owner (or WSS_Content_Application_Pools) access to all sharepoint databases but no access to report server databases.

Windows Services
SQL Server – Running as Admin\dbadmin
SQL Server Analysis Services – Running as Admin\dbadmin

ServerC (Domain Controller) Windows Server 2003 Enterprise x64 Edition SP2 (not R2)

SPNs

Assigned to admin\dbadmin

MSOLAPSvc.3/ServerB
MSOLAPSvc.3/ServerB.FQDN
MSSQLSvc/ServerB.FQDN:1433
MSSQLSvc/ServerB:1433

Assigned to admin\fadmin

HTTP/ServerA.FQDN:10011
HTTP/ServerA:10011
HTTP/REPORTS:6666
HTTP/REPORTS.FQDN:6666
HTTP/ServerA.FQDN
HTTP/ServerA
HTTP/mysites
HTTP/mysites.FQDN
HTTP/portal
HTTP/portal.FQDN

Symptoms

SharePoint seems to be working ok – We can create sites/pages etc... We can also upload reports and data connections. However if we try to deploy a report and data connections from Visual Studio (from ServerB) we get the following error...

Server was unable to process request. ---> The request failed with HTTP status 401: Unauthorized. (System.Web.Services)

So we have uploaded a report from SharePoint and uploaded the Data Connections, however when we try to map the report to the data connections (i.e. ‘Manage Data Sources’) we get the following error...

An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode.

It is as though SharePoint cannot talk to the report server (by which I presume it means the report server databases on ServerB).

To further substantiate this when we log into central administration and click ‘set server defaults’ under ‘Reporting Services’ on the ‘Application Management’ tab, we get the same error...

An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode.

We have configured ‘Manage Integration Settings’ and ‘Grant Database Access’ as follows:

Report Server URL - http://reports.FQDN:6666/reportserverintegrated
Authentication Mode – Windows Authentication

Now if we delve into the world of Kerberos, using the klist tool I can see the tickets that are being created when I try to ‘Manage Data Sources’ (I’ve purged all tickets first)

c:\Program Files>klist purge
(now click ‘manage data sources)
c:\Program Files>klist

Current LogonId is 0:0x1a8ecf6

Cached Tickets: (3)

#0> Client: my current login @ FQDN
Server: krbtgt/ FQDN @ FQDN
KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
Ticket Flags 0x60a00000 -> forwardable forwarded renewable pre_authent
Start Time: 12/7/2009 10:03:52 (local)
End Time: 12/7/2009 20:03:52 (local)
Renew Time: 12/14/2009 10:03:52 (local)
Session Key Type: RSADSI RC4-HMAC(NT)


#1> Client: my current login @ FQDN
Server: krbtgt/ FQDN @ FQDN
KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
Ticket Flags 0x40e00000 -> forwardable renewable initial pre_authent
Start Time: 12/7/2009 10:03:52 (local)
End Time: 12/7/2009 20:03:52 (local)
Renew Time: 12/14/2009 10:03:52 (local)
Session Key Type: RSADSI RC4-HMAC(NT)


#2> Client: my current login @ FQDN
Server: HTTP/ServerA. FQDN @ FQDN
KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
Ticket Flags 0x40a40000 -> forwardable renewable pre_authent ok_as_delegate
Start Time: 12/7/2009 10:03:52 (local)
End Time: 12/7/2009 20:03:52 (local)
Renew Time: 12/14/2009 10:03:52 (local)
Session Key Type: RSADSI RC4-HMAC(NT)

So we can see the HTTP ticket but cannot see an MSSQLSvc ticket which is where the problem seems to be.

Below are some of the Kerberos errors we are seeing in the System event viewer on ServerB...

A Kerberos Error Message was received:
on logon session FQDN\my current login
Client Time:
Server Time: 11:8:30.0000 12/7/2009 Z
Error Code: 0x19 KDC_ERR_PREAUTH_REQUIRED
Extended Error:
Client Realm:
Client Name:
Server Realm: admin
Server Name: krbtgt/admin
Target Name: krbtgt/admin@admin
Error Text:
File: e
Line: 98a
Error Data is in record data.

A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 11:0:6.0000 12/7/2009 Z
Error Code: 0xd KDC_ERR_BADOPTION
Extended Error: 0xc00000bb KLIN(0)
Client Realm:
Client Name:
Server Realm: FQDN
Server Name: ServerB$@FQDN
Target Name: ServerB $@ FQDN @ FQDN
Error Text:
File: 9
Line: e2d
Error Data is in record data.

We are also getting lots of audit failures in the Security event viewer on ServerA as follows

Object Open:
Object Server: Security Account Manager
Object Type: SAM_ALIAS
Object Name: DOMAINS\Account\Aliases\000003F1
Handle ID: -
Operation ID: {0,441511871}
Process ID: 440
Image File Name: C:\WINDOWS\system32\lsass.exe
Primary User Name: ServerA$
Primary Domain: ADMIN
Primary Logon ID: (0x0,0x3E7)
Client User Name: dbadmin
Client Domain: ADMIN
Client Logon ID: (0x0,0x1A34CE8C)
Accesses: AddMember
RemoveMember
ListMembers
ReadInformation

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Jonathan Scott     Answered On: Nov 23

Update - I think we have solved it.

I installed a tool called DelegConfig (www.iis.net/.../default.aspx?tabid=34&g=6&i=1434)

This flagged up that one of the SPNs was incorrect - I had included the port number. Once I deleted this SPN and set it up again without the port number (restarted iis etc...) it worked.

 

Related Topics:




 

Related Post