SQL Server Reporting Service 2008 R2 - The request failed with HTTP status 401: Unauthorized.

Question

I got the error "The request failed with HTTP status 401: Unauthorized." when I call the Reporting Service Web Service.

The code is very simple:

// Initialise the reporting service Web Service

this.RSWebService = new ReportingService2010();

// Pass windos authentication credentials to Web Service

this.RSWebService.Credentials = System.Net.CredentialCache.DefaultCredentials;

// List children

List<CatalogItem> reports = this.RSWebService.ListChildren(folder, false).ToList();

The SQL Server is installed on a different machine (say server A) from the Web server (say server B). We are using Windows Server 2003.

The code work fine for all the scenario below:

i) Run locally (XP) to call the web service on the sql server

ii) Provide the Network Credentials with the username, password, and domain when calling the web service

iii) If the web server and sql server is using same server (i.e. not distributed)

I'm using windows authentication and impersonation. Any idea?

Answer 1

When I had this error message recently, it was just after we had moved to new webfarms. We re-started the webfarm overnight and in the morning everything was back to normal. So I've no idea what caused the error or what it means, but its a quick and easy solution, although you may have to do it out of hours. . .

Answer 2

Hi Raymond,

If I understand correctly, the issue only happens in the environment that the web server is different with the SQL Server Reporting Services server. This means the web server is a remote server of the Report Server. Windows Integrated authentication is used in this case. When running the web page, the 401 error occurs.

If so, it is actually a double hop issue. The double-hop problem will usually only be an issue to those of you who write some sort of web-based code (a web application or web service) that uses impersonation.

To fix the issue, we can configure kerberos authentication for the web application and the SQL Server Reporting Services.

Below is a great article that has delivered many solutions to fix the issue you met:
http://odetocode.com/articles/216.aspx

Additionally, you can read the following article to know more about double-hop:
http://blogs.technet.com/b/askds/archive/2008/06/13/understanding-kerberos-double-hop.aspx
http://blogs.msdn.com/b/knowledgecast/archive/2007/01/31/the-double-hop-problem.aspx

If you have any more questions, please feel free to ask.

Thanks,
Jin Chen

---

Jin Chen - MSFT

Answer 3

Hi Raymond,

If I understand correctly, the issue only happens in the environment that the web server is different with the SQL Server Reporting Services server. This means the web server is a remote server of the Report Server. Windows Integrated authentication is used in this case. When running the web page, the 401 error occurs.

If so, it is actually a double hop issue. The double-hop problem will usually only be an issue to those of you who write some sort of web-based code (a web application or web service) that uses impersonation.

To fix the issue, we can configure kerberos authentication for the web application and the SQL Server Reporting Services.

Below is a great article that has delivered many solutions to fix the issue you met:
http://odetocode.com/articles/216.aspx

Additionally, you can read the following article to know more about double-hop:
http://blogs.technet.com/b/askds/archive/2008/06/13/understanding-kerberos-double-hop.aspx
http://blogs.msdn.com/b/knowledgecast/archive/2007/01/31/the-double-hop-problem.aspx

If you have any more questions, please feel free to ask.

Thanks,
Jin Chen

---

Jin Chen - MSFT

Thanks Jin. I believe this is the cause of the problem as well.

I tried to register the SPN but unfortunately it still doesn't work. I'm not sure whether I'm using the correct SPN name.

I have 3 reporting server instance on the SQL box. So the Web Service URL is like http://Server:80/VirtualDirectoryDev, http://Server:80/VirtualDirectoryTest  and http://Server:80/VirtualDirectoryStaging .

What's the SPN I need to register?

I tried

Setspn -a http/Server:80/VirtualDirectoryDev domain\account

and

Setspn -a http/aerver:80 domain\account.

But both of them are not working :(

Answer 4

Hi Raymond,

What about using the following command:
    Setspn -A HTTP/aerver domain\account
and
    Setspn -A TTP/aerver.<domain name>.com domain\account

Please make sure the web application is running under a domain account or the Network Services account. The ASP.NET impersonate is enabled. The authentication is Windows Integrated mode.

A little confused thing is that why the 3 Reporting Services instances use the same port? Please make sure all of them works.

Thanks,
Jin Chen

---

Jin Chen - MSFT

Answer 5

Hi Raymond,

What about using the following command:
    Setspn -A HTTP/aerver domain\account
and
    Setspn -A TTP/aerver.<domain name>.com domain\account

Please make sure the web application is running under a domain account or the Network Services account. The ASP.NET impersonate is enabled. The authentication is Windows Integrated mode.

A little confused thing is that why the 3 Reporting Services instances use the same port? Please make sure all of them works.

Thanks,
Jin Chen

---

Jin Chen - MSFT

Thanks Jin. I'll give it a try.

We are using integrated windows authentication in IIS and no anonymous access. The web application is using impersonation. The reporting service is running under a service account.

Say I change the port to be Dev 1101, Test 1201 & Staging 1301. Also each of them will still have their own virtual directory name. So I don't to specify the port number as well as the virtual directory at all?

Also, I saw that someone said the code below as well:

this.RSWebService.PreAuthenticate = true;

Is that required?

 

 

Answer 6

Hi RaymondLui, did you ever get to the bottom of this?  I seem to be having a similar issue: http://stackoverflow.com/questions/11245336/soaphttpclientprotocol-readresponse-the-request-failed-with-http-status-401-u