401 Unauthorized access Error in SSRS

In the SSRS Reporting Environment, you may never encounter this error and you live a happy and easy life. We all know an SSRS report can easily ping your server to 100% CPU if you decide to export a 120 page report, to PDF,  with charts, etc. etc. You may get a complaint that performance suffers in another application while running the reports and you want to fix it, but do not know how.

Then one day you or someone at your firm has decided to do your SQL Server a favor and separate out SSRS from the SQL Server. This sounds like a great idea. You build a new SSRS box (lets call it SSRSPROD), run some performance optimizations (http://sqlcat.com/technicalnotes/archive/2009/01/14/reporting-services-performance-optimizations.aspx) and you are set.

You remote onto the new server, deploy all of your reports, open up IE on your server and check the reports; they run and life is good. You give the all clear to your users and suddenly your inbox is flooded with "My Reports do not work, I am getting a weird error" Testing it out for yourself on your computer, you suddenly see the dreaded "401 - Unauthorized Access Error". You go and check the data sources, you are using Windows Authentication, everything looks good. All of the user/group permissions on the reporting server are also correct. You remote onto the server, the reports run and once again they work. You then ask the Lord Google for an answer and see a ton of Kerberos discussions and different links. !@##$#@ you say to yourself. What did I do??? You have stumbled onto the Double-Hop Authentication Problem that so many of us have hit before in ASP.NET land or even SSRS.

Here are three quick and easy solutions, maybe not so quick and easy, but they work:

1) Maybe you just have a simple header error. This would happen if SSRSPROD is accessed through a different name like http://myreports/reports instead of http://ssrsprod/reports. The solution is here:
http://blogs.msdn.com/lukaszp/archive/2008/07/18/reporting-services-http-401-unauthorized-host-headers-require-your-attention.aspx

2) Convert all of your Data Sources to use SQL Accounts. This works because SQL connection parameters are passed through the authentication without a care. This has the added benefit of allowing your reports to be Subscription enabled in the future. HOWEVER, it has the major drawback of setting up the SQL Account as well as Security on the report. If this report should only have been viewed by John Doe, it is going to take some effort to make sure that SQL Account has the same permissions as JD. It may be tempting, but DO NOT GIVE YOUR NEW ACCOUNT DBO privileges. You will thank me after your next security audit.

3) Setup Authentication Delegation, Pass-through, whatever you like to call it. Mohammed Jeelani has a nice start from his blog:
http://blogs.msdn.com/mjeelani/archive/2004/12/07/275921.aspx
Then you can look at Microsoft's delegation articles:
http://technet.microsoft.com/en-us/library/cc739740%28WS.10%29.aspx
Finally, when you feel you have some more background on this, click these two links to solve your problem IF you are using Kerberos:
http://msdn.microsoft.com/en-us/library/cc281253.aspx#proxyfirewallRSWindowsNegotiate
http://msdn.microsoft.com/en-us/library/cc281382.aspx

Please post any comments!
Thanks

Comments

Post a Comment

Popular posts from this blog

host

import pandas as pd from datetime import datetime import os # Example DataFrame df_final = pd.DataFrame({     'Name': ['Alice', 'Bob', 'Charlie'],     'Age': [25, 30, 35],     'City': ['New York', 'Los Angeles', 'Chicago'] }) # Generate today's date in 'YYYY-MM-DD' format today = datetime.now().strftime("%Y-%m-%d") # Create a dynamic filename filename = f"{today}_data.csv" # Network drive path (Windows) network_folder = r"\\network_drive\shared_folder" # Full path to save the file full_path = os.path.join(network_folder, filename) # Save DataFrame to CSV try:     df_final.to_csv(full_path, index=False)     print(f"File saved successfully to {full_path}") except Exception as e:     print(f"Error saving file: {e}")
Read more

Steps to create SSH key from git bash

Generating an SSH key in OpenSSH involves a few simple steps. Here’s how you can do it: 1. Open a Terminal If you're on Linux or macOS, you can use the built-in terminal. On Windows, you can use the Command Prompt, PowerShell, or Git Bash if you have Git installed. 2. Generate the SSH Key Pair Run the following command: bash Copy code ssh-keygen -t rsa -b 4096 -C "your_email@example.com" Here’s what each option means: -t rsa : Specifies the type of key to create, in this case, RSA. -b 4096 : Specifies the number of bits in the key, 4096 bits is a good choice for strong encryption. -C "your_email@example.com" : Adds a label to the key, usually your email address. 3. Choose a File Location After running the above command, you'll be prompted to choose a location to save the key. By default, it will be saved in ~/.ssh/id_rsa (on Linux or macOS) or in C:\Users\YourUserName\.ssh\id_rsa (on Windows). Press Enter to accept the default location, or specify a diff...
Read more

test

.. https://ubiquis.co.uk/loading-csv-files-with-pdi-metadata-injection-2/ -- Create a temporary table to demonstrate the solution CREATE TABLE YourTable ( ID INT, YourColumn VARCHAR(100) ); -- Insert sample data INSERT INTO YourTable (ID, YourColumn) VALUES (1, '1234_4567_abc'), (2, '789_012_def'), (3, 'xyz'); -- Declare variables for storing results DECLARE @firstPart VARCHAR(100), @secondPart VARCHAR(100); -- Iterate over rows in the table DECLARE @currentID INT = 1; DECLARE @rowCount INT = (SELECT COUNT(*) FROM YourTable); WHILE @currentID <= @rowCount BEGIN -- Retrieve input string from the table DECLARE @inputString VARCHAR(100); SELECT @inputString = YourColumn FROM YourTable WHERE ID = @currentID; -- Check if the delimiter exists in the input string IF CHARINDEX('_', @inputString) > 0 BEGIN -- Find the position of the first occurrence of the delimiter in the input string DECLARE @delimiter...
Read more