Re: Access while running DTS package using stored procedure
- From: Thanks <Thanks@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 29 Aug 2005 19:39:01 -0700
Hi Allan,
I'm experiencing the exact same problem, you have stated clearly that,
>>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."
What would you advise to workaround this problem?
Note that our SQL server is automatically started when the Win 2003 server
is started, i.e. with the user localhost\SYSTEM. We thought about setting up
a dedicated user ID to start SQL server but we are really reluctant to do
that due to a number of reasons. What else could we do?
Much appreciate if you or anyone could shed some lights.
Thanks,
calvin
calvinleung@xxxxxxxxxxxxx
"Allan Mitchell" wrote:
> 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 ***
>
>
>
.
- Prev by Date: Adding final register to a flat file obtained from a DTS
- Next by Date: Skip a step in DTS
- Previous by thread: Adding final register to a flat file obtained from a DTS
- Next by thread: Skip a step in DTS
- Index(es):
Relevant Pages
|
