December 18, 2007 at 7:53 am
We have a DTS package that needs to be run remotely from a user's workstation. I created a login that would allow him to do this. I tested the call from my workstation and it worked but, when he tried to run it, he got an error saying that his session login was not authorized to use this package. The fact that it runs from my computer might be that I am authorized to run it using my Windows login and he is only authorized to run as a SQL user. The call is something like this:
dtsrun /Sservername /Uusername /Ppassword /Npackagename
which I got from books online. This particular package is running on a SQL Server 2000 SP4 server. What could be forcing the call to use the login instead of the SQL user and password supplied by the command line?
Any help would be appreciated. Thanks,
Ed
December 18, 2007 at 8:25 am
Does the SQL user have rights to run the package? It has to be able to read it from MSDB.
If you execute that statement from your workstation, does it work?
December 18, 2007 at 8:36 am
The SQL user does have those rights but, from the users workstation, it is giving an error that his Windows login does not have rights. It works from mine but I have rights to that server.
Ed
December 18, 2007 at 9:28 am
The specific permissions the login needs in msdb are EXECUTE on sp_enum_dtspackages, sp_get_dtspackage, and sp_get_dtsversion.
Would you post the exact error message that the user gets? Have you set package owner and user passwords?
Greg
Greg
December 18, 2007 at 12:59 pm
The actual error is
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_3
DTSRun OnStart: DTSStep_DTSDataPumpTask_4
DTSRun OnStart: DTSStep_DTSDataPumpTask_5
DTSRun OnError: DTSStep_DTSDataPumpTask_5, Error = -2147217843 (80040E4D)
Error string: Login failed for user 'DOMAIN\jdoe'.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
This is the Windows user that is not in /U and /P parameters of DTSRun. The user there specified has more than those permissions.
Thanks,
Ed
December 18, 2007 at 2:47 pm
It looks like the package is starting and the error is generated by DTSStep_DTSDataPumpTask_5. Check the connection used by the Transform Data task and make sure it's set to use SQL Server authentication rather than Windows authentication.
You may find more specific information by enabling package logging and checking the package log after execution.
Greg
Greg
December 19, 2007 at 10:53 am
Greg,
You are right! That was the problem. The connection to SQL within the package was using Windows login.
Thanks,
Ed:)
December 19, 2007 at 2:35 pm
You're welcome. It really makes a difference when we can see the actual error message.
Greg
Greg
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply