[archive] scheduled DTS and AcuODBC

[Migrated content. Thread originally posted on 02 January 2006]

Hello erveryone,
I've this strange behaviour: we've got a W2003 wserver running a SQL 2000 Server. On this server there's installed a standalone ODBC client for feeding some SQL databases.
We've got a DTS package that works fine when manually run from dts configuration tool, but when scheduled it doesen't execute correctly, 'cos it's not able to reach open the configured tables.
The Vision files to be accessed via ODBC are on a samba server with particoular security restrictions, so we think the problem is due to the user SQL uses to run the scheduled package.
Has anyone an idea to solve this or to force SQL Agent to use a defined user to run the process?
TIA Giovanni

I figure your problem is that the processes, when ran automatically are ran as Local Administrator, thus it does not see any network resources.

To my knowledge, there are two ways to overcome this;

A) Change so that your process runs in the namespace of a user known to the network (typically you would make a designated user in the domain for this, but I am no IS techie so you will have to ask your IS department how to do this).

B) Copy the resources (Vision data and AcuODBC) locally.

There might be more alternatives as well, but I do not know this that well, I feel confident though, that this is an issue related to the services default to run in Local Admin mode and Local Admins have no access to a network by default.

[Migrated content. Thread originally posted on 02 January 2006]

Hello erveryone,
I've this strange behaviour: we've got a W2003 wserver running a SQL 2000 Server. On this server there's installed a standalone ODBC client for feeding some SQL databases.
We've got a DTS package that works fine when manually run from dts configuration tool, but when scheduled it doesen't execute correctly, 'cos it's not able to reach open the configured tables.
The Vision files to be accessed via ODBC are on a samba server with particoular security restrictions, so we think the problem is due to the user SQL uses to run the scheduled package.
Has anyone an idea to solve this or to force SQL Agent to use a defined user to run the process?
TIA Giovanni

Hi Gisle,
thank U for your suggestion, we temporarily solved thingh using a scheduled task command line dtsrun start of the process: specifying the owner and the user in command line our first test were able to run AcuODBC correctly.
Next days we'll look for working on Domain user launching the service.
I found many infos about it in:
http://support.microsoft.com/?kbid=269074
but I found in MSDN newsgroups many issues on problems related to DTS scheduled run not directly related to that Kb Article.
Again many thanks for your effortless support, and happy new year!
Giovanni

[Migrated content. Thread originally posted on 02 January 2006]

Hello erveryone,
I've this strange behaviour: we've got a W2003 wserver running a SQL 2000 Server. On this server there's installed a standalone ODBC client for feeding some SQL databases.
We've got a DTS package that works fine when manually run from dts configuration tool, but when scheduled it doesen't execute correctly, 'cos it's not able to reach open the configured tables.
The Vision files to be accessed via ODBC are on a samba server with particoular security restrictions, so we think the problem is due to the user SQL uses to run the scheduled package.
Has anyone an idea to solve this or to force SQL Agent to use a defined user to run the process?
TIA Giovanni

Hi Gisle,
thank U for your suggestion, we temporarily solved thingh using a scheduled task command line dtsrun start of the process: specifying the owner and the user in command line our first test were able to run AcuODBC correctly.
Next days we'll look for working on Domain user launching the service.
I found many infos about it in:
http://support.microsoft.com/?kbid=269074
but I found in MSDN newsgroups many issues on problems related to DTS scheduled run not directly related to that Kb Article.
Again many thanks for your effortless support, and happy new year!
Giovanni