I have an Aurelia.io application with a view where I want to embed a SQL Server Report result into an iframe. Do I simply use an external src or go with a server to server execution and return the result locally?
-
[Im just learning the limitations of wordpress with respect to it removing iframe code and messing with code snippets. Ill have to make my own server and port this over to deal with this myself]
The site is tested in a secure page so the first thing to know is that if you call the report directly as the src of the iframe that it also needs to be secure or there will be a mixed mode error returned by the client. Just make sure the url to the report is https.
The second issue is that the content for the report is served up from a different domain. So there would be same origin errors resulting from this call as well. What we need to do is have the server we are calling return the appropriate headers to allow the content on a different origin. This is done by adding the Embed parameter to the url of the report.
The next problem is that the report server requires authentication. So going to the page will result in a prompt for a network username and password through basic authentication for the content coming from the report server. That part is dealt with by the user entering the information when prompted or by configuring the report server to not require authentication. We will not discuss configuring the report server to not require authentication.
The following is an aurelia.io view snippet that binds the id of a plan object to build the url required to return the report that would otherwise be viewed in report server. We will remove the parameter inputs and the toolbar and just show the report itself.
This assumes a project called Report Project1 and a report named Report1.
Assume the following applies to an iframe and these are the attributes required. The iframe itself is not shown because WordPress strips the code.
if.bind="plan.id"
src.bind="'https://localhost/ReportServer?
%2fReport%20Project1/Report1&rs:Embed=true&rc:Parameters=false&rs:Command=false&PlanId=' + plan.id"
width="100%"
height="400"
Now the issue is the user could view the src of this iframe and change the plan to be a plan they do not have rights to and execute it in the browser. Barring authorization inside the report itself we need another way to control security on this file. What we can do instead is call our api to Response.Write the result of this call to the reporting service and in our api perform whatever security we have on the user and access to plans. Lets assume we have a controller named PlanApiController with a method named ReportHtml that takes in the id of the plan we want to use in our report.
Note, we will not validate the plan in the following code, but we could. So lets change our Aurelia view as follows:
Assume the following applies to an iframe and these are the attributes required.
if.bind="plan.id"
src.bind="'https://localhost:44319/api/planapi/ReportHtml/' + plan.id" width="100%"
height="400"
One thing we have to account for here is that report server still needs authentication to take place. The client will not receive a prompt. So we are both handling this annoyance and also creating a way for our api to verify the user parameters to the report before calling it. We will not actually perform that validation as it is trivial.
using System.Net;
using System.Net.Http;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
[HttpGet]
[Route("[action]/{id:int}")]
public async Task ReportHtml(int id, CancellationToken cancellationToken) {
var asPDF = "&rs:Format=PDF"; //leave blank to return the html as if it were embedded and called directly from ui
//If the site its hosted in is https then a mixed mode error will result, in this case it isn't really required since its server to server and the browser wont
//throw an error but for sake of consistency between client based examples we will do the same here.
var protocal = "https://"; //https is required when using this clientside, but server to server does not require it because clientside does not see the return as mixed mode
var reportPath = "/Report Project1/Report1"; //path should start with a slash
var embed = "&rs:Embed=true"; //embed gets around same origin issues as it directs the report server to add necessary headers to the result
var hideParameters = "&rc:Parameters=false"; //dont show the parameters inputs
var hideCommandBar = "&rs:Command=false"; //dont show the navigation and control toolbar
var planId = $"&PlanId={id}"; //this is a parameter required by the report itself
var url = $"{protocal}localhost/ReportServer?{reportPath}{asPDF}{embed}{hideParameters}{hideCommandBar}{planId}";
Console.WriteLine("GET: + " + url);
//reference url to how to do this
//https://rocky1978.wordpress.com/2012/12/10/c-how-to-access-website-with-windows-credential-ntlm/
//Credentials are those needed to access the report server site itself,
//not required to access the database inside the report. It is
//possible to set the server up to not require a login and
//we might do this in an intranet scenario
var credential = new NetworkCredential("username", "password", "domain");
var myCache = new CredentialCache { { new Uri($"{protocal}localhost/"), "NTLM", credential } };
// Add the target Uri to the CredentialCache with credential object
// Create an HttpClientHandler to add some settings
var handler = new HttpClientHandler {
AllowAutoRedirect = true,
Credentials = myCache
};
// ... Use HttpClient.
HttpClient client = new HttpClient(handler);
HttpResponseMessage response = await client.GetAsync(url, cancellationToken);
HttpContent content = response.Content;
// ... Check Status Code
Console.WriteLine("Response StatusCode: " + (int)response.StatusCode);
if (string.IsNullOrEmpty(asPDF)) {
// ... Read the string. when html output
var result = await content.ReadAsStringAsync();
await Response.WriteAsync(result, cancellationToken);
}
else {
var result = await content.ReadAsByteArrayAsync();
await Response.Body.WriteAsync(result, 0, result.Length, cancellationToken);
}
}
Resources used while researching this feature
https://docs.microsoft.com/en-us/sql/reporting-services/export-a-report-using-url-access?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/reporting-services/report-builder/rendering-to-html-report-builder-and-ssrs?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/reporting-services/report-builder/export-reports-report-builder-and-ssrs?view=sql-server-2017
https://social.msdn.microsoft.com/Forums/SECURITY/en-US/9ef2f893-648b-46d3-9339-9fd06075570a/ssrs-prompting-for-usernamepassword-when-trying-to-access-the-report-installed-on-a-server-from?forum=sqlreportingservices
https://docs.microsoft.com/en-us/sql/reporting-services/security/configure-windows-authentication-on-the-report-server?view=sql-server-2017
https://stackoverflow.com/questions/12166202/reporting-services-ssrs-prompts-with-username-password
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/350d4ced-548c-48b5-87f0-0afaaa0f834f/how-to-hide-parameters-in-reporting-url-in-ssrs-2008?forum=sqlreportingservices
https://app.pluralsight.com/library/courses/sql-server-reporting-playbook/table-of-contents
https://hajloo.wordpress.com/2010/05/06/how-to-convert-rdl-files-to-rdlc-files/
https://docs.microsoft.com/en-us/sql/reporting-services/report-data/specify-credential-and-connection-information-for-report-data-sources?view=sql-server-2017
httpclient with credentials
https://gist.github.com/bryanbarnard/8102915
https://rocky1978.wordpress.com/2012/12/10/c-how-to-access-website-with-windows-credential-ntlm/ <—this one works