Re: Access while running DTS package using stored procedure

The permissions when executing xp_cmdshell can be different depending on who
is asking.

>From BOL

"When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in
which the SQL Server service is running. When the user is not a member of
the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail. This is true only for
Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and xp_cmdshell is always executed under the security context
of the Windows 9.x user who started SQL Server."

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Sreedhar Kaluva" <sreedhar@xxxxxxx> wrote in message
news:eFgAMgofFHA.3916@xxxxxxxxxxxxxxxxxxxxxxx
> Hi All,
>
> I wrote a DTS Package that can take the data from a .txt file residing
> in a shared folder on a different server (same domain) and dump the data
> into a table.
>
> When I open the DTS package and click on execute Package the package
> works file.
>
> I also wrote a stored procedure in which, I am running the above DTS
> package using XP_CmdShell using something like below:
>
> ********************
> Create PROCEDURE Run_DTSPackage_XYZ
>
> AS
>
> Declare @RunDTS as varchar(1000)
> Declare @Result as int
> Declare @sServerName as VarChar(255)
> Declare @sPkgName as VarChar(255)
> Declare @uPkgID as VarChar(255)
> Declare @uPkgVID as VarChar(255)
>
> Select @sPkgName = 'RUNDTS_XYZ'
> Select @uPkgID = id, @uPkgVID = versionid from msdb..sysdtspackages
> where Name = @sPkgName
> Order by versionID
>
> SELECT @sServerName = CONVERT(varchar(255),
> SERVERPROPERTY('servername'))
> Select @RunDTS = 'DTSRun /S ' + @sServerName + ' /U usr /P usrpwd' + '
> /E /N ' + @sPkgName + ' /G ' + @uPkgID + ' /V ' + @uPkgVID
> EXEC @Result = master..xp_cmdshell @RunDTS
>
> GO
> ***************************
>
> I ran this stored procedure in query analyzer and got an error like
> "Error opening data file and access denaied.
>
> I checked the folder and it has got full access to everyone eventhough I
> want to restrict the access to that folder. Could someone advise me on
> what is causing the access denial when I run the dts package using
> DTSRun command eventhoug the package executes successfully using
> UserInterface in Enterprise Manager? Any clue to resolve the issue would
> be greatly appreciated. I want to have my packages run to get the data
> from different servers via network path.
>
> ***********************
>
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> Error Detail Records:
> Error: 5 (5); Provider Error: 5 (5)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
>
> DTSRun: Package execution complete.
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***


.

Relevant Pages

  • Re: Security
    ... In the main package properties, you can enable package level logging. ... The connection within the package uses a SQL login for the SQL server it connects to, so even though it is executing using the agent, so using the agents permissions, i presumed that the actual connection will be made using the defined login. ...
    (microsoft.public.sqlserver.server)
  • Re: multiple excel file import
    ... Remember DTS packages ... >file here but when executing it says the directories are bad? ... then use on task and a loop in the package. ... global community for SQL Server professionals ...
    (microsoft.public.sqlserver.dts)
  • Re: help! scheduled package does nothing...
    ... executing the package manually. ... Have the package log to text file ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... > Both the sqlserver and the sqlserveragent services are ran by the same ...
    (microsoft.public.sqlserver.dts)
  • Re: Problem Executing DTS Task from VB
    ... There is no way of executing a package on a remote machine, ... >> Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • Re: sp_executesql problem
    ... Perhaps SQL server picked a bad plan and the query is executing for a long time? ... > declare @CNSMPLSEQID varchar ...
    (microsoft.public.sqlserver.programming)
Loading