Charitable Health Foundation: SSRS Connectivity & Development

Organization

XTIVIA’s client is an independent charitable patient assistance foundation that provides financial assistance to patients and families with high medical costs by offering financial services for their co-payments, coinsurance, deductibles, and other health-related expenses.

They manage nearly 70+ disease programs, each of which covers the FDA-approved medications that treat a specific disease. Since their founding in 2009, our client has given aid to nearly 125,000+ children and adults, whether through treatment assistance or helping them manage a life-threatening, chronic, or rare disease. Our client has a GuideStar Platinum Seal of Transparency, a five-star GreatNonprofits rating, and is an ImpactMatters-rated nonprofit.

Challenge

Our client configured SQL Server 2017 within an AWS environment using AMI (Amazon Machine Image) on EC2. They were able to configure SQL Server, SSIS, and other components, but were experiencing connectivity issues with SSRS. When Report Builder was launched on the server, the data was visible on the preview mode, but when they tried to access the report from the Web URL, they were experiencing an “Unable to connect to the remote server” issue. When Report Builder was launched from a remote machine using HTTPS, they would get the error “Unable to connect to the server that is specified in the URL.” Connectivity using HTTP would work fine, connecting to the SSRS portal on the server itself would work fine, but when they connected to the SSRS portal from a remote computer, they would get the error “Secure Connection Failed.”

In addition to connectivity issues, our client didn’t have the internal expertise to develop complex SSRS-paginated reports with filters that could be exported to PDF. The report has static content (images and text) and variable content in MTD and YTD numbers. The client then 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 found the following:

• The secure site was working with port 4441 (used by the client) from all source machines, but Report Builder was expecting 443 port during report execution,
• IIS was blocking the port 443 when we tried to use 443 for SSRS secure portal and for accessing through Report Builder; and,
• The database configuration (registering URLs into the database) was not done correctly.

XTIVIA followed the steps below to resolve the connectivity issue

• Found the services using port 443
• Removed IIS services from local services roles and restarted the Windows server
• Removed the existing certificate and issued a new certificate with a unique name indicating that the certificate is for SSRS server
• Ensured that the certificate was placed under trusted certificates
• Removed the certificate with 4443 port and added 443 secure certificates issued by local CA
• Reconfigured the SSRS database with the latest Web/Portal URL
• Followed the “Change Database” wizard to complete the setup
• Used the new URLs after the database setup and validated that all the connectivity issues were resolved

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

BUSINESS RESULT

With the help of XTIVIA’s seasoned SSRS professionals, the client saw notable improvements across their entire organization. With the solution in place, their SSRS reporting environment was properly connected, which gave them access to business-critical information. This also led to a significant reduction in the manual effort required to produce PDF reports, and it mitigated the risk of errors on the reports due to manual effort. Ultimately, it gave them timely availability of the “Program Grant Report” for each assistance program, and they were able to continue helping patients with little-to-no downtime.

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.