Re: dts and access db
From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 11/10/04
- Next message: Kevin C: "DTS vs SP: Is there a performance difference?"
- Previous message: Sheldon W.: "Re: Adding DTS via script"
- In reply to: JFB: "Re: dts and access db"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 10 Nov 2004 09:09:39 -0700
Okay well then it does apply to the article Allen
originally referred you to as it is a permissions issue for
the account that SQL Server is running under. Why do you
want to set permissions for sa? You may be misunderstanding
this on what the accounts are, what sa is - it's a SQL
account so it can't have full control to the folder on the
other server as you indicated. Not totally understanding all
the accounts and what they are, how they work can be
tripping you up a bit here. The job owner being sa just
determines the security context under which the jobs will
run - it doesn't mean that sa is the account that needs
permissions on the other computer. That may be where the
confusion is.
For a workgroup and accessing resources on the other
computers, the best you can try is a kluge with this.
Create local account on the computer where SQL Server is.
Run SQL Server and SQL Agent under that account.
Create the exact same account and password on the other
computer where the access database resides. Give the
appropriate permissions to this account for the folder where
the Access database resides.
-Sue
On Wed, 10 Nov 2004 10:15:47 -0500, "JFB" <jfb@newSQL.com>
wrote:
>Hi Sue,
>You were right when I execute xp_cmdshell 'dir \\server\data\*.*' from
>sqlagent I got a denied access message.
>This is the first time than I'm using a folder out the sqlserver for
>schedule job.
>My servers are in just workgroup, not domain, not active directory.
>How can I setup permission for sa account under \\server\data folder?
>Tks in advance.
>JFB
>
>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>news:8uo1p095qa510b1cfnhs48n30ol7m98s25@4ax.com...
>> If it doesn't fail when run manually but fails when
>> scheduled as a job then something in the article is likely
>> related to your issue. The service account for SQL Server
>> needs to have the correct permissions and needs to be a
>> domain account. You can log on to SQL Server under this
>> account and run the package manually to see if you get any
>> errors.
>> You may also want to log on as under the service account and
>> execute xp_cmdshell to do a dir command on \\server\Data\
>> and see if you can get the directory listing or if you get
>> any errors.
>> You need to also make sure the database is not being opened
>> exclusively by another user or program. However, being that
>> this never happens when the package is run manually then
>> again, it's probably related to permissions or account
>> settings as described in the article.
>>
>> -Sue
>>
>> On Tue, 9 Nov 2004 09:33:17 -0500, "JFB" <jfb@newSQL.com>
>> wrote:
>>
>>>Tks for u reply...
>>>1. The server have full control of the data folder.
>>>2. Nothing in the article affects my problem.
>>>3. The pkg doesn't fail manually, no errors.
>>>4. Here is the error detail when it runs as a job schedule:
>>>Executed as user: SQLSERVER\SYSTEM. ...cuting... DTSRun OnStart:
>>>DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
>>>DTSStep_DTSExecuteSQLTask_1,
>>>Error = -2147467259 (80004005) Error string: The Microsoft Jet
>>>database engine cannot open the file '\\server\Data\Data.mdb'. It is
>>>already opened exclusively by another user, or you need permission to view
>>>its data. Error source: Microsoft JET Database Engine Help
>>>file:
>>>Help context: 5003051 Error Detail Records: Error: -2147467259
>>>(80004005); Provider Error: -534709256 (E020FBF8) Error string: The
>>>Microsoft Jet database engine cannot open the file
>>>'\\server\Data\Data.mdb'.
>>>It is already opened exclusively by another user, or you need permission
>>>to
>>>view its data. Error source: Microsoft JET Database Engine Help
>>>file: Help context: 5003051 DTSRun OnFinish:
>>>DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete.
>>>Process
>>>Exit Code 1. The step failed.
>>>
>>>JFB
>>>
>>>"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
>>>news:d910p053vuphhmhnvdlkajsjee2lavpb2s@4ax.com...
>>>> It depends on what the specific permissions error is. If you
>>>> are positive it's nothing in the article then it could be
>>>> just the nature of some access issues. Does the service
>>>> account for SQL Server have full control on the folder where
>>>> the ldb file is created? Does it always fail? Is the Access
>>>> database opened by another user or users when you get the
>>>> errors?
>>>> Posting the exact error message when you run it manually
>>>> would help. If it's Access specific related to some of the
>>>> above then you should hit similar error messages when run
>>>> manually. It won't matter whether it's scheduled or not.
>>>>
>>>> -Sue
>>>>
>>>> On Mon, 8 Nov 2004 18:06:11 -0500, "JFB" <jfb@newSQL.com>
>>>> wrote:
>>>>
>>>>>I'm using sa account with secure pwd... other dts are in the same way
>>>>>without any problem.
>>>>>I already read this article.
>>>>>Anything else to think about ???
>>>>>Tks
>>>>>JFB
>>>>>
>>>>>"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
>>>>>news:eygQdWcxEHA.412@TK2MSFTNGP14.phx.gbl...
>>>>>> Under what account is the job scheduled?
>>>>>>
>>>>>> Have a look here
>>>>>>
>>>>>> http://support.microsoft.com/?kbid=269074
>>>>>>
>>>>>> --
>>>>>>
>>>>>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>>>>>> www.SQLDTS.com - The site for all your DTS needs.
>>>>>> www.Konesans.com
>>>>>>
>>>>>>
>>>>>> "JFB" <jfb@newSQL.com> wrote in message
>>>>>> news:eiTz0WbxEHA.3844@TK2MSFTNGP12.phx.gbl...
>>>>>>> Hi,
>>>>>>> I want to schedule a package to import access database to sql2000
>>>>>>> server
>>>>>>> (sp3).
>>>>>>> The pkg fail with permissions error... what do I neet to have in mine
>>>>>>> for
>>>>>>> schedule pkg?
>>>>>>> Do I need to install access in my sql2000 server?
>>>>>>> The pkg is running ok manually.
>>>>>>> Tks in advance
>>>>>>> JFB
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>
- Next message: Kevin C: "DTS vs SP: Is there a performance difference?"
- Previous message: Sheldon W.: "Re: Adding DTS via script"
- In reply to: JFB: "Re: dts and access db"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
