Microsoft SQL Server Reporting services (SSRS) is one of the best reporting solution available in market. Since it is coming as free with SQL Server, Most of the companies tries to use it for Reporting solutions. For intranet based appliation, it works well. Now world is moving towards cloud. As Databases are moved to cloud and web based application are common , We need a Reporting solution with perfect 3 tier architecture.
As per Microsoft documentation, SSRS is multi-tiered, with an application layer, server layer, and data layer . Since it follows three tier architecture, Any front end application like report manager, report builder or third party application can access the SSRS application layer via Http/Https connection. When the application is hosted in cloud, port for http or https alone needs to be opened.
Since https port alone needs to be opened, We can think SSRS as perfect solution. You can create a web application by embedding reportviewer and link to the report builder in to your application. Report viewer should help to view the report and Report builder will be helped to design report. If you create your web application as above architecture and test it locally, It will work fine.
The problem will start if you host your application in cloud. When you open the report builder and try to create a dataset, you will end up in getting the following error.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
If you open the 1433 port for sql server to public, Application will work fine. What does mean? Yes. You are correct. Report builder is not a perfect 3 Tier click once application. It connects database directly from client using SQL port.
If you start search the web for this issue, you can find lots of reference about the issue. Someone even created a bug in Microsoft Connect and Microsoft accepted this as a limitation and try to fix in "unknown" version in the future.
It is totally impossible to open SQL port to the universe. Now you are in real trouble. The only way to overcome the issue is to keep specific machine to access Report builder and relax firewall rule to access SQL port from that specific machine.
When raised this issue to Microsoft, they are not ready to accept it as product bug. But they are calling this as a documentation bug. But still the documentation bug was not fixed till the post is published. So If you try to use SSRS in cloud, Be aware of this issue and plan accordingly!
How to use SSRS in cloud environment? Microsoft provided a very good solution earlier and now destroying this again. Let's see about that solution in another post.
 |
| Architecture from MSDN |
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
It is totally impossible to open SQL port to the universe. Now you are in real trouble. The only way to overcome the issue is to keep specific machine to access Report builder and relax firewall rule to access SQL port from that specific machine.
When raised this issue to Microsoft, they are not ready to accept it as product bug. But they are calling this as a documentation bug. But still the documentation bug was not fixed till the post is published. So If you try to use SSRS in cloud, Be aware of this issue and plan accordingly!
How to use SSRS in cloud environment? Microsoft provided a very good solution earlier and now destroying this again. Let's see about that solution in another post.
No comments:
Post a Comment