February 7, 2011 at 9:46 am
I have a SSIS 2008 package that has a connection to Sybase database. The package runs fine locally in BIDS, and when I load the package to MSDB on the SQL Server and run from there, it runs fine. When I create a job in Agent (which runs under a different user), I get the following error "The connection manager failed to defect from the transaction". So, under this different user, the Sybase connection is not getting read properly.
I created the correct system DSN's and tested the connection to the Sybase database on the server running SQL Server, they connect no problem
Why would the package run fine in MSDB under my user, and not run correctly under a different user on the same server?
February 8, 2011 at 5:22 am
Stringzz (2/7/2011)
Why would the package run fine in MSDB under my user, and not run correctly under a different user on the same server?
Because the other user doesn't have the same permissions as you have?
Either configure the SQL Server Agent job to run under a proxy account, or give the necessary priviliges to the SQL Server Agent user account.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 8, 2011 at 9:41 am
So I have changed my connections to ADO.net and I did a test where I downloaded one date column and it works for both me and the Agent user. Now, when I change my whole package to use ADO.net to connect to Sybase, I get an error when I use the Agent user. The error is on columns that have strings. I get cannot convert Unicode to Non-unicode error. This package works for me locally and when I run from MSDB (as myself). I am doing a data conversion transformation and converting the strings to DT_STR, that does not work either.
I even tried to convert the strings to varchar in the sybase t-sql, that did not work.
So we are back to the question, why would this work for me, and the Agent user has this issue?
February 8, 2011 at 3:11 pm
The DBA informed me that the system user is a startup user for sql server and agent service and has "SA" rights, so it should have more rights than I have.
Also, I can execute the package in MSDB when I connect to Integration Services remotely, if I Remote Desktop to server and log into Integration Services using the same ID, the package does not run. Weird.
February 9, 2011 at 12:49 am
Stringzz (2/8/2011)
The DBA informed me that the system user is a startup user for sql server and agent service and has "SA" rights, so it should have more rights than I have.Also, I can execute the package in MSDB when I connect to Integration Services remotely, if I Remote Desktop to server and log into Integration Services using the same ID, the package does not run. Weird.
If you're sure the SQL Server Agent account has SA permissions, then it is indeed very weird.
The only thing I can think of is that maybe the Agent is referring to an older version of the package?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 9, 2011 at 1:01 am
Koen (da-zero) (2/9/2011)
Stringzz (2/8/2011)
The DBA informed me that the system user is a startup user for sql server and agent service and has "SA" rights, so it should have more rights than I have.Also, I can execute the package in MSDB when I connect to Integration Services remotely, if I Remote Desktop to server and log into Integration Services using the same ID, the package does not run. Weird.
If you're sure the SQL Server Agent account has SA permissions, then it is indeed very weird.
The only thing I can think of is that maybe the Agent is referring to an older version of the package?
The fact that the SQL Agent account has sa permissions means only that there should be no permissions issues within the local SQL Server environment - but that does not rule out permissions issues when connecting to the sysbase db. The remote desktop problem reinforces this idea. If you remote desktop to a console session (run mstsc /console), I'll bet it will work.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply