SharePoint List data source giving HTTP 401 “Unauthorized” error

The answer to this question was a series of comments. I went ahead and put it in the chat as well as an answer.

Go to the security of the server not the database and map the user to that database.

Can you recycle the application pool on the server where reporting services is running? Or reboot the IIS server if possible.

Another thing that comes to mind, when you launch this report it goes into the report server "Portal". Does this user have access to the actual report to view it? Click the details section of this "Portal" and assign this user as a content manager role.

DWH_REPORTING_USER is this an admin account on your domain?

Local admin on the server, so did you grant \COMPUTER_NAME\DWH_Reporting_User rights as a content manager in the roles section? Why dont you use a domain admin account?

So how do you access the actual report - that is your issue. Do me a favor aprem, write up a stored procedure or sql query in the first tab of reporting services and run it using that user. Meaning in the shared data source it should be using this user DWH_Reporting_user. Test the connection then write a small sql statement to retrieve some data. Run the SQL script from within RS do you see any data?

@Aprem look at the three tabs at the top of rs, its the first tab to define datasets, this is where you can create an SQL query (and run it using the red exclamation mark). – JonH 20 mins ago

i defined a new dataset as "select top 1000 * from mytable", i rebuilt the project, deployed it, and now what do id o? – Артём Царионов 16 mins ago

In the "Shared Datasets" you have a dataset right? Double click on it and go to "Query Designer" it is a button on this form. Click on it and "Execute" the query (red exclamation mark). You dont need to deploy it right now, just do it on a test machine.ago

You specify the user in the datasource section "Shared Data Sources", that account is the account being used to "pull" the data. You really need to experiement with RS some more or read some material on it. This is as basic as it gets.

*Ok aprem do you understand your issue now, the user you are using to get the data has no issues, in addition, it is functioning correctly. Now you have to view the report. To view the report is to view the webpage, and to view a web page means you need to either use "Anon" access or windows authentication. So you need a domain or local WINDOWS account to view the report. This account needs to be setup on the RS portal as a content manager role. *

You are dealing with two beasts, one is the database (db server) and one is IIS (web server) each serve a specific purpose. The database serves to allow you to pull data while the web server hosts the pages.

The reportserver database is very important, it keeps a listing of all your reports and the meta data associated with your reports in the database. It also stores job ids and subscriptions associated with your reports. Basically it is the backend database for all reporting services stuff like reports, datasets, and datasources. Think of it this way when you create a new "Report Project" you are allowed to create reports, datasets, and datasources. Without the reportserver database how would the system remember your datasets, datasources, and report names? It is the heart of rs.

Check out this link below, as it could lend an answer to your problem.

REF: http://www.taznetworks.com/rss/2006/03/crm-30-sbe-sql-reporting-error.html

EDIT:

In doing some work with CRM recently on some test installs we kept coming up with the error that "Setup failed to validate specified Reporting Services Report Server https://server.xxx.com/ReportServer. Error: The request failed with HTTP status 401: Unauthorized.

This seems to be a pretty common problem out there but not a lot of response to the issues. One thing I can say is that CRM is a good product but man is it complex in the guts of it. Not for the faint of heart that's for sure, especially when it comes to SQL and SQL reporting services.

Anyway back to the problem. In most cases with CRM 3 and SBS the documentation at such sites as Anne Stanton's show a problem with ISA. This box we were building didn't have ISA (as of yet).

The problem we were experiencing seems to be more with SP1 of Windows Server 2003 than anything else. The KB article 896861 seems to be spot on. We did the following according to the article:

1. Click Start, click Run, type regedit, and then click OK.
2. In Registry Editor, locate and then click the following registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
3. Right-click Lsa, point to New, and then click DWORD Value.
4. Type DisableLoopbackCheck, and then press ENTER.
5. Right-click DisableLoopbackCheck, and then click Modify.
6. In the Value data box, type 1, and then click OK.
7. Quit Registry Editor, and then restart your computer.

Doing this allowed us to get to https://servername/reports and show the full SQL Reporting Services web site. CRM Reports now work as they were meant to.