August 14, 2006 at 8:39 am
hi guys, i have a a simple dts in my sql server , i got the command line syntax from start, run, dtsrunui. it runs perfectly in my computer (I have sql server 2000 installed in my computer) but i need another user to be able to run this command, but he keeps getting error DTSrun not a recognizable.... . do i need to install sql server in his computer? is there a way to go around this?
help!!!
August 14, 2006 at 9:54 am
Installing SQL Server would work but perhaps a bit overkill. i am not sure if the dtsrun executable can be ported to another machine but it may be worth trying at worst it will do nothing and indicate any dependancies there may be at best it may actually work. Never tried that before.
Another option is to set up a job on your SQL Server to run the DTSrun cmd using xp_cmdshell. You can then write a simple app in Visual Basic or C# to connect to your SQL Server and run the stored proc sp_start_job to start the job you set up. This app can then be installed on your colleagues machine
August 14, 2006 at 10:01 am
hi!!! thanks, so what i did was i downloaded the dtsrun.exe from msde2000 and installed it. and it works. one question though.. for it to be able to get no errors, i had to add the user as a sysadmin role in sql server.. do you know why? the command line is not encrypted...
this dts only drops two tables and recreated it them again. the user has access to this tables. to update and delete, am i missing anything?
August 14, 2006 at 10:18 am
its been a while since I looked at DTSrun as I am working on SQL 2005 and packages are now run with dtexec. Mind you there are similar problems and as far as I know its associated with security context as indicated below
from Microsoft support on http://support.microsoft.com/kb/269074/EN-US/
If you manually run a package by using the DTSRun.exe command line utility, the security context is that of the Windows account you used to log in to the computer. If you run the package by using DTSrun.exe through the xp_cmdshell extended stored procedure, the package is run in the context of the account used to start the SQL Server service, provided that the user that executed xp_cmdshell is a member of the Sysadmin role. If the user that executed xp_cmdshell is not an account in the Sysadmin role, then DTSRun.exe runs in the context of the SQLAgentCmdExec account.
If the SQL Server was started using the Local System account, the DTS package has no permissions outside of the computer that is running SQL Server.
If the SQL Server service is started under a Windows NT account, the package has the same rights and permissions as that Windows NT account. If that Windows NT account is a local machine account (as opposed to a domain account), the package does not have any rights outside of that computer. If the Windows NT account is a domain account, the package may be able to access resources on many different computers on that domain.
Sometimes a DTS package contains an object that makes a connection to a data source using Windows NT authentication. The security context used for this connection is the same as the context of the package that is running. If the package is run from a command prompt by using DTSRun.exe, the credentials of the currently logged-in Windows NT account is used. If the package is run as a SQL Server Agent job, then the integrated security connection is made using the account you used to start SQL Agent (assuming that the owner of the package is a member of the Sysadmin role).
August 15, 2006 at 5:20 am
This site has a list of the files required to redistribute a dts package:
http://www.sqldts.com/default.aspx?225
I created a self extracting zip file to install the files on target pc's and a batch file to execute the dtsrun.exe.
There should be no reason to add the user to the sysadmin role (very dangerous). Use SQL security, enter the user name and password of an account that can run the package (In my case it would be my own admin account) then generate the command line with encryption. Copy and paste into the .bat file making sure the file path to the location of the dtsrun is included (saves trying to edit %PATH% on XP) and run.
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
August 15, 2006 at 7:28 am
Thank you! that is exactly what i needed.. i created a batch file (.bat) with the encrypted command using the new sql server login and passworkd, I created. (i used the dtsrunui to get the code) but how do i make this account be able to run the package? do i have to make it be the owner of the package? what permissions do i give to this new sql server login?
thanks again for all your help.
August 15, 2006 at 8:00 am
http://www.databasejournal.com/features/mssql/article.php/3404791
The account has to be the package owner, but not much else. (I think)
John
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply