Charitable Health Foundation: SSRS Connectivity & Development

Organization

XTIVIA’s client is an independent charitable patient assistance foundation dedicated to providing financial aid to patients and families burdened by high medical costs. Their services cover co-payments, coinsurance, deductibles, and other health-related expenses.

The foundation manages over 70 disease programs, each focusing on FDA-approved medications for specific diseases. Since its inception in 2009, the organization has supported over 125,000 children and adults, providing treatment assistance and helping manage life-threatening, chronic, or rare diseases.

The foundation is recognized for its excellence and transparency, holding a GuideStar Platinum Seal of Transparency, a five-star GreatNonprofits rating, and an ImpactMatters certification.

Challenge

Our client configured SQL Server 2017 within an AWS environment using Amazon Machine Image (AMI) on EC2. While SQL Server, SSIS, and other components were successfully configured, they encountered connectivity issues with SQL Server Reporting Services (SSRS).

Connectivity Issues:

• Data was visible in preview mode when launching Report Builder on the server. However, accessing the report via the Web URL resulted in an “Unable to connect to the remote server” error.
• From a remote machine using HTTPS, attempting to launch Report Builder returned an “Unable to connect to the server that is specified in the URL” error.
• Connecting to the SSRS portal using HTTP from the server worked fine, but accessing it from a remote computer resulted in a “Secure Connection Failed” error.

Report Development Challenges:

• The client also lacked internal expertise to develop complex SSRS-paginated reports with advanced filters, export functionality (e.g., PDF), and a mix of static and dynamic content such as images, text, and Month-to-Date (MTD) and Year-to-Date (YTD) figures.

Faced with these challenges, the client turned to XTIVIA, their trusted partner, to resolve the SSRS connectivity issues and develop the required reports.

Solution

XTIVIA conducted a comprehensive analysis of the SQL Server and SSRS environment on AWS, identifying several key issues:

• Port Mismatch: The secure site was configured to work with port 4441 (used by the client) from all source machines, but Report Builder expected port 443 during report execution.
• IIS Interference: IIS was blocking port 443, which was used for SSRS secure portal and Report Builder access.
• Database Configuration Errors: URL registration in the database was not correctly configured.

Steps Taken to Resolve the Connectivity Issues

XTIVIA implemented the following steps to address these challenges:

1. Identify Conflicting Services: Located services using port 443.
2. Reconfigure IIS Services: Removed IIS services from local service roles and restarted the Windows server.
3. Issue a New Certificate: Removed the existing certificate and issued a new one with a unique name to indicate it was for the SSRS server.
4. Certificate Placement: Ensured the new certificate was placed under trusted certificates.
5. Replace Secure Port Certificate: Removed the certificate associated with port 4443 and added a secure certificate issued by the local Certificate Authority (CA) for port 443.
6. Reconfigure the SSRS Database: Updated the SSRS database with the latest Web/Portal URLs and completed the setup using the “Change Database” wizard.
7. Validation: Used the new URLs post-database configuration and validated that all connectivity issues were resolved.

Delivering Client Success

After resolving the connectivity issues, XTIVIA successfully developed and delivered the required Microsoft enterprise reporting application(SSRS)-paginated reports. Key achievements included:

• Ensuring the report output matched the client-provided PDF template.
• Performing extensive testing to validate that both static and dynamic content, such as images and MTD/YTD figures, appeared correctly on the reports.

BUSINESS RESULT

With XTIVIA’s Expertise, the Client Achieved Notable Organizational Improvements

XTIVIA’s seasoned SSRS professionals implemented a solution that resolved the client’s connectivity issues and enhanced their Microsoft Reporting tool(SSRS) reporting environment. This allowed seamless access to business-critical information, significantly reducing the manual effort required to generate PDF reports while mitigating the risk of errors caused by manual processes.

The timely availability of the “Program Grant Report” for each assistance program ensured uninterrupted operations, enabling the client to continue supporting patients with minimal downtime.

KEYWORDS

SSRS Reports Development, SSRS Connectivity Issues, SQL Server on AWS, SSRS on AWS, Microsoft Reporting, Enterprise Reporting, Microsoft SSRS, SQL Server Reporting Services,Report Builder, SSRS Paginated Reports,Secure Connection Issues in SSRS, AWS SQL Server AMI

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.