none
Cannot UtilizeConnection via SSH from MS Sql Server to Postgresql DB (SSMS, SSIS, Cygwin) RRS feed

  • Question

  • 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
    Friday, October 18, 2019 6:47 PM

Answers

  • I have found the solution to the issue. Once I was able to narrow the problem down to how SSIS was communicating with the ODBC drive and the package never even making it to the driver, I was able to form a much more specific question which, with the help of another in a different forum, led me to the following links:

    https://stackoverflow.com/questions/1273489/ssis-cant-find-32-bit-odbc-driver-in-debug-mode

    https://stackoverflow.com/questions/13943765/why-does-my-odbc-connection-fail-when-running-an-ssis-load-in-visual-studio-but/13960063

    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!

    Tuesday, October 22, 2019 8:17 PM

All replies

  • Hi Christopher,

    What do you mean under dubug mode? SSIS projects have no such notion.


    Arthur

    MyBlog


    Twitter

    Friday, October 18, 2019 7:39 PM
    Moderator
  • Thank you for responding to my post Arthur!

    The debug mode I am referring to the toolbar at the top of the window (Debug --> Start Debugging (F5) ). If it is called something else please let me know as I have only been working with SSIS for 9 months.


    Here is a link to the Microsoft documentation that briefly covers it:

         https://docs.microsoft.com/en-us/sql/integration-services/run-a-package-in-sql-server-data-tools?view=sql-server-2014

    Friday, October 18, 2019 7:56 PM
  • Hi Christopher,

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

    Please check if the connection can test successfully when you use parameters

    to set the connection string.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, October 21, 2019 6:42 AM
  • It does successfully test and the package properly executes completely when run with debug.
    Monday, October 21, 2019 1:04 PM
  • Questions from a different website forum with applicable answers. Hopefully this helps give more clarity.

    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)

    Monday, October 21, 2019 8:22 PM
  • I have found the solution to the issue. Once I was able to narrow the problem down to how SSIS was communicating with the ODBC drive and the package never even making it to the driver, I was able to form a much more specific question which, with the help of another in a different forum, led me to the following links:

    https://stackoverflow.com/questions/1273489/ssis-cant-find-32-bit-odbc-driver-in-debug-mode

    https://stackoverflow.com/questions/13943765/why-does-my-odbc-connection-fail-when-running-an-ssis-load-in-visual-studio-but/13960063

    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!

    Tuesday, October 22, 2019 8:17 PM