Geek Answers Handbook

Download XML file from sql report server

I am trying to download an XML file from an Azure SQL Report Server that requires authentication to receive the file. I am trying to use two approaches:

Approach 1

In this approach, I tried using the C # console application and downloaded the file from the server. It works great!

void DownloadFile(string uname, string password) { ServerReport report = new ServerReport(); report.ReportServerUrl = new System.Uri("https://xxxxx.reporting.windows.net/ReportServer", System.UriKind.Absolute); report.ReportPath = "/Demo2.rdl"; report.ReportServerCredentials.SetFormsCredentials(null, uname, password, "xxxxx.reporting.windows.net"); byte[] data = report.Render("XML"); FileStream fs = new FileStream(@"c:\output.xml", FileMode.Create); fs.Write(data, 0, data.Length); fs.Close(); }

Approach 2

Now I want to download the same file from the Windows Phone application. Now, since ServerReport is not on the Windows phone, I rely on WebClient to download the file. So I am trying to make the code below and it seems to not work:

 void DownloadFile(string uname, string password) { WebClient webClient = new WebClient(); webClient.Credentials = new NetworkCredential(uname, password, "xxxxx.reporting.windows.net"); webClient.DownloadFile("https://xxxxx.reporting.windows.net/ReportServer?%2fDemo2.rdl&rs:Command=Render&rs:Format=XML", @"c:\output.xml"); }

The following is the result of approach 2: does it seem to redirect to the login page?

  <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <html lang="en-US"> <head id="Head1"><meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1" /><link rel="Stylesheet" type="text/css" href="Public/Logon.css" /><title> Windows Azure SQL Reporting </title></head> <body > <form name="Logon" method="post" action="logon.aspx?ReturnUrl=%2fReportServer%3f%252fDemo2.rdl%26rs%3aCommand%3dRender%26rs%3aFormat%3dXML&amp;%2fDemo2.rdl&amp;rs%3aCommand=Render&amp;rs%3aFormat=XML" id="Logon"> <input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE" value="/XXXX" /> <input type="hidden" name="__EVENTVALIDATION" id="__EVENTVALIDATION" value="/YYYY" /> <h1 id="Title"><span id="LblTitleContent">Windows Azure</span></h1> <hr /> <div class="ClsIntro"> <span id="LblIntro"></span> </div> <div class="ClsInput"> <div> <label for="TxtUser" id="LblUser">User name</label> </div> <div> <input name="TxtUser" type="text" id="TxtUser" tabindex="1" /> </div> </div> <div class="ClsInput"> <div> <label for="TxtPwd" id="LblPwd">Password</label> </div> <div> <input name="TxtPwd" type="password" id="TxtPwd" tabindex="2" /> </div> </div> <div class="ClsSignIn"> <input type="submit" name="BtnLogon" value="Sign in" id="BtnLogon" tabindex="4" /><input type="image" name="BtnLogonArrow" id="BtnLogonArrow" tabindex="5" src="Public/WhiteRightArrow.png" alt="Sign in" align="baseline" border="0" /> </div> <div class="ClsErrMsg"> <span id="lblMessage"></span> </div> <hr /> </form> </body> </html>

What could be the problem with approach 2, we don’t notice something?

Update

I am completely new to this azure and web service. Maybe I'll ask some kind of stupid question. Here is what I tried:

  • I tried to add the "Service" link to the azure sql report server in my Windows Phone 7 project. One thing I noticed is that I cannot add serice as a "Web link" because it is disabled in the Windows Phone 7 application .
  • After adding serive to my application, I get a set of classes. And one of them is ReportExecutionServiceSoapClient;
  • ReportExecutionService is not available.
  • I'm not quite sure how to use the ReportExecutionServiceSoapClient api to get a cookie that I can use for WebClient.
  • Any sample application on how to use these APIs would be great. I tried to search over the Internet, but could not get more information.

  • Below I tried, but the number of cookies is null.

     ReportExecutionServiceSoapClient client = new ReportExecutionServiceSoapClient(); void MainPage_Loaded(object sender, RoutedEventArgs e) { client.ClientCredentials.UserName.UserName = "XXXX"; client.ClientCredentials.UserName.Password = "XXXX"; client.CookieContainer = new CookieContainer(); client.LogonUserCompleted += new EventHandler<System.ComponentModel.AsyncCompletedEventArgs>(client_LogonUserCompleted); client.LogonUserAsync("XXXX", "XXXX", "xxxx.reporting.windows.net"); } void client_LogonUserCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e) { int count = client.CookieContainer.Count; // NULL COOKIE COUNT!!! }

The following is a ClientConfig file. I added enableHttpCookieContainer = "true".

  <configuration> <system.serviceModel> <bindings> <basicHttpBinding> <binding name="ReportExecutionServiceSoap" maxBufferSize="2147483647" enableHttpCookieContainer="true" maxReceivedMessageSize="2147483647"> <security mode="Transport" /> </binding> </basicHttpBinding> </bindings> <client> <endpoint address="https://xxxx.reporting.windows.net:443/ReportServer/ReportExecution2005.asmx" binding="basicHttpBinding" bindingConfiguration="ReportExecutionServiceSoap" contract="SqlReportingService.ReportExecutionServiceSoap" name="ReportExecutionServiceSoap" /> </client> </system.serviceModel> </configuration>
  • Please let me know what is missing.

solvable - Finally, I can download the xml file from the Azure Sql report server. - Below is the solution. - First add the service link to the Sql report server. - Then run ReportExecutionServiceSoapClient :: LogonUserAsync. - Use the resulting cookie with WebClient for further request.

  public class WebClientExtended : WebClient { private CookieContainer myContainer; private HttpWebRequest myRequest; [SecuritySafeCritical] public WebClientExtended() { } [SecuritySafeCritical] public WebClientExtended(CookieContainer containter) { myContainer = containter; } public CookieContainer Cookies { get { return myContainer; } set { myContainer = value; } } protected override WebRequest GetWebRequest(Uri address) { myRequest = (HttpWebRequest)base.GetWebRequest(address); myRequest.Method = "GET"; myRequest.CookieContainer = Cookies; return myRequest; } protected override WebResponse GetWebResponse(WebRequest request, IAsyncResult result) { return myRequest.EndGetResponse(result); } } ReportExecutionServiceSoapClient rs = new ReportExecutionServiceSoapClient(); string servername = "XXXX.reporting.windows.net"; string uname = "XXXX"; string password = "XXXX"; public void LoadReport() { rs.ClientCredentials.UserName.UserName = uname; rs.ClientCredentials.UserName.Password = password; rs.CookieContainer = new CookieContainer(); rs.LogonUserCompleted += new EventHandler<System.ComponentModel.AsyncCompletedEventArgs>(rs_LogonUserCompleted); rs.LogonUserAsync(uname, password, servername); } void rs_LogonUserCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e) { WebClientExtended webClient = new WebClientExtended(rs.CookieContainer); webClient.Credentials = new NetworkCredential(uname, password, servername); webClient.DownloadStringCompleted += new DownloadStringCompletedEventHandler(webClient_DownloadStringCompleted); webClient.DownloadStringAsync(new Uri(String.Format("https://{0}/ReportServer?%2fDemo2.rdl&rs:Command=Render&rs:Format=XML", servername))); } void webClient_DownloadStringCompleted(object sender, DownloadStringCompletedEventArgs e) { MessageBox.Show(e.Result); }
+4
source share
2 answers

The ServerReport class calls the LogonUser method on ReportExecutionService to get a cookie for forms authentication that will be used in all other requests (for example, rendering in XML).

Using credentials in WebClient in your WP7 application does not apply to this. You have 2 options:

  • Try to get a cookie by calling LogonUser and use it in your WebClient
  • Do not use WebClient, but visualize the report through ReportExecutionService

Take a look at the post in Windows Azure SQL Reporting: using the SOAP API to get started with this service in SQL Azure.

0
source

In the update approach: In fact, you could get cookies even if the code says that they are not. They are simply hidden somewhere inside the CookieContainer due to the “security stuff”.

Just try pasting the same CookieContainer instance into the next WebRequest and try if it authenticates.

0
source

More articles:


All Articles