none
Any disadvantages installing SSIS on IIS7 Web Server RRS feed

  • Question

  • We are adding the capability of executing SSIS export packages from ASP.net applications hosted on II7 webserver residing in the DMZ. The packages are required to export data from the Database server thereby users would be able to download the exports on their local computers.

    We are considering to install SSIS on the web server so that the dtsx on the web server  can run. So, I'm just wondering if installing SSIS on the web server could cause security vulnerabilities and if so what alternative we have in running SSIS packages from ASP.net without having SSIS installed on the web server( rather install it on the database server behind the DMZ).If we need to install SSIS on the database server which is behind DMZ, what would be the approach to implement it.

    Thanks,

    Jedd.

    Friday, August 7, 2009 12:45 AM

Answers

  • SSIS requires a valid SQL Server license, and the SSIS feature (in SQL setup) has to be installed to run packages with DTExec or SSIS APIs.

    Security mostly depends on what your packages need to do, and which Windows domain account they use to connect to database servers in your company. Keeping the account which calls the runtimes as minimal as possible is a must. If you are connecting SSIS to SQL Server, make sure the account can only write to the tables they have to. If your package is just reading data, and the data is not so sensitive, exporting to an file or in a destination table, for the web page to render doesn't seem so bad. Just keep your source data under lock and key if it is sensitive.

    Few options to launch a package from ASP:
    - Process.Start to launch DTExec as a child process under the security context of choice on the IIS 7 machine.
    - SSIS APIs to do .Execute() and start the package under the security context of choice, however the big drawback is the child threads (SSIS uses many) will take their security token from the host process w3wp.exe and not the impersonated thread, which usually doesn't have enough permissions to do anything hurtful on the system, but also doesn't have enough permissions to do much useful in a database either.
    - SQL command from your ASP to call sp_start_job in MSDB database and run the package underneath SQL Server Agent (as the service account itself, or with a limited permissions proxy account)
    - SQL command xp_cmdshell option to run the DTExec process on the SQL Server machine. xp_cmdshell can be configured to use a proxy account with limited credentials. Can be too wide open for comfort, since if the xp_cmdshell is called to do something bad its riskier.

    Thx, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
    • Marked as answer by Tony Tang_YJ Wednesday, August 12, 2009 7:50 AM
    Friday, August 7, 2009 4:59 AM