Cannot UtilizeConnection via SSH from MS Sql Server to Postgresql DB (SSMS, SSIS

  • cboone

    SSC Journeyman

    Points: 76

    I have an SSIS project that I am trying to deploy. When I run in debug mode it executes successfully. Outside of debug mode, both locally and deployed, I get the following error meesages:

    Error: 2019-10-17 19:24:34.15

    Code: 0xC0014020

    Description: An ODBC error -1 has occurred.

    End Error

    Error: 2019-10-17 19:24:34.15

    Code: 0xC0014009

    Description: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server.

    End Error

    Error: 2019-10-17 19:24:34.15

    Code: 0xC0014009

    Description: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server.

    End Error

    Error: 2019-10-17 19:24:34.15

    Code: 0xC00291EC

    Description: Failed to acquire. Connection may not be configured correctly or you may not have the right permissions on this connection.

    End Error

    We have a persistent ssh set up for the problem connection (ODBC Connector), which I have identified, that is running as a service. This issue occurs when ssh is not run as a service and I just have it open as well.

    Something is changing between debug and non-debug mode and I cannot figure out what it is.

    I think the underlying issue is a credential problem but It appears like the package is executed

    by the same user (which has full permissions) either way I run it.

    Things I have tried:

    Dtexec was set to use 64 bit (via system environment variables). I changed this to 32 bit and had the same results.

    Testing the connection under right-click connection manager--> edit, returns "success".

    Through cmd prompt I can ping the target server.

    Tried converting connection managers to project connection managers.

    Re-created project/package from scratch.

    Verified it is being executed by the correct user that has the needed permissions.

    Set delay validation to "True"

    Set protection level to "DontSaveSensitive" and ensured connection is using a connection string created with parameters.

    Enabled logging to try and obtain a more detailed error

    Checked the registry using Procmon64 to see if I can catch any credential issues there but have not found any.

    In deployed state:

    Run as a job under proxy user that has needed permissions (gave sysadmin temporarily to ensure this wasn't a block).

    Run using parameters to set the connection string.

    Environment Information:

    Running on Windows Server 2016 (Cloud)

    Visual Studio 2017 (SSDT) Version 15.9.16

    SQL Server Management Studio Version 18.2 (15.0.18142.0)

    SSH created using Cygwin and a RSA key

  • Site Owners

    SSC Guru

    Points: 80380

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • luismarinaray

    SSCrazy

    Points: 2794

    Friend,

    Have you checked, that you can connect, as a client to your PostgreSQL Server  from another computer?

    Have you checked that you are allowed to connect to PostgreSQL with your user specifications in the ETL package ?

    Have you checked that you are using the right ODBC  Postgresql Driver 32 or 64 bits ?

    Have you checked that you are using the right ODBC  Postgresql Driver 32 or 64 bits without SSH?

    Have you checked from dtexec that you are running without errors in your development or production machines ? Perhaps running dtexec package.dtsx from various machines in command prompt, could give more clues.

    Good Luck

  • cboone

    SSC Journeyman

    Points: 76

    Have you checked, that you can connect, as a client to your PostgreSQL Server from another computer?

    Yes I have with success but they use PUTTY to build the SSH tunnel. This server is running cygwin with autossh as a service.

    Have you checked that you are allowed to connect to PostgreSQL with your user specifications in the ETL package ?

    Yes, the connection successfully tests with the package as does the ODBC driver from Windows "ODBC DATA Sources"

    Have you checked that you are using the right ODBC Postgresql Driver 32 or 64 bits ?

    Yes, I have verified that all involved aspect of the process are using 32 bit. I was running dtexec in 64 bit mode but changed everythin the 32 in an attempt to solve any compatability issues.

    Have you checked that you are using the right ODBC Postgresql Driver 32 or 64 bits without SSH?

    Yes, I am using 32 bit as I have read that since Visual Studio 2017 (SSDT) only runs as 32 bit. Due to this I cannot see 64 bit connectors.

    Have you checked from dtexec that you are running without errors in your development or production machines ? Perhaps running dtexec package.dtsx from various machines in command prompt, could give more clues.

    Tested per your suggestion: I ran it using both the 32 and 64 bit versions of dtexec. I recieved the same errors as posted in the origional problem. I am working on our dev machine. I have also attempted this on my local where it was developed and it did not work (my local uses Putty to establish the ssh tunnel)

    • This reply was modified 3 weeks, 2 days ago by  cboone.
  • luismarinaray

    SSCrazy

    Points: 2794

    Friend,

    Please, enable and check in your PostgreSQL server, error reporting log files and keep an eye in the log, just to check that you are really connecting or disconnecting, the issue should be related to this, since the error in SSIS is can't acquire connection, let's suppose that ssh is working fine...

    good luck

  • cboone

    SSC Journeyman

    Points: 76

    So we have made some progress in diagnosing where the issue is occurring and have narrowed it down to between SSIS and the ODBC driver. When I watch the database for connection requests I can see the connection when I do connection manager --> edit --> test.

    When I switch to non-debug mode I am not able to see any communication with the database. The package still runs in debug mode but I think this may have something to do with the connection and its existence still sitting in memory.

    All signs keep narrowing down to an issue between SSIS and the ODBC driver. I will update here with more as I progress.

  • cboone

    SSC Journeyman

    Points: 76

    I have now confirmed and pinpointed that the issue is that SSIS outside of debug mode never reaches the ODBC Driver. I enabled logging on the driver and when run in debug I get data in the logs from the connector. In no-debug mode the logs do not populate.

  • luismarinaray

    SSCrazy

    Points: 2794

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88149

    How is the SSH tunnel being opened?  I have seen issues where the calling process - in this case SSIS - is run under a user context that does not have access to start the SSH tunnel and therefore fails when attempting to connect.

    This could also be an issue where SSIS in debug mode has access to cygwin - but in local/deployed mode it isn't available.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • cboone

    SSC Journeyman

    Points: 76

    So it looks like those links were the solution. I had to change the runtime environment to not use 64 bit. Once i changed that, it ran with no issue. I know I had messed with that in the past but it was giving me the error on my dev server:

    Cannot execute the package. The SSIS Runtime for "SQLServer2017" is not properly installed.

    Reinstall the SSIS Runtime for "SQLServer2017".

    Technical details: DTEXEC.EXE for "SQLServer2017" not found. (Microsoft.DataTransformationServices.VsIntegration)I did not think to try it on my local machine as well and once I did, tada, problem fixed!

    Thank you very much for the insightful questions that helped lead me to pinpoint the issue and the links that gave the end solution.

     

     

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply