SQL Server 2005 Report Builder and Basic Authorization


I have a customer who needs to use Basic Auth with SQL Reporting Services 2005, so I wanted to make sure they could get at Report Builder too.


WARNING: Don’t EVER do this unless you are using SSL with Reporting Services!


If you turn Basic Authentication on for the /ReportServer and /Reports vdirs, then turn Windows Authentication OFF, Reporting Services will pretty much function as usual. When you surf to http://someMachine/reports, IE will prompt you for credentials (Basic), and then you get Report Manager, etc. etc.


However, if you try to browse to http://someMachine/reportserver/reportbuilder/reportbuilder.application to open Report Builder, Report Builder tries to load, but you very quickly get the error: “Cannot Retrieve Application. Authentication Error”.


Thanks to some help from Bob Meyers and Tudor Trufinescu, it became apparent that the web server must allow anonymous access to the /reportserver/reportbuilder folder, or the ClickOnce process on the client cannot download the Report Builder bits. The ClickOnce process cannot prompt for credentials, so it must use Windows credentials, which will never work if the server is set to Basic Authorization…Therefore, a failure.


So, you must use Internet Information manager to grant Anonymous access on the /ReportServer/ReportBuilder folder. Once you do this, we can successfully “grab” the ReportBuilder bits. After we’re actually able to download and launch Report Builder, it will actually prompt the user for credentials.


WARNING, part deux: DO NOT grant Anonymous on the parent /ReportServer vdir..this would be a bad, bad thing.


 



 


.


Comments (36)

  1. Matt Pawuk says:

    Hey Russell, thanks for this post. We are running into a problem with this same issue. However, we are using Windows Integrated authentication but in an extranet environment. Does the same situation apply for us do you think? When I turn on anonymous access it works just like you describe in this post but if there’s a way to not turn on anonymous access and still use Windows Integrated authentication then we would prefer that route. At any rate, thanks for sharing all these solutions with the rest of the development community!

    Thanks,

    Matt

  2. Hey Matt —

    Can’t say I like the idea of turning anonymous on in an extranet scenario. Could you use forms auth instead of Windows for your extranet people?

  3. Matt Pawuk says:

    Russell,

    Thanks for the response. We are using Basic authentication + SSL certificate now in an extranet environment still. If we go to forms authentication for our extranet users how do we then pass those credentials along to Report Builder? I understand the concept of having the user login and then storing their credentials in session variables but I’m unclear on how to pass them along to the underlying tool.

    Sorry, I know I’m getting into more detail than you probably bargained for and I don’t mean to try and get free instruction but I’ve never really been clear on this concept.

    Thanks again,

    Matt

  4. Hey Matt — I have good news and bad news for you…The good news is that you don’t have to do anything. The bad news is you don’t have to do anything because you can’t pass Forms Auth(or Basic, for that matter) credentials TO Report Builder in the first place. If you decide to go with any other auth mechanism than Windows, Report Builder (Forms) or IE/IIS (Basic) will prompt for credentials a second time when you launch Report Builder. Maybe someday ClickOnce apps will allow what you want to do, but right now, they don’t. Sorry!

  5. Janie says:

    Hi Russel,

    I guess this post would also help in the authentication problem I encounter.  I got this error whenever I run a report in the report builder.  I am using a domain account and my RS identity service is set to Network Service.

    Logon failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E)

    —————————-

    Logon failed.

    would you know what username does the report builder is using? how can i trace it?

  6. cory says:

    Janie, I am in the same situation. Any luck?

  7. tlarson says:

    When using Forms authentication, Report Builder prompts for a username and password.  However, the LogonUser function in iAuthenticationExtension uses a username, password, and authority.  How do you get ReportBuilder to pass a particular authority to LogonUser?

    Thanks!

  8. Tim, don’t worry about authority — You don’t need to change anything in the forms Auth sample for Report Builder to authenticate correctly.

  9. Janie and cory, you need to enter domain creds…when I played around with this I seem to remember that a particular account which I thought would work didn’t…Can you try a local user account on the box just to see what happens?

  10. suddha says:

    Janie and Corie, I hope you have already found the solution to your prob. But you have not posted the solution till now. I found the solution in another community. I would like to post the solution here also, so that the other troubled souls, who eventually come to reach here with their all confusions dont have search for another community.

    I talked so much because i have only one line to say about the solution:

    Open the Reporting Services Configuration tool,

    there go for Execution Accounts.

    Now just deactivate the unattended execution account(uncheck the check-box on that tab).

    believe me it really works, just so simple.

  11. Kevin says:

    LIFESAVER!!!  Thanks so much, I had to fix this issue before I could leave for vacation, you saved me!

  12. Adam Tappis says:

    Thanks for the info. It’s helped me to solve  half my problem. Now that report builder launches from a client machine it pops up a login prompt but rejects any credentials typed in (even server administrators). Report builder works fine when I’m on the box through remote server. Any ideas?

    Also, when I have integrated authentication switched on I can’t access the RS portal. It only works when I switch on basic. Am I missing something on the server configuration side of things?

  13. Tim Larson says:

    There’s a big gotcha when using Report Builder with custom authentication (mabye for standard windows auth, too, I’m not sure):  You cannot distinguish between different domains.

    The credentials that Report Builder sends and the credentials that you see in your custom authentication and authorization aren’t always the same…

    When you type a username and password into Report Builder, you can type in the following:

    Username:   myDomainmyUsername

    Password:   myPassword

    The Domain, Username and Password are all passed into your custom authentication function, so that you can figure out if the password is valid.

    However, when it comes to your authorization function, when you’re determining if the user should have access to a particular resource, all you have access to is the username – the report server does not remember what the domain is for the user.  This is rather annoying if you’re using more than one domain, as we are.

    Our solution was the following:  when a user types in a username and password, type it in like this:

    Username:   XXXmyDomainmyUsername

    Password:   myPassword

    This way, as far as reporting services is concerned, the domain is "XXX" (a dummy value) and the username is "myDomainmyUsername".  This username is passed into both your authentication and authorization functions, and so long as you parse it out yourself, you then have access to both the domain and the username.  It’s a kludge, but given the limitations of custom auth in reporting services, it’s the only thing I could come up with.

    To Adam: as to the problem you are describing, I would do some debugging in your custom authentication and authorization to see what is going on.  It may be that you’re not interpreting the username/domain/password properly in your custom authentication, or it might be that your custom authorization is expecting to have the domain available (which won’t be available without the hack I describe above).  You can also turn off SSL (only in testing, not in production!) and do some network sniffing (using ethereal or something similar) to see what packets are being exchanged between Report Builder and report server.  Sometimes you’ll see error messages in those packets that won’t show up in Report Builder.

        Tim

  14. Adam Tappis says:

    Thanks for the reply Tim. I am not using a custom extension in the above example. My server is set up for Basic Authentication using standard windows accounts (impersonation is switched on).

  15. Jay Foster says:

    Hi All,

    I sure hope this saves someone the headache I had with the "Cannot retrieve application. Authentication error." Error message when trying to get the ReportBuilder to download.

    Here’s my situation: Need to have clients download the ReportBuilder after logging into Report Manager. There are no domains here, no intranet, no extranet, simply an Internet connection. Everything on the same Win2003 server, sql server, IIS and Report Services. I was able to login to the Report manager just fine but when I tried to access the ReportBuilder button, it failed with the above error. I scoured the web to find a resolution and there were places that actually threw me in the wrong direction for my particular configuration! After 8 hours on the phone with Microsoft here’s the…

    RESOLUTION:

    In IIS, there are 3 virtual directories (Reports, ReportBuilder, and ReportServer). For each go to Properties, Directory Security and Edit button for the Authentication section. Here’s what the settings should look like:

    Do NOT check the Anonymous Access checkbox for all 3 virtual directories, make sure Anonymous Access is unchecked

    Reports directory use Integrated Windows Authentication

    ReportBuilder use Basic authentication (make sure you have an SSL cert)

    ReportServer use Integrated Windows Authentication

    Very important! when logging into the Report Manager you are prompted for Windows credentials, under UserName and Password, be sure the users check the "Remember Me" checkbox, if not, the credentials will not be stored and the ClickOnce application will not authenticate properly.

    Good luck!

  16. Manny says:

    Suddha – You saved me (great tip on account execution)!!!

  17. Al C says:

    Any Help So Much Appreciated.

    I’m using Report Builder with the Custom Security Extension to use Forms authentication.  

    Everything seems to be working great except for 1 little bit of ugliness.  

    When the user tries to launch Report Builder, they are prompted for their login.  If the login is incorrect and fails, they are presented with an ugly SoapException error.  

    Is there anyway to customize the error displayed in the Report Builder Login?

    When Report Builder is working in Windows Integrated mode externally there is no error at all.  The prompt just remains.  Even this would be OK if I could have it work that way with Forms Auth.  I just can’t show the ugly SOAPException.  

    Any Ideas?

  18. This is unfortunately expected behavior. No way around it. There is another post here that talks about same.

  19. Al C says:

    Ok, I guess I have to deal with it.

    Thanks very much for the response and for the other blog about how to setup Forms Auth… great help.

  20. bechram says:

    I’m not sure if the issue I’m having has been described here as a "failure to authenticate" type of issue.  All I’m trying to do is launch report builder from IE for a report server running on the same machine.  I get the initial "Verifying application requirements…" dialog and then I see a quick flash of a window (as if Report Builder attempted to load and failed for some reason) and that’s it.

    Any thoughts would be greatly appreciated!

  21. No clue, bechram – can you launch Report Builder on a different box?

  22. jelp.me@gmail.com says:

    We created a custom security extension for RS using Forms Authentication. The issue I am having is in ReportBuilder when the forms authentication ticket expires. Forms auth will attempt to redirect (send a HTTP 302) and the report builder will show the error message "Object moved to here" instead of prompt for credentials. I have been watching the IIS logs and I see the first time report builder start it attempt to connect using default windows credentials and after the FormsAuthenticationRequired exception it will prompt for credentials and invoke LogonUser method, but this only happens the first time or if change the server in the url. I was expecting ReportBuilder to consider the Forms Authentication expiration at any time.

    Do you have any idea how to work around this?

    Any help is really appreciated.

    Thanks

  23. deanoc says:

    Hi,

    We have made ReportBuilder available over the internet using SSL and Basic Authentication. This works fine for some users, but unfortunately some users do not get prompted by ReportBuilder to login and if they manually enter the URL in the Select Site Or Server form, they get an "unable to connect to server" error.

    As some users can connect and some can’t, this seems to be a firewall/client PC issue. Does anyone have any ideas on where/how I should start looking?

    Thanks, Dean

    I have the situation where users can download the application, but some users

  24. deanoc says:

    Hi again,

    It turns out that it was the proxy server settings in IE. If we disabled the use of the proxy server, then the Report Builder app was able to connect to the Server ok.

  25. Rob Risetto says:

    Hi,

    I have similar problem where the Report builder is downloaded by no login dialog is presented, instead the Select Site dialog is shown and if I choose the reportserver I get the "unable to connect" error. Now there’s no proxy setting on IE. Another user over the internet can download the RB, gets prompted for credentials and connects successfully. It seems some setting my client that is causing the issue. Can you please suggest items that I should check.

    thanks

    Rob

  26. Faheem Zafar says:

    hi too all,

    my user can’t see report builder link report manager’s page.

    thought the user is network user and have rights as "User" on the report server and have the rights of "build" reports.

  27. Heidi says:

    Hi Faheem, in Site Settings on Report Manager, check the System User role definition to see if it has Execute Report Definition.

  28. Jaswant A says:

    Hi All,

    If anyone knows please tell me.

    How to pass Session variable values from web application to Report builder application.

    This problem is breaking my heart and soul.

  29. Quentin says:

    Ive installed SQL 205 with Report Services as per instructions found at http://support.microsoft.com/kb/938245/ for Windows Server 2008. From the server when i logon i receive the dialog box and enter the administrator username and password. I then receive the error "unable to connect to remote server". Ive searched every where for a solution but nothing so far – what could i have done wrong or what could be causing the problem?

    Thanks

  30. Ambers says:

    Any idea how to do this with SSRS 2008?

  31. Kevin Karlin says:

    Thanks Russel and all the rest of the posters, this comment saved my bacon "Very important! when logging into the Report Manager you are prompted for Windows credentials, under UserName and Password, be sure the users check the "Remember Me" checkbox, if not, the credentials will not be stored and the ClickOnce application will not authenticate properly."

    We have a report server using integrated auth in an extranet environment. I have never utilized Report Builder before, but my client wanted it so I figured no big deal a little set up and we’d be done. I spent half a day trying to figure out why after setting all of my roles correctly things still wouldnt work. For security, I NEVER have my system remember passwords for websites, and that was aparently what was tripping me up. Launched a new IE instance logged in, checking "Remember Me" and suddenly everything worked.

    I have read and re-read all the documentation I can find and dont see that as a step in any instructions.

    Thanks Again.

  32. siri says:

    Hi,

    i have the report model created. i wish to be able to pass on the url to my end user who will be able to create the reports.

    i used sitewide settings and using windows authentication and domain i added the other user as an end user.

    my question is can i just pass on the url

    http:localhostreports to him and ask him to open it on his system.

    Does the local system work on his machine.

    sorry for my ignorance.

    how do i send him the url to report manager where he can launch the report builder and start working on it.

    please help me out

    thanks

  33. Quest says:

    Should the Default Web Site use anonymous access, IWAM_user account? I was wondering if this should be locked down.  All of our RS/RB users are external to our network.

  34. HERBERTS says:

    Am always interested in report generating tools.It is my source of inspiration. I have tried several tools. At the moment I use FastReport server.

    I think the issue of having authorization and security should be solved in Report builder.Why is it that some people have it running normally, and others not much ? I think something should be done with that by the developers.