locked
Is it common to not allow access to outside resources (web service) from SQL, regarding accessing REST service from SSIS RRS feed

  • Question

  • Is best practice to not allow any access to outside resources, such as a REST service, from a SQL server instance that is behind a firewall? Im not talking about access from the outside to SQL, but rather the ability to access an external resource such as an web service from an SSIS package.

    The alternative would be to have something else access this data and make it available at a location within the firewall.  Then have the SSIS package access the data in this "secure" location.

    Friday, January 26, 2018 4:23 PM

Answers

  • Hello,

    Why do you worry about accessing external resource like Web service; you define 6 perform the access and have full control over it. It's something different when you allow access to the machine where SQL Server is running, then you have to take more care about security and how to protect the system.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by shiftbit Saturday, January 27, 2018 2:22 PM
    Saturday, January 27, 2018 5:48 AM

All replies

  • Hi,

    +1 to the alternative you have told in your OP. It is best to propose this architecture when there is a requirement to migrate data from third party solution providers / merging the data from acquired company.

    Get the agreement to place the files in secured shared folder with your organization and then process the files using SSIS.


    sathya - My blog ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Friday, January 26, 2018 7:35 PM
  • Why not just access the REST endpoint from a script component or script task?

    Something like this (my preference ...)
    http://dennysjymbo.blogspot.be/2013/05/using-json-feed-as-data-source-in-ssis.html




    • Edited by shiftbit Friday, January 26, 2018 7:59 PM sdfgsdfg
    Friday, January 26, 2018 7:56 PM
  • Hello,

    Why do you worry about accessing external resource like Web service; you define 6 perform the access and have full control over it. It's something different when you allow access to the machine where SQL Server is running, then you have to take more care about security and how to protect the system.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by shiftbit Saturday, January 27, 2018 2:22 PM
    Saturday, January 27, 2018 5:48 AM
  • @Olaf - thats my point, im not aware of anything that would be cause for concern when connecting out to get resources, but the suggestion came up when topic was mentioned on my team.
    Saturday, January 27, 2018 2:22 PM