Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sauce1979
Starting Member
47 Posts |
 Posted - 2015-01-26 : 11:48:01
|
| I am currently working on a SQL Server upgrade project for a a client where I am converting old dts packages to SSIS. However for a few of the packages no conversion is allowed to be done. For these few packages I have to use dts legacy components in SQL 2005 on a windows 2003 server to run them.These packages use CAPICOM via an Active X script in the package to envelope connection string data for security within the package. Consequently I have to register the capicom.dll for the job owner (which will execute the job via proxy) and install private and public key files via internet explorer. I also do this when I am logged in with my account so i can test the package.I have created a SQL Server Agent Job which is used to execute the package. We have a schedule account which is local admin on the server and sysadmin within SQL Server. This account is used to create a credential and then a proxy for the CmdExec subsystem is then created based on this credential. A CmdExec job step is then added to the job. The directory of cmd file which calls the dts package is then entered in the command window.Finally a recurring schedule is added to execute the job every 5 minutes. If i am logged in to the server using the scheduled account the schedule runs successfully. I am also able to run the command file manually by double clicking on it. The DTS package is run successfully. However once the schedule is set up and I log off the machine and log onto my development machine with my normal account and fire up SQL Server. I connect to that instance with the schedule and see that the job is failing with and Active X error in the package. From experience with this package this Active X error occurs when the user executing the package has not registered the capicom.dll. This has already been done for the scheduler account because the job runs when the scheduler account is logged in on the server. It almost seems as though the job will only run if the Scheduler account is logged on. If i log directly on to the server with own user account I am able to manually execute the package via the cmd file which indicates that the capciom.dll is registered under my account. Yet if I try an run the job in SQL Server when I am logged in under my account (using the scheduler account proxy) then the job fails.Does anybody have any idea why this may be happening? Any ideas would be much appreciated |
|
|
|
|
|
SQL Agent job running DTS package running in SQL 2