web service architecture suggestion

Spunny 326 Reputation points
2022-07-27T22:24:29.077+00:00

Currently we generate month statements (bank monthly statements) from asp.net application.
Background:

  1. Data is in sql server tables
  2. we use SSRS reports with parameters to run report and generate pdf files
  3. Have batch file that calls SSRS report passing parameter values

What we did is
asp.net application calls stored procedure.
In stored procedure we call batch file by using xp_Cmdshell and passing parameter values in a loop.
We do this for each client\account in a loop.

Security team want to disable using xp_cmdshell due to security issues. So we are planning to re-architect this.
We were thinking of moving all the logic into web service (like running SSRS reports and generating pdf statements) etc

our asp.net application web server is in DMZ zone. Sys admins do not opens ports from this server. We have only one port open to talk to database server which is sitting on our end.
That is the reason, we had to implement through database.

What are the best options for this kind of requirement.

some one suggested introducing middle tier between web server and sql server. Then middle tier can start the process by spawning sub process.

Thank You

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,246 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,204 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bruce (SqlWork.com) 55,041 Reputation points
    2022-07-28T02:04:59.367+00:00

    You don’t explain what the xp_cmdshell is for. Why do you need an external process? Why don’t you use SSIS instead.?

    0 comments
  2. Lan Huang-MSFT 25,386 Reputation points Microsoft Vendor
    2022-07-28T05:28:59.703+00:00

    Hi @Spunny ,
    Maybe you could try using SSIS to call a web service and then store and query the results.
    https://learn.microsoft.com/en-us/sql/integration-services/control-flow/web-service-task?view=sql-server-ver16

    You can use web service tasks to read data from and send data to a web service.
    https://www.sqlshack.com/ssis-web-service-tasks

    Calling the ssis package from a stored procedure without using xp_cmdshell is discussed in the SSIS documentation.
    https://learn.microsoft.com/en-us/sql/integration-services/run-manage-packages-programmatically/loading-and-running-a-remote-package-programmatically?redirectedfrom=MSDN&view=sql-server-ver16

    Another option would be a CLR procedure that runs the package, so instead of TSQL/xp_cmdshell you use CLR/Dts object model.
    https://learn.microsoft.com/en-us/sql/integration-services/run-manage-packages-programmatically/loading-and-running-a-local-package-programmatically?redirectedfrom=MSDN&view=sql-server-ver16

    Best regards,
    Lan Huang

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments