Re: Scheduled DTS package fails even if SQLSERVERAGENT logs on as owner

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks anyway, I found this under SQL Agent properties / Job System ...

Turns out that with the script to map the drive, I was able to reset all
security settings to default.


"Mark Pea***" <fbmarkp@xxxxxxxxxxxxxxxx> wrote in message
news:ekbseDQnFHA.2904@xxxxxxxxxxxxxxxxxxxxxxx
> Thanks for the suggestions.
> The final bit turned out to be a hardcoded ref to a mapped drive (in the
> .mdw file) after all.
> Mapping the drive at the start of the package in an ActiveX task took care
> of it:
>
> Dim wsh
> Set wsh = CreateObject("WScript.Network")
> wsh.MapNetworkDrive "Z:", "\\server\share"
> If Not wsh Is Nothing Then Set wsh = Nothing
>
> For production, though, SQLSERVERAGENT can't be a sysadmin. Can one set
> the proxy to logon as an ordinary domain user?
>
>
> "Allan Mitchell" <allan@xxxxxxxxxxxxxxxxxx> wrote in message
> news:eIieAQFnFHA.2920@xxxxxxxxxxxxxxxxxxxxxxx
>> What is the error?
>>
>> --
>>
>>
>>
>> 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
>>
>>
>> "Mark Pea***" <fbmarkp@xxxxxxxxxxxxxxxx> wrote in message
>> news:OH0N%23IFnFHA.2484@xxxxxxxxxxxxxxxxxxxxxxx
>>>I hadn't checked this before you asked about it, but turns out that
>>>domain\user1 (who is a local admin on the server) does happen to be
>>>included in sysadmin role.
>>>
>>> "Allan Mitchell" <allan@xxxxxxxxxxxxxxxxxx> wrote in message
>>> news:eCKd7REnFHA.320@xxxxxxxxxxxxxxxxxxxxxxx
>>>> And in what DB role on SQL Server is domain\user1?
>>>>
>>>> Remember if they are not in the sysadmin role the job will execute
>>>> under the proxy account
>>>>
>>>> http://support.microsoft.com/default.aspx?scid=kb;en-us;269074&sd=tech
>>>>
>>>> --
>>>>
>>>>
>>>>
>>>> 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
>>>>
>>>>
>>>> "Mark Pea***" <fbmarkp@xxxxxxxxxxxxxxxx> wrote in message
>>>> news:DB4B13F8-39A6-4A68-9358-6387C9F9D2D7@xxxxxxxxxxxxxxxx
>>>>>
>>>>> Successful DTS package fails when scheduled as a SQL Agent job.
>>>>>
>>>>> The package runs only one ActiveX script, which makes a call to a COM
>>>>> DLL
>>>>> that uses DAO to run read-only queries against an Access 2000 .mdb
>>>>> file with
>>>>> workgroup security.
>>>>>
>>>>> Domain\user1 is assigned to the workgroup, and has full NTFS file and
>>>>> folder
>>>>> permissions.
>>>>>
>>>>> The DTS package owner is domain\user1;
>>>>> the Agent job owner is domain\user1;
>>>>> the SQLSERVERAGENT service logs on as domain\user1.
>>>>>
>>>>> SQL Agent security context can write files to the same folder as the
>>>>> .mdb
>>>>> file.
>>>>>
>>>>> All paths involved are confirmed UNCs.
>>>>>
>>>>> Have I missed something? With these security settings, what's the
>>>>> difference
>>>>> between Agent and DTS security context as far as Access is concerned?
>>>>>
>>>>> Thank you.
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Quantcast