TAF SSRS Connectivity and Development

Organization

Our client is an independent charitable patient assistance foundation that helps patients and families facing high medical out-of-pocket costs by providing financial assistance for their copayments, coinsurance, deductibles, and other health-related expenses. Since their founding in 2009, our client has helped nearly 125,000 children and adults access the treatment they need to stay healthy or manage a life-threatening, chronic, or rare disease. Our client holds a GuideStar Platinum Seal of Transparency, a five-star rating from GreatNonprofits, and has been rated as a nonprofit with impact by ImpactMatters.

Challenge

Our client configured SQL Server 2017 on an AWS environment using an Amazon Machine Image (AMI) on EC2. While they successfully set up SQL Server, SSIS, and other components, they encountered connectivity issues with SSRS. Reports in Report Builder displayed correctly in preview mode on the server; however, accessing the reports via the Web URL resulted in an “Unable to connect to the remote server” error. Additionally, launching Report Builder from a remote machine using HTTPS caused an error stating, “Unable to connect to the server that is specified in the URL.” However, connectivity through HTTP worked without issues. On the server itself, the SSRS portal functioned correctly, but attempting to connect to the portal remotely led to a “Secure Connection Failed” error.

Beyond connectivity issues, the client lacked internal expertise to create a complex SSRS paginated report with filters and the ability to export to PDF. This report required both static content (images and text) and dynamic data, including MTD and YTD numbers.

Our client approached a trusted partner, XTIVIA, to resolve the SSRS connectivity issues and develop the SSRS report.

Solution

XTIVIA analyzed the SQL Server and SSRS environment on AWS and identified the following issues:

• The secure site was configured to use port 4441 (as set by the client) and was accessible from all source machines; however, Report Builder expected port 443 for report execution.
• IIS was blocking port 443, which caused issues when attempting to use it for the SSRS secure portal and Report Builder access.
• Database configuration was incomplete, specifically the registration of URLs in the database.

XTIVIA implemented the following steps to resolve the SSRS connectivity issue:

• Identified the services utilizing port 443.
• Removed IIS services from the local service roles and restarted the Windows server.
• Removed the existing certificate and issued a new one with a unique name, explicitly indicating that it was for the SSRS server.
• Ensured the new certificate was placed under Trusted Certificates.
• Replaced the certificate bound to port 443 with the newly issued secure certificate from the local Certificate Authority (CA).
• Reconfigured the SSRS database to include the updated Web and Portal URLs.
• Completed the setup using the Change Database wizard.
• Validated the new URLs post-configuration, confirming that all connectivity issues were resolved.

XTIVIA delivered the complex SSRS paginated reports after fixing the connectivity issues. We ensured that the report output exactly matches the PDF template provided by our client. Extensive testing was performed to ensure the static and variable content appear on the PDF report as expected.

BUSINESS RESULT

• Established a properly connected SSRS reporting environment.
• Reduced manual effort required for generating PDF reports.
• Mitigated the risk of errors in reports caused by manual processes.
• Ensured the timely availability of the Program Grant Report for each assistance program.

KEYWORDS
SSRS Reports Development, SSRS Connectivity Issues, SQL Server on AWS, SSRS on AWS

SOFTWARE
SQL Server 2017 Enterprise, SSRS, Windows Active Directory, IIS, AWS SQL Server AMI, AWS WorkSpaces

XTIVIA needs the contact information you provide to us to contact you about our products and services. You may unsubscribe from these communications at anytime, read our Privacy Policy here.