URGENT - Please help - newbie DTS question  
Author Message
awmoat-wikitemp





PostPosted: Tue Dec 02 20:26:49 CST 2003 Top

SQL Server >> URGENT - Please help - newbie DTS question

Hi,

We have an Access97 database, that lives on a file server running NT40SP6a,
and not running SQL.

We are trying to build a DTS package on an NT40SP6a/SQL7SP4 server that will
open the Access database and populate a SQL database from it.

Everything works fine when we execute the DTS package directly from
Enterprise Manager, because it runs in the logged-in user's security
context. When we try to schedule the package, it fails with the following
result -

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147008507 (80074005)
Error string: Unspecified error
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts.hlp
Help context: 1100
Error Detail Records:
Error: -2147008507 (80074005); Provider Error: 0 (0)
Error string: Unspecified error
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts.hlp
Help context: 1100
Error: -2147467259 (80004005); Provider Error: -534709256 (E020FBF8)
Error string: The Microsoft Jet database engine cannot open the file
'\\<server_name>\<share_name>\db.mdb'. It is already opened exclusively
by another user, or you need permission to view its data.
Error. The step failed.

The DTS package can't run under the Local System account, because it runs on
the SQL server, which has a different Local System account than the file
server, so we can't pass through the authentication on the Share. Similarly,
we can't map a drive to the file server and have it work, because the drive
map is associated with the logged-in user, and doesn't carry over to the
Local System account.

Does anyone have any idea how to make the scheduled package run in a domain
user's context without reconfiguring the SQLServerAgent to run in a domain
account?

Any help is much appreciated.

Thanks.

SQL Server253  
 
 
Greg





PostPosted: Tue Dec 02 20:26:49 CST 2003 Top

SQL Server >> URGENT - Please help - newbie DTS question Hi Ron,
From a security point of view, you cant. What you are asking is to allow a
non-user (Local System) access to a file secured by domain account. Not a
very good idea.

On the other hand have you enabled guest on the server that houses the file?
And have you granted everyone complete rights to this file?

Still not a good idea to do this.

--
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools






> Hi,
>
> We have an Access97 database, that lives on a file server running
NT40SP6a,
> and not running SQL.
>
> We are trying to build a DTS package on an NT40SP6a/SQL7SP4 server that
will
> open the Access database and populate a SQL database from it.
>
> Everything works fine when we execute the DTS package directly from
> Enterprise Manager, because it runs in the logged-in user's security
> context. When we try to schedule the package, it fails with the following
> result -
>
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147008507
(80074005)
> Error string: Unspecified error
> Error source: Microsoft Data Transformation Services (DTS) Package
> Help file: sqldts.hlp
> Help context: 1100
> Error Detail Records:
> Error: -2147008507 (80074005); Provider Error: 0 (0)
> Error string: Unspecified error
> Error source: Microsoft Data Transformation Services (DTS) Package
> Help file: sqldts.hlp
> Help context: 1100
> Error: -2147467259 (80004005); Provider Error: -534709256 (E020FBF8)
> Error string: The Microsoft Jet database engine cannot open the file
> '\\<server_name>\<share_name>\db.mdb'. It is already opened exclusively
> by another user, or you need permission to view its data.
> Error. The step failed.
>
> The DTS package can't run under the Local System account, because it runs
on
> the SQL server, which has a different Local System account than the file
> server, so we can't pass through the authentication on the Share.
Similarly,
> we can't map a drive to the file server and have it work, because the
drive
> map is associated with the logged-in user, and doesn't carry over to the
> Local System account.
>
> Does anyone have any idea how to make the scheduled package run in a
domain
> user's context without reconfiguring the SQLServerAgent to run in a domain
> account?
>
> Any help is much appreciated.
>
> Thanks.
>
>
>


 
 
bharath





PostPosted: Tue Dec 02 20:56:04 CST 2003 Top

SQL Server >> URGENT - Please help - newbie DTS question Hi Ron,

In case you have kept the excel sheet open while running
this, close the same and run it... This is error is
related to the DTS needing exclusive access to run the
same.
regards
bharath
>-----Original Message-----
>Hi Ron,
>From a security point of view, you cant. What you are
asking is to allow a
>non-user (Local System) access to a file secured by
domain account. Not a
>very good idea.
>
>On the other hand have you enabled guest on the server
that houses the file?
>And have you granted everyone complete rights to this
file?
>
>Still not a good idea to do this.
>
>--
>I hope this helps
>regards
>Greg O MCSD
>http://www.ag-software.com/ags_scribe_index.asp. SQL
Scribe Documentation
>Builder, the quickest way to document your database
>http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL
Server Extended
>Property Extended properties manager for SQL 2000
>http://www.ag-software.com/IconExtractionProgram.asp.
Free icon extraction
>program
>http://www.ag-software.com. Free programming tools
>
>
>
>


>> Hi,
>>
>> We have an Access97 database, that lives on a file
server running
>NT40SP6a,
>> and not running SQL.
>>
>> We are trying to build a DTS package on an
NT40SP6a/SQL7SP4 server that
>will
>> open the Access database and populate a SQL database
from it.
>>
>> Everything works fine when we execute the DTS package
directly from
>> Enterprise Manager, because it runs in the logged-in
user's security
>> context. When we try to schedule the package, it fails
with the following
>> result -
>>
>> DTSRun: Loading...
>> DTSRun: Executing...
>> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
>> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
>> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
>> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -
2147008507
>(80074005)
>> Error string: Unspecified error
>> Error source: Microsoft Data Transformation Services
(DTS) Package
>> Help file: sqldts.hlp
>> Help context: 1100
>> Error Detail Records:
>> Error: -2147008507 (80074005); Provider Error: 0 (0)
>> Error string: Unspecified error
>> Error source: Microsoft Data Transformation Services
(DTS) Package
>> Help file: sqldts.hlp
>> Help context: 1100
>> Error: -2147467259 (80004005); Provider Error: -
534709256 (E020FBF8)
>> Error string: The Microsoft Jet database engine
cannot open the file
>> '\\<server_name>\<share_name>\db.mdb'. It is already
opened exclusively
>> by another user, or you need permission to view its
data.
>> Error. The step failed.
>>
>> The DTS package can't run under the Local System
account, because it runs
>on
>> the SQL server, which has a different Local System
account than the file
>> server, so we can't pass through the authentication on
the Share.
>Similarly,
>> we can't map a drive to the file server and have it
work, because the
>drive
>> map is associated with the logged-in user, and doesn't
carry over to the
>> Local System account.
>>
>> Does anyone have any idea how to make the scheduled
package run in a
>domain
>> user's context without reconfiguring the SQLServerAgent
to run in a domain
>> account?
>>
>> Any help is much appreciated.
>>
>> Thanks.
>>
>>
>>
>
>
>.
>
 
 
Heng





PostPosted: Tue Dec 02 21:22:37 CST 2003 Top

SQL Server >> URGENT - Please help - newbie DTS question But the error message shows the file is opened by another user.
DTS is trying to open it exclusively.

-Hank



> Hi,
>
> We have an Access97 database, that lives on a file server running
NT40SP6a,
> and not running SQL.
>
> We are trying to build a DTS package on an NT40SP6a/SQL7SP4 server that
will
> open the Access database and populate a SQL database from it.
>
> Everything works fine when we execute the DTS package directly from
> Enterprise Manager, because it runs in the logged-in user's security
> context. When we try to schedule the package, it fails with the following
> result -
>
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147008507
(80074005)
> Error string: Unspecified error
> Error source: Microsoft Data Transformation Services (DTS) Package
> Help file: sqldts.hlp
> Help context: 1100
> Error Detail Records:
> Error: -2147008507 (80074005); Provider Error: 0 (0)
> Error string: Unspecified error
> Error source: Microsoft Data Transformation Services (DTS) Package
> Help file: sqldts.hlp
> Help context: 1100
> Error: -2147467259 (80004005); Provider Error: -534709256 (E020FBF8)
> Error string: The Microsoft Jet database engine cannot open the file
> '\\<server_name>\<share_name>\db.mdb'. It is already opened exclusively
> by another user, or you need permission to view its data.
> Error. The step failed.
>
> The DTS package can't run under the Local System account, because it runs
on
> the SQL server, which has a different Local System account than the file
> server, so we can't pass through the authentication on the Share.
Similarly,
> we can't map a drive to the file server and have it work, because the
drive
> map is associated with the logged-in user, and doesn't carry over to the
> Local System account.
>
> Does anyone have any idea how to make the scheduled package run in a
domain
> user's context without reconfiguring the SQLServerAgent to run in a domain
> account?
>
> Any help is much appreciated.
>
> Thanks.
>
>
>


 
 
Ron





PostPosted: Wed Dec 03 09:12:25 CST 2003 Top

SQL Server >> URGENT - Please help - newbie DTS question Actually, as a test, to ensure that we had exclusive access to it, we copied
the MDB file to the SQL server's file system, and tried to access it using a
UNC path. We had the same result, the local SYSTEM account is not able to
access the share. Changing the DTS package to use an absolute path
(D:\folder\DB.MDB) allowed us to schedule the package and run it
successfully.

Thanks,
Ron


> But the error message shows the file is opened by another user.
> DTS is trying to open it exclusively.
>
> -Hank
>


> > Hi,
> >
> > We have an Access97 database, that lives on a file server running
> NT40SP6a,
> > and not running SQL.
> >
> > We are trying to build a DTS package on an NT40SP6a/SQL7SP4 server that
> will
> > open the Access database and populate a SQL database from it.
> >
> > Everything works fine when we execute the DTS package directly from
> > Enterprise Manager, because it runs in the logged-in user's security
> > context. When we try to schedule the package, it fails with the
following
> > result -
> >
> > DTSRun: Loading...
> > DTSRun: Executing...
> > DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
> > DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
> > DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> > DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147008507
> (80074005)
> > Error string: Unspecified error
> > Error source: Microsoft Data Transformation Services (DTS) Package
> > Help file: sqldts.hlp
> > Help context: 1100
> > Error Detail Records:
> > Error: -2147008507 (80074005); Provider Error: 0 (0)
> > Error string: Unspecified error
> > Error source: Microsoft Data Transformation Services (DTS) Package
> > Help file: sqldts.hlp
> > Help context: 1100
> > Error: -2147467259 (80004005); Provider Error: -534709256 (E020FBF8)
> > Error string: The Microsoft Jet database engine cannot open the file
> > '\\<server_name>\<share_name>\db.mdb'. It is already opened
exclusively
> > by another user, or you need permission to view its data.
> > Error. The step failed.
> >
> > The DTS package can't run under the Local System account, because it
runs
> on
> > the SQL server, which has a different Local System account than the file
> > server, so we can't pass through the authentication on the Share.
> Similarly,
> > we can't map a drive to the file server and have it work, because the
> drive
> > map is associated with the logged-in user, and doesn't carry over to the
> > Local System account.
> >
> > Does anyone have any idea how to make the scheduled package run in a
> domain
> > user's context without reconfiguring the SQLServerAgent to run in a
domain
> > account?
> >
> > Any help is much appreciated.
> >
> > Thanks.
> >
> >
> >
>
>