February 5, 2019 at 12:43 pm
We have been tasked with collecting data for the DW from a MariaDB instance which is in a separate data center with no VPN connection. We built an SSIS package which uses a script task to start a new process (C# system.diagnostics.process.start) to open an SSH connection with port forwarding using PLink to our target Maria DB instance. The script task stores the process ID in a variable so that another script task at the end of the package can kill the process after the package has retrieved and processed the needed data.
This package works perfectly whenever it is run from Visual Studio; however, once we try to run it from the SSIS Catalog, the package fails because none of the connections to the MariaDB instance were able to be made. Both the SSIS service as well as the SQL Agent service run under their own dedicated accounts (Active Directory) and we have ensured that both have access to all the appropriate local and network resources. We have also ensured that our Kerberos configuration, including delegation, is correct and functioning as expected. We have also ensured that our project parameters and environments are configured correctly and the package validation does report success (data flow tasks have delayed validation). The host key has already been cached. All SSIS and SQL Server components are 2017 Enterprise CU13. Running the job under a proxy account, even a Domain Admin, doesn't work either.
Everything we've found leads us to believe that there is a disconnect between running PLink from an interactive session and running it in a non-interactive session, but we just can't seem to work our way around this problem. Anyone have any guidance on either what is going on with our SSH connection or how better to retrieve data from a MariaDB instance that is only accessible using SSH with port forwarding?
March 20, 2019 at 10:09 am
Well, we never were able to figure this out. We instead set up a site-to-site VPN to replace the SSH tunnel which, as one would expect, resulted in a successful outcome.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply