question

Spunny-0938 avatar image
0 Votes"
Spunny-0938 asked LanHuang-MSFT answered

web service architecture suggestion

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

dotnet-csharpdotnet-aspnet-webpages
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LanHuang-MSFT avatar image
0 Votes"
LanHuang-MSFT answered

Hi @Spunny-0938,
Maybe you could try using SSIS to call a web service and then store and query the results.
https://docs.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://docs.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://docs.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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.