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
October 19, 2019 at 7:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
October 21, 2019 at 6:57 pm
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
October 21, 2019 at 7:11 pm
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)
October 21, 2019 at 10:36 pm
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
October 22, 2019 at 5:12 pm
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.
October 22, 2019 at 6:04 pm
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.
October 22, 2019 at 6:59 pm
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.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 22, 2019 at 8:03 pm
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply