Problems Connecting to Sybase using ODBC Drivers
-
Hi All,
I have recently migrated a sql database from an old server to a new one. This database is populated from an old sybase database. I have made the required changes to the SSIS job using visual studio and runs there fine. I have setup the ODBC DSN connectors and they test successfully. However when i try to run it as a job i get the error below.
"ERROR [28000] [Sybase][ODBC Driver][SQL Anywhere]Invalid user ID or password"
Anyone seen this before and can help? The job itself is just a lift a shift, so it takes data from the sybase db and migrates it to a sql db.
Note: I did see another error initially regarding a dsn version but figure out the way around that was to select the 32 bit mode box in the job configuration, once this was done i started to get the error above.
-
Reading the error, my guess would be that you have an invalid username or password in your connection string for the SQL Anywhere driver you are using in SSIS.
I am a bit confused how you (or why you) modified the SSIS job from Visual Studio... I am hoping that is just getting terminology mixed up and not that you are doing something weird and hacky.
NORMALLY, SSIS packages are loaded into a SQL Instance and are called from a SQL Job. The SQL Job would be configured with the parameters needed to connect to the appropriate data sources and then would connect and perform the required tasks. If you aren't passing in any username/password information, then it is VERY likely running as the SQL Server Agent service account. This account MAY not have permissions on our Sybase database.
You will likely get better information from reviewing the Sybase logs, but I suspect that it is a configuration issue on your SSIS job.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Hi Brain, Apologies for the confusion, and thanks so much for answering. This is a legacy system which i have inherited. Will try to answer your queries.
Reading the error, my guess would be that you have an invalid username or password in your connection string for the SQL Anywhere driver you are using in SSIS. -- I have set up the odbc connection on the server and tested the connection. This tests ok,
Test it on the control flow and it tests fine there
I am a bit confused how you (or why you) modified the SSIS job from Visual Studio... I am hoping that is just getting terminology mixed up and not that you are doing something weird and hacky. ---I needed to move the job from one server to another, and used vs to complete those changes. I test the connection in there and that tests successfully too.
NORMALLY, SSIS packages are loaded into a SQL Instance and are called from a SQL Job. --That is where i think the job is failing. I have set the job to run as 32 bit as it was failing on this prior to this error.
You will likely get better information from reviewing the Sybase logs, but I suspect that it is a configuration issue on your SSIS job--- i think you are right here and the issue is with the job.
-
The username and password stored in the ODBC can be different than in the SQL Job, depending on the configuration you set up there. With ODBC connections, the configuration is stored in the registry and with SOME ODBC connections, it doesn't store a username or password pair. SQL Server is an example of one where the username and password are NOT stored. I am uncertain if Sybase is the same thing, but if so, it will need a username/password provided in the connecting application (SQL Server job).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply