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. 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 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 has a GuideStar Platinum Seal of Transparency, a five-star GreatNonprofits rating, and an ImpactMatters-rated nonprofit.

Challenge

Our client configured SQL Server 2017 on the 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 itself, the data was visible in the preview mode but when they tried to access the report from the Web URL, they were getting an “Unable to connect to the remote server” issue. When Report Builder is 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 connect to the SSRS portal from a remote computer, they would get the error “Secure Connection Failed”. In addition to the connectivity issue, our client did not have the internal expertise to develop a complex SSRS paginated report with filters that could be exported to PDF. The report has static content (images and text) and variable content in the form of 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 found that:

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

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 the SSRS server
• Ensured that the certificate was placed under trusted certificates
• Removed certificate with 443 port and added 443 secure certificate 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 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

• Properly connected SSRS reporting environment
• Reduction in the manual effort required to produce the PDF reports
• Mitigated risk of errors on the reports due to manual effort
• Timely availability of the “Program Grant Report” for each assistance program

SOFTWARE

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

KEYWORDS

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

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.