Re: dts and access db

From: JFB (jfb_at_newSQL.com)
Date: 11/10/04 

Date: Wed, 10 Nov 2004 10:15:47 -0500

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
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>

Relevant Pages

  • RE: IIS anonymous access
    ... Since your asp page only needs to read the file from the unix folder, I'd suggest you can only grant the read permission to this domain account on ...
    (microsoft.public.inetserver.iis.security)
  • SQL Server permissions problem
    ... folder & when you run as a job the server2 sqlagent ... account should have permission to access the same. ... >I have also a stored procedure, into a SQL Server ...
    (microsoft.public.sqlserver.security)
  • Re: xp_msadenabled
    ... This has been seen before when the account that SQL Server is running under ... didn't have permission to access Active Directory. ...
    (microsoft.public.sqlserver.server)
  • Re: Exec permissions on Stored Procedure
    ... my case the aspnet machine account for a web app) must have insert ... permission to the underlying table as well as execute permission to ... With the same owner as the tables. ... Or use any of the new mechanisms in SQL Server 2005, ...
    (microsoft.public.sqlserver.security)
  • RE: User name password dialogue box
    ... If you site doesn't grant the permission to one account and disable the ... Right-click the 1033 folder to open the folder property dialog. ...
    (microsoft.public.sharepoint.portalserver.development)