次の方法で共有

Report Builder and Forms Authenticaton in SQL Server Reporting Services 2005

  • [アーティクル]

I've had a fair number of questions from my customers around the use of Forms Authentication in conjunction with Report Builder. The basic thing people want to know is "does it work?".

As a matter of fact, it does. It functions the same way as with Windows Auth except for one behavior: Basically, there is no way to pass the Forms Auth cookie that was generated when a user logged into your application (or Report Manager) along to Report Builder. This is a limitation of ClickOnce applications.

Since we can’t pass the auth cookie to Report Builder, it must deal with the authorization process itself…so the client will pop up a simple logon dialog to your users. Double logon prompts are a bit messy, but I think it’s a fair trade-off in order to be able to use Report Builder with all security mechanisms in SSRS.

Comments

  • Anonymous
    December 20, 2005
    I have successfully gotten formsAuthentication working with SQL Server 2005 with the exception(so far) being that the Reprot Builder log in sends a user account of "" to the Custom Security Extension. Is there a configuration that needs to be changed so that the credentials are passed to the Security Extension?

    Thanks

  • Anonymous
    December 21, 2005
    I've never seen anyone run into this problem before...When you enter your credentials into Report Builder, they go right to the security extension, so I don't see how they could change to a zero-length string. How did you determine that "" was getting sent?

  • Anonymous
    March 07, 2006
    The comment has been removed

  • Anonymous
    March 10, 2006
    I need to see the stack trace? I'm guessing your ASPWebHosting Permission is being denied, but have to see the stack.

  • Anonymous
    April 05, 2006
    Hello Russell,

    I'm getting the same problem, i.e. "The permissions granted to user '' are insufficient" with Report Builder.

    Did you find a resolution for Tim Larson & barnold?

    Thanks!ch

  • Anonymous
    April 06, 2006
    Hi...

    No, I asked for the complete stack trace whch I never got. If you could post it I'd be happy to take a look...Paste entire (long) stack trace in...

  • Anonymous
    April 06, 2006
    Hello Russell,

    I picked the following out using tcptrace:

    <faultcode>soap:Client</faultcode><faultstring>System.Web.Services.Protocols.SoapException: The permissions granted to user '' are insufficient for performing this operation. ---&gt; Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user '' are insufficient for performing this operation.
      at Microsoft.ReportingServices.Library.RSService._GetItemType(String item, Byte[]&amp; secDesc)
      at Microsoft.ReportingServices.Library.RSService.GetItemType(String item)
      at Microsoft.ReportingServices.WebServer.ReportingService2005.GetItemType(String Item, ItemTypeEnum&amp; Type)
      --- End of inner exception stack trace ---
      at Microsoft.ReportingServices.WebServer.ReportingService2005.GetItemType(String Item, ItemTypeEnum&amp; Type)</faultstring><faultactor>http://erlaptop:8080/ReportServer/ReportService2005.asmx</faultactor>

    Any ideas?

    Thanks!

  • Anonymous
    April 07, 2006
    If you go into IIS Manager, what are the directory security settings (auth and access controls) for the ReportServer vdir? What about the /ReportBuilder folder under the ReportServer vdir?

  • Anonymous
    April 07, 2006
    Both the ReportServer and /ReportBuilder virtual directories have anonymous access enabled, using the IUSER account

  • Anonymous
    April 24, 2006
    I am getting an error "The attempt to connect to the report server failed.  Check your connection information and that the report server is a compatible version." , when I enter valid login and password. I am running June CTP, is this the problem??

  • Anonymous
    April 24, 2006
    Could be -- perhaps you added a service pack and/or changed the credentials that you used to connect to the server?

  • Anonymous
    April 26, 2006
    The comment has been removed

  • Anonymous
    May 05, 2006
    The response from the server looked like this (I can't seem to submit it verbatim - the blog rejects it, so I've abbreviated it):

    Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user '' are insufficient for performing this operation.

    --- End of inner exception stack trace ---

    at Microsoft.ReportingServices.WebServer.ReportingService2005.GetItemType(String Item, ItemTypeEnum&amp; Type)

    Fault actor:http://vmsql03.mtw.int/ReportServer/ReportService2005.asmx
    ErrorCode: rsAccessDenied
    HttpStatus: 400
    Message: The permissions granted to user '' are insufficient for performing this operation.
    HelpLink: http://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsAccessDenied&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=9.00.1399.00
    ProductName: Microsoft SQL Server Reporting Services
    ProductVersion: 9.00.1399.00
    ProductLocaleId: 127
    OperatingSystem: OsIndependent
    CountryLocaleId: 1033
    Source: ReportingServicesLibrary


  • Anonymous
    May 05, 2006
    I'm making progress on this... I think.

    The error I'm dealing with is that I can't get Report Builder to authenticate when using Forms authentication.  I can log in just fine at UILogon.aspx and Logon.aspx and see all sorts of fun stuff.

    I have discovered some interesting facts:

    ReportBuilder uses the ReportService2005.asmx web service to talk to the report server.

    The ReportService2005.asmx web service will only give data out if either the LogonUser function is called or if there are NetworkCredentials (basic auth) attached to the web request.  

    Report Builder doesn't seem to be interested in calling the LogonUser function, so we need to coax it into attaching Basic Auth to its web request.  Report Builder will send the basic auth to the ReportService2005.asmx web service, but only if it gets a '401 unauthorized' error first (my guess is that when it gets a '400 - Bad Request' error, it assumes it is authorized and doesn't bother to send credentials).

    If I set the ReportService2005.asmx web service to allow anonymous access in IIS, Report Builder will never send the basic auth because it gets 400 errors.  Report Builder looks at the HTTP header to see if it is unauthorized or not - it doesn't look inside the SOAP packet and see that it is unauthorized.

    If I set the ReportService2005.asmx to disable anonymous access in IIS, and only allow basic auth, the 401 error will get sent back to Report Builder, Report Builder will then send the username and password as basic auth, but IIS will still respond with a 401 error, becuase the username and password are not valid for basic auth.  Kindof.  I think what is happening here is that IIS rejects the basic auth because it's not a windows user (or something), whereas if ReportService2005.asmx gets it's hands on the HTTP request, it opens up the basic auth and is happy.


    Is it possible that I have my rssrvpolicy.config file set up wrong?  (I have no idea what I'm doing in that file...)

    Is there a way to get Report Builder to call the LogonUser function in the ReportService2005.asmx web service?

    Is there a way to get Report Builder to send basic auth to ReportService2005.asmx, even if anonymous access is allowed on that web service?

    Or how can I set up IIS so that it will return a 401 error if no basic auth is included, but so that when basic auth is there, it will somehow authorize it at the IIS level based on the Forms auth, even though there is no call to any kind of function that can set the using forms auth rather than basic auth?  (this is where I think the rssrvpolicy.config file might come into play, but I really don't know)


    Or am I completely missing something obvious here?

    Thanks for reading my continued rambling difficulties.

       Tim Larson
       Mission to the World

  • Anonymous
    June 13, 2006
    How are you progressing with this issue. I and it seems may other developers on the web are having similar problems, but I've yet to see any definitive solution and work around.

    In my instance it's further complicated by the customer using Windows Home edn, ( would you credit it!) on some of their laptops.

  • Anonymous
    June 21, 2006
    The comment has been removed

  • Anonymous
    June 27, 2006
    Great workaround, Tim....One other thing you might want to try is use Soap Trace and/or Pocket Soap to see if a cookie is indeed even being passed TO the SSRS server by Report Builder...

  • Anonymous
    June 29, 2006
    The comment has been removed

  • Anonymous
    August 14, 2006
    Can someone post the code for the HTTP filter workaround?
    I am trying to pass authentication info from my app. to Report Builder and have not written any HTTP filters before.

    Thanks in advance...

  • Anonymous
    August 14, 2006
    Here is the code for the Filter.  I hope that it comes out formatted properly on the blog...

    To make this filter apply, go to the web.config file for the Report Server and add this under the system.web section.  Make sure the DLL (compiled from the code below) is in the Bin directory for the Report Server application (in my case, it is MTW.PPSSTR.SSRSHTTPFilter.dll).

    --------------------------------------------
    web.config addition:
    --------------------------------------------

    <httpModules>
     <add type="MTW.PTSSRS.SSRSHTTPFilter.SSRSServerFilter,MTW.PTSSRS.SSRSHTTPFilter" name="MTW.PTSSRS.SSRSHTTPFilter"/>
    </httpModules>

    --------------------------------------------
    Filter Code:
    --------------------------------------------

    #region Copyright © 2006 Mission to the World
    /============================================================================
     File:     SSRSFilter.cs
     Summary:  An HTTPFilter to change the contents of the HTML coming out of
               Report Server.  Specifically, it is used to change this error:
                   HTTP/1.1 500 Internal Server Error ...
                   The permissions granted to user '' are insufficient for performing this operation.
               To this error:
                   HTTP/1.1 401 Unauthorized
                   WWW-Authenticate: Basic realm="vmsql03.mtw.int"
                   The permissions granted to user '' are insufficient for performing this operation.
               Why do this?  Because when ReportBuilder sees the first error, it doesn't
               think to send the authentication as basic authentication, which is
               what the Report Server is expecting.  The 401 error will trigger
               ReportBuilder to send authentication as Basic Auth, which the report
               server will accept
    --------------------------------------------------------------------
       
    THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
    ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
    THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
    PARTICULAR PURPOSE.

    05/06/2006 - SSRSServerFilter Created by Tim Larson, tlarson@mtw.org

    Resources for learning about HTTP Filters:
      http://www.ondotnet.com/lpt/a/4236
      http://www.dotnet2themax.com/ShowContent.aspx?Type=freeware&ID=35efbee1-d8cd-4720-9eb2-83fc9a4033bb
      http://www.users.bigpond.com/conceptdevelopment/Localization/HttpFilter/,
      http://www.devx.com/vb2themax/Article/19901
      http://www.ondotnet.com/pub/a/dotnet/2003/10/20/httpfilter.html
      http://www.users.bigpond.com/conceptdevelopment/Localization/HttpFilter/code.html
      http://authors.aspalliance.com/aspxtreme/sys/Web/HttpResponseClassFilter.aspx
      http://www.aspnetresources.com/articles/CustomErrorPages.aspx
      http://www.aspnetresources.com/articles/HttpFilters.aspx
      http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q307985

    ===========================================================================    /
    #endregion

    using System;
    using System.IO;
    using System.Text;
    using System.Web;
    using System.Text.RegularExpressions;

    namespace MTW.PTSSRS.SSRSHTTPFilter
    {
       public class SSRSServerFilter : System.Web.IHttpModule
       {
           public void Init(System.Web.HttpApplication application)
           {
               // Set our function as an event handler for the
               // ReleaseRequestState event in the ASP processing chain
               application.ReleaseRequestState += new System.EventHandler(InstallSSRSServerFilter);

               // We'll also use it as an event handler for the PreSendRequestHeaders
               // (which is actually dealing with the response, not the request)
               // in case the Page flushes its content prior to everything being ready
               application.PreSendRequestHeaders += new System.EventHandler(InstallSSRSServerFilter);
           }

           public void InstallSSRSServerFilter(Object sender, EventArgs e)
           {
               HttpApplication application = (HttpApplication)sender;
               HttpContext context = application.Context;

               // if the filter has not been installed yet, create a new
               // instance of it, and install it
               const string INSTKEY = "SSRSServerFilterInstalled";
               if (!context.Items.Contains(INSTKEY))
               {
                   HttpResponse currResponse = context.Response;
                   HttpRequest currRequest = context.Request;

                   string s1 = currResponse.ContentType;
    //                bool b1 = currResponse.ContentType.Contains("text/xml");
                   Uri u1 = currRequest.Url;
                   string s2 = currRequest.Url.ToString();
                   bool b2 = currRequest.Url.ToString().Contains("ReportService2005.asmx");

                   // Add our filter to the page, but only if we are encountering
                   // a text/xml content type, and only if we are viewing the
                   // ReportService2005.asmx page
                   if (
                           (currResponse != null) && (currRequest != null) && (currResponse.ContentType != null)
                           &&
                           (currResponse.ContentType.Contains("text/xml"))
                           &&
                           (currRequest.Url.ToString().Contains("ReportService2005.asmx"))
                      )
                   {
                       // Add a filter to the current response
                       SSRSServerStream ms = new SSRSServerStream(currResponse.Filter);
                       ms.SSRSServerStream_Init(currResponse, currRequest.Url.Host);
                       currResponse.Filter = ms;
                       context.Items.Add(INSTKEY, new object());
                   }
               }
           }

           public void Dispose()
           {
           }
       }


       public class SSRSServerStream : System.IO.Stream
       {
           // The stream that goes to the client
           private Stream repStream;
           // Used to write the stream to the client
           private StreamWriter repSR;
           private bool isDone = false;
           private StringBuilder _htmlBuffer = new StringBuilder();
           private string sReportServerHost;
           private string s1NewStatusDescription, s1CheckValue;
           private int i1NewStatusCode;
           private string s1NewHeaderName, s1NewHeaderValue;

           private HttpResponse currResponse;

           public SSRSServerStream(System.IO.Stream stream)
           {
               // Save the stream that is going back to the client
               repStream = stream;
               // Create a StreamWriter
               repSR = new StreamWriter(repStream);
           }

           public void SSRSServerStream_Init(HttpResponse ResponseToChange, string sHost)
           {
               currResponse = ResponseToChange;
               sReportServerHost = sHost;
               CreateReplaceStrings();
           }

           private void CreateReplaceStrings()
           {
               s1NewHeaderName = "WWW-Authenticate";
               s1NewHeaderValue = "Basic realm="" + sReportServerHost + """;
               // Old Status: HTTP/1.1 500 Internal Server Error
               i1NewStatusCode = 401;
               s1NewStatusDescription = "Unauthorized";
               s1CheckValue = "permissions granted to user '' are insufficient";
           }

           // The Write function is where all of the filtering happens
           public override void Write(byte[] buffer, int offset, int count)
           {
               if (isDone)
               {
                   repSR.Write(System.Text.UTF8Encoding.UTF8.GetString(buffer, offset, count));
                   repSR.Flush();
               }
               else
               {
                   string buf = System.Text.UTF8Encoding.UTF8.GetString(buffer, offset, count);
                   this._htmlBuffer.Append(buf);

                   // If the </soap:Envelope> closing tag is found, this is the end
                   // of the file, so let's do the replace work
                   if (Regex.IsMatch(buf, "</soap:Envelope>", RegexOptions.IgnoreCase))
                   {
                       string html = this._htmlBuffer.ToString();
                       StringBuilder result = new StringBuilder();

                       // Replace the text (here is where all the magic happens)
                       if (html.Contains(s1CheckValue))
                       {
                           currResponse.Status = i1NewStatusCode.ToString() + " " + s1NewStatusDescription;
                           currResponse.StatusCode = i1NewStatusCode;
                           currResponse.StatusDescription = s1NewStatusDescription;
                           currResponse.AppendHeader(s1NewHeaderName, s1NewHeaderValue);
                       }

                       // write the resulting string to the response stream
                       repSR.Write(html);
                       isDone = true;
                       repSR.Flush();
                   }
               }

           }

           #region StreamFunctionStubs
           // The following functions are overridden to fulfill the
           // implementation of the stream class.

           public override bool CanRead
           { get { return false; } }

           public override bool CanSeek
           { get { return false; } }

           public override bool CanWrite
           { get { return true; } }

           public override long Length
           { get { throw new NotSupportedException(); } }

           public override long Position
           {
               get { throw new NotSupportedException(); }
               set { throw new NotSupportedException(); }
           }

           public override int Read(Byte[] buffer, int offset, int count)
           { throw new NotSupportedException(); }

           public override long Seek(long offset, System.IO.SeekOrigin direction)
           { throw new NotSupportedException(); }

           public override void SetLength(long length)
           { throw new NotSupportedException(); }

           public override void Close()
           { repStream.Close(); }

           public override void Flush()
           { repStream.Flush(); }
           #endregion
       }
    }

  • Anonymous
    August 29, 2006
    Thanks, Tim.
    I got the filter running but then realized it did not address my issue.  I am trying to "pass" authentication to Report Builder so I am not prompted for login a 2nd time (using forms authentication).  Everything I have seen says this can't be done.  At this point I am looking for a workaround.  If anyone has done this please let me know.

  • Anonymous
    September 01, 2006
    Hi Vince. There is no way to accomplish what you want. The only way to avoid a prompt from a ClickOnce application (therefore, ReportBuilder) is if you are using Windows Credentials. ClickOnce apps don't know / don't understand / don't look for cookies, so there's not too much (or the Reporting Services team) can do in this scenario. The ClickOnce guys have been asked to consider adding additional functionality to help in this scenario, but who knows what will happen.

  • Anonymous
    September 21, 2006
    For the record, I got the custom authentication extension up and running after a lot of weeping and gnashing of teeth.

    I started from scratch on a clean development machine. I also read all the material I could find before embarking on my second attempt.

    Knowing WHY I was carrying out steps in the examples rather than just slavishly following them as I was the first time around made a difference as there were several aspects that were particular to the application I'm working on.

    I've just discovered that Microsoft are running a Tech-Net event on RS next week near where I'm based - talk about bad timing! Could have done with it a few months ago!

    Eugene

  • Anonymous
    December 29, 2006
    HiI have been working with Forms Authentication in RS2005 and the Report Builder and have tried to implement the Filter that Tim Larson has descriped.I have succesfully got the Report Builder working now and as it turned out I actually got it to work without the filter. I noticed that when Report Builder is calling the LoginUser method the userName and password is empty strings. Because I just implemented my test LoginUser method, always returning true, the Report Builder is believing that the user is OK. Instead if I changed the code to test if the useName is empty, and returns false, if so, the Report Builder now have to prompt the user for a username and passsword, and this solved my problems. Maybe someone else missed that, if they were always returning true from the LoginUser method during test. So every thing is working for me now, also without the filter that Tim Larson has implemented.

  • Anonymous
    May 29, 2007
    The comment has been removed

  • Anonymous
    May 29, 2007
    You said:"Since we can’t pass the auth cookie to Report Builder, it must deal with the authorization process itself…so the client will pop up a simple logon dialog to your users. Double logon prompts are a bit messy, but I think it’s a fair trade-off in order to be able to use Report Builder with all security mechanisms in SSRS."Why doesnt it automatically try the credentials of the logged in user? I thought thats what intergrated windows authentication meant?  If the logged on user's credentials dont work, then it should show the login dialog, right?  Or am I still missing something? :)JK

  • Anonymous
    May 30, 2007
    JK, not enough info in your problem description. Is SSRS configured to use Forms Auth, too? How? Are you sharing the same user store?It sounds like you want to mix authentication modes - Forms Auth sometimes, Windows auth others. You can't do this.

  • Anonymous
    May 30, 2007
    Hi Russell, thanks for replying.  Because I am confused I am probably not describing the situation too well :)My website uses forms auth for authenticating access to the website only. RS is configured for windows auth only.When a report is run the ReportViewer.ServerReport.ReportServerCredentials is set to a IReportServerCredentials that returns new NetworkCredentials(configured username, configured pwd). This works fine, and all reports run using the same windows auth credentials saved in web.config.  Our website also has a Report Builder button for ad-hoc reports, that launches the Report Builder app.  This pops up the login dialog even though RS has been configured for windows auth, and the logged on user has permissions to use RS.So I am using windows auth all the way, and it works for the user who has permission to run the reports, but not for the user who has permission to run the report builder.  I have tried to make the permissions for both users the same.I am guessing that I have not correctly set the permissions for the report builder.  In my first post I described the permissions used  - are those permissions correct for allowing someone to run report builder using windows auth? (ie no login dialog)Thanks,JK

  • Anonymous
    May 30, 2007
    Another way of looking at it...I know how to make the /ReportServer webservice use windows auth, just click the windows auth checkbox in IIS on the Directory Security tab for the /ReportServer website.But my attempts to allow windows auth for the ReportBuilder click once app have failed.  I have tried changing the ACL of the ReportServer windows folder - with no success.  What access permissions are required, and on what object(s)?  Thanks,JK.  

  • Anonymous
    June 05, 2007
    The comment has been removed

  • Anonymous
    June 05, 2007
    The comment has been removed

  • Anonymous
    June 06, 2007
    Are you able to even launch the ClickOnce App, or are you prevented from getting into the /ReportBuilder folder itself to get at the application?

  • Anonymous
    June 10, 2007
    The comment has been removed

  • Anonymous
    June 12, 2007
    They can launch the reportbuilder.application click once app.  It just shows its own login dialog (different from the normal windows login dialog).If the user tries to browse to the folder in windows explorer using (server)(path)report serverreportbuilder, they get an error: "Access to the resource (server)(path)etc has been disallowed"JK

  • Anonymous
    June 14, 2007
    That fact that you can't have a single sign on for Report Builder is a fairly serious flaw. As far as the end user is concerned they have logged in. Forget about the the complications from a developers point of few.

  • Anonymous
    June 14, 2007
    JK, is an error actually thrown at any point when the user supplies credentials?

  • Anonymous
    June 19, 2007
    Hi Russell,If you enter the credentials into the Report Builder login dialog it says "The permissions granted to user (domain)(user) are insufficient for performing this operation".JK.

  • Anonymous
    July 11, 2007
    The comment has been removed

  • Anonymous
    February 26, 2008
    Is it possible to use the Report Builder in a Software as a Service Model?We want the users to be authenticated based on their user id, password and company name, stored in one of the tables in database.We don't want the users to be provided the login credentials of the Report Server.Once the user has logged on to our website they can click the Report Builder and it will launch the application and get data only for the company they are logged into

  • Anonymous
    March 02, 2008
    Deveshd: Yes, you can do this using the sample Forms Authentication security extension (however you'll only provide a user name and password, not a company name).

  • Anonymous
    April 01, 2008
    PingBack from http://copyrightrenewalsblog.info/russell-christophers-semi-useful-bi-musings-report-builder-and-forms/

  • Anonymous
    April 25, 2008
    The comment has been removed

  • Anonymous
    June 13, 2009
    PingBack from http://barstoolsite.info/story.php?id=6437

  • Anonymous
    June 15, 2009
    PingBack from http://edebtsettlementprogram.info/story.php?id=22584

  • Anonymous
    June 15, 2009
    PingBack from http://workfromhomecareer.info/story.php?id=14700