Home » SharepointRSS

Kerberos issue with SQL Reporting Services 2005 on Server 2003 R2

Hi Guys,

apologies if this is the incorrect forum, so moderators, feel free to move it to SQL/IIS/SharePoint as appropriate... [Windows Server Security moderator pushed me this direction]


I have a test environment that I'm trying to get SQL Reporting Services 2005 SP3 working in integrated mode with SharePoint 2007 SP2.

The environment is all in VMWare, running Server 2003 R2 x86 and is layed out like this:

SERVER A:
AD/DNS/DHCP

SERVER B:
SQL 2005 SP3 CU8

SERVER C:
SharePoint 2007 SP2 Dec 09 CU
- Central admin on port 9000
- SSP on port 9001
- MySite on port 81
- Main Content on port 80
SQL Reporting Services 2005 SP3 CU8
- Reporting Service website on port 82

SERVER D:
SharePoint 2007 SP2 Dec 09 CU
- Central admin on port 9000
- SSP on port 9001
- MySite on port 81
- Main Content on port 80
SQL Reporting Services 2005 SP3 CU8
- Reporting Service website on port 82

Through the use of DNS and (SharePoint) Alternate Access Names, SERVER D is used to deliver the Main Content in SharePoint and the Reporting Service website.  SERVER C is used to deliver the Central Admin, SSP and MySite.

I've set up SPN's for the SharePoint App Pools, using the following:
[main content] setspn -S HTTP/SERVERA DOMAIN\AppPoolUserA
               setspn -S HTTP/SERVERA.FQDN DOMAIN\AppPoolUserA
               setspn -S HTTP/SERVERB DOMAIN\AppPoolUserA
               setspn -S HTTP/SERVERB.FQDN DOMAIN\AppPoolUserA
[report server]setspn -S HTTP/SERVERA:82 DOMAIN\SQLAppPoolUser
               setspn -S HTTP/SERVERA.FQDN:82 DOMAIN\SQLAppPoolUser

However I'm running into the issue where I (err SharePoint) can't authenticate with the Report Server web instance when attempting to view a report in SharePoint.
Remotely I get the generic SharePoint error. If I try from SERVER D then SharePoint reports that it got a 401: Unauthorized error.
If I try to connect (manually in the browser) locally to the Report Server using:
http://SERVERD:82/ReportServer/ - Authentication failes
http://localhost:82/ReportServer/ - I get a list of site collections
http://IP_ADDRESS_OF_SERVERD:82/ReportServer/ - I get a list of site collections.


Connecting remotely produces the same result.

This is similar to this MS KB article (http://support.microsoft.com/kb/871179/)

However, I'm sure I've managed to follow its advice, but it's still not working.

Help!  Anyone got any ideas?

Thanks
Craig
 

6 Answers Found

 

Answer 1

Hello,

Never can tell the initial problem til you start troubleshooting...here's some ideas-

First I'd check the IIS settings for the websites.  Open IIS, click on the "Web Sites" node and see the website ID numbers for the different web apps you are hosting on the server.

Open a cmd console prompt and go to C:\Inetpub\AdminScripts.
From this location run "cscript adsutil.vbs get w3svc\<websiteID>\ntauthenticationproviders"

The command shoudl return "Negotiate,NTLM".  If you get a not set message or just "NTLM", then run:
'cscript adsutil.vbs set w3svc\<websiteID>\ntauthenticationproviders "Negotiate,NTLM" '

Check to be sure your SSRS app pool account is in the IIS_WPG group for the server. 

Check in Central Admin and see what authentication type you are using for SSRS.  You may need to flip this from Trusted Account to Windows or vice versa, can't remember presently.

One other thing you can do is stop the services  on the server  that you're not using.  Since you are not serving SharePoint content from server C, you could stop those services.  Also, if you get authentication failures trying to connect using the FQDN or NetBios name of the SSRS server, you may need to update the hosts file and check if you are being stopped by the loopback check.

Usually when this is not working, there is one setting that is not quite right and you'll have to find that one issue  to correct.  I'd also recommend using the same app pool account for SSRS and SharePoint in integrated mode.  Sometimes the port numbers are dropped by clients when requesting Kerberos tickets so having the same app pool will eliminate the duplicate SPN issue and the port dropping issue.

Advertise

 

Answer 2

Hi Dan,

thanks for that.  Most I've covered at some point along the way.  I'm now working through this with MS Support.

Couple of things though:

I've been using "cscript adsutil.vbs get w3svc\<websiteID>\root\ntauthenticationproviders".  Was that just a type-o on your part?

I'm also running the Report Server App pool using the same account as the SQL Server account (which I think was some advice I had seen).  Can you confirm that you've seen advice on using the same App Pool account as SharePoint? (I'll raise this with MS Support today as well)

Thanks!
Craig
 

Answer 3

Hi Dan,

just tried a couple of things:

Switched the Report Server App Pool to use the same account as the SharePoint Content App Pool.  No luck (get some security related stack trace error...)

Switched the Report Server process to use the same account as the SharePoint Content App Pool (at the same time as above).  Same error as above.

Oh well, hopefully MS Support will figure it out today....

Later'ish
Craig
 

Answer 4

OK, turns out it was kerberos  getting confused over ports and DNS aliases.

The resolution was to put the IIS Web App for the Report server  on a host-header (with appropriate DNS alias), update SPNs and SharePoint SSRS config to match and Bobs-your-Uncle... it works.

Broke:
SERVERNAME:80 + INTRANET:80 -> SharePoint [no host-headers]
SERVERNAME:82 + INTRANET:82 -> SSRS [no host-headers]

Working:
SERVERNAME:80 + INTRANET:80 -> SharePoint [no host-headers]
REPORTING:80 -> SSRS [host-header: reporting]

Couple of things to note:

Even with the Report Server on port 82, the SPN for the report server didn't need port 82 to work when no DNS aliases are involved, it "just works".  e.g. my test/dev environments are a single WFE with SSRS on port 82, no DNS aliases, kerberos turned on and it all works.

Oh well, when I move SSRS to it's own server (bound to happen) I wont need to change any of the config, just repoint the DNS alias!

 

Big ups to Iori from MS Support for getting through this with me.

Later'ish
Craig

 

Answer 5

Hi Dan,

just tried a couple of things:

Switched the Report Server App Pool to use the same account as the SharePoint Content App Pool.  No luck (get some security related stack trace error...)

Switched the Report Server process to use the same account as the SharePoint Content App Pool (at the same time as above).  Same error as above.

Oh well, hopefully MS Support will figure it out today....

Later'ish
Craig

I have the similar question, What you said helps solve my problem, Thanks for your answer!
 

Answer 6

If you have SSRS on the same box as your SharePoint farm, then you don't have a double hop issue  which is why it "just works".  When you move SSRS to a different machine, you may need to add the DNS and SPNs since you'll be introducing a double hop scenario. 

Good feedback on the DNS, I had seen that answer in another forum but slept since then and didn't think of that. 

One final point is that some browsers don't send port number with request for Kerberos ticket which is why the port number is not always required (I think IE6 is a culprit here, there is a KB on this as well).  I think newer browsers will send the port but it is generally a best practice to use DNS so you can be sure that tickets are generated for the majority of browsers accessing your sites.

Here are some KBs that have more information on configuring SPNs:

http://support.microsoft.com/kb/929650 - http://support.microsoft.com/kb/907272 - http://support.microsoft.com/kb/908209 (IE 6 issue)

 
 
 

Sharepoint Ads

 
 
 

<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter