SSIS EVENT LOG Package configuration error

Question

SSIS EVENT LOG Package configuration error

Ken Grissom-138180

SSChasing Mays

Points: 602
More actions
September 7, 2007 at 12:41 pm

#176561

Problem Description:
     SSIS packages using package configuration in order to be
     mobile from machine to machine.
     SSIS package runs correctly when running as an ADMIN using
     the package configuration thru the SQL Agent scheduler.
     SSIS configuration is kept in a SQL SERVER table.
     SQL SERVER SERVICE AND SQL AGENT Services running as a domain
     user with proper permissions on local box.
     Want to use least privilages set so Local account set up for
     proxy use.
     Proxy account granted rights to the SSIS subsystem.
     Package connects to AS400 and pull data into SQL SERVER. Package does not use cmd_shell.
     SSIS packages stored on OS file system.
     SSIS job set up in the SQL AGENT JOB scheduler with the owner
     of the job set to the proxy account and the job step runs as
     the proxy account.

     Running the job results in the following errors:

           Date  9/7/2007 8:37:24 AM
           Log  Job History (CMDDBSmallTables)

           Step ID  1
           Server  ASERVER
           Job Name  AJobName
           Step Name  ASTEP
           Duration  00:00:06
           Sql Severity  0
           Sql Message ID  0
           Operator Emailed
           Operator Net sent
           Operator Paged
           Retries Attempted  0

           Message
           Executed as user: aserver\auser. ...00 for 32-bit Copyright
           (C) Microsoft Corp 1984-2005. All rights reserved.
           Started: 8:37:25 AM Error: 2007-09-07 08:37:30.38
           Code: 0xC001403D     Source:       Description: The log
           provider type "DTS.LogProviderEventLog.1" specified for log
           provider "SSIS log provider for Windows Event Log" is not
           recognized as a valid log provider type. This error occurs
           when an attempt is made to create a log provider for unknown
           log provider type. Verify the spelling in the log provider
           type name. End Error Error: 2007-09-07 08:37:30.38
           Code: 0xC0010018     Source:       Description: Error
           loading value "<DTS:LogProvider
           xmlnsTS="roperty'>http://www.microsoft.com/SqlServer/Dts"><DTSroperty
           DTS:Name="ConfigString"></DTSroperty><DTSroperty
           DTS:Name="DelayValidation">0</DTSroperty><DTSroperty
           DTS:Name="ObjectName">
           </DTSrop" from node "DTS:LogProvider". End Error
           Could... The package could not be lo... The step failed.
Assumptions:
     I beleve the package configuration to be complete, since
     the above package runs correctly thru the scheduler using
     an adminstrative ID with the package configuration. The package
     configuration has no mention of the "SSSIS log provider for Windows Event Log".
     Therefore, I believe the text of the error message is not accurate for the
     error that I am actually receiving.

Statement:
     If the above asumptions are not correct, I am not sure how to specify the
     "SSSIS log provider for Windows Event Log" in the Configuration table.

Question:
     Are my assumptions correct?
     Has anyone else see this issue?
     Am I missing that is causing the proxy account not to execute the job correctly?
     Is there any way to work around this error?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Pettifer SSC Eights! Points: 915 More actions · Answer 1

Hi

I had a similar problem recently. It is most likely because although your AD user has rights on the server, it does NOT have rights to write to the system log you're using. I'll assume you're using the Application log in this reply.

The fix is the same whether you're using Win 2K3, Win Vista, Win 7 or Win 2K8, but the method of application differs a little. Basically you need to add some information to the registry, but in Vista, 7 and 2K8 there is a little system utility you can use to make life a little easier. The registry key in question is:

HKLM\System\CurrentControlSet\Services\Eventlog\Application\CustomSD

Which may or may not exist to start with. Let's assume it does NOT exist and we will start with Win 2K3. As with all registry operations, if you are directly editing be sure you know what you are doing before you start and take a backup. If you are not sure then this method is not for you!! Don't despair, there are some alternatives which I will tewll you about after I explain the manual method.

You will need to add the key and make the type REG_SZ (a string). The key contains a chunk of SDDL (Security Definition Descriptor Language) which is impenetrable at the best of times. The default value for the Application log is:

O:BAG:SYD:(D;; 0xf0007;;;AN)(D;; 0xf0007;;;BG)(A;; 0xf0007;;;SY)(A;; 0x5;;;BA)(A;; 0x7;;;SO)(A;; 0x3;;;IU)(A;; 0x2;;;BA)(A;; 0x2;;;LS)(A;; 0x2;;;NS)

So you will need to start with this. To let your SSIS service account write to this log you will need to append the following to the above default:

(A;;0×3;;;{SID})

Where you replace {SID} with the SID of the service account. That will limit write permission to the Application log to that account only. If you cannot obtain the SID of the account (which can be a nuisance) you can change {SID} to AU which grants write access to the Authenticated Users domain group, thus:

(A;;0×3;;;AU)

If the CustomSD key already exists then you only need append the (A;;0×3;;;{SID}) part to the existing key.

OK, so you're not keen on editing the registry yourself? No problem - this can be achieved using the server's Local Group Policy. The manual method described above and the Local Policy method (as well as one other) are described here:

http://support.microsoft.com/kb/323076

Now let's move to later OS platforms where there is a handy tool called wevutil that means you don't have to directly edit the registry. There is an excellent and verbose explanation available here:

http://jpadda.wordpress.com/2010/08/08/event-log-write-permissions/[/url]

I will reproduce it here in case this link ever dies:

Access to Write to Event log
Open Cmd prompt as admin.
Type: C:\>wevtutil gl application > C:\temp\out.txt
This outputs the security credentials for the application event log to the given text file.
Open the generated C:\temp\out.txt file in Notepad
It looks a little bit like this:
name: application
enabled: true
type: Admin
owningPublisher:
isolation: Application
channelAccess: O:BAG:SYD:(A;;0xf0007;;;SY)(A;;0×7;;;BA)(A;;0×7;;;SO)(A;;0×3;;;IU)(A;;0×3;;;SU)(A;;0×3;;;S-1-5-3)(A;;0×3;;;S-1-5-33)(A;;0×1;;;S-1-5-32-573)
logging:
logFileName: %SystemRoot%\System32\Winevt\Logs\application.evtx
retention: false
autoBackup: false
maxSize: 20971520
publishing:
The line you’re interested in is the “channelAccess”. (The wonderful Windows SDDL – Security Descriptor Definition Language).
You need to add (append) the following to the end of the line: (A;;0×3;;;AU)
- This gives write/read access (the “0×3” bit ) to Authenticated Users (AU).
You then need to apply the updated setting…
C:\>wevtutil sl Application /ca:O:BAG:SYD:(A;;0xf0007;;;SY)(A;;0×7;;;BA)(A;;0×7;;;SO)(A;;0×3;;;IU)(A;;0×3;;;SU)(A;;0×3;;;S-1-5-3)(A;;0×3;;;S-1-5-33)(A;;0×1;;;S-1-5-32-573)(A;;0×3;;;AU)
Done.
You can check the change by:
C:\>wevtutil gl application > C:\temp\updatedout.txt
And you’ll see the change in the channelAccess line.
The more eagle eyed amongst you (and you’d need to be eagle eyed to spot this) will notice that the registry location:
Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application now has an additional key CustomSD with the updated channelAccess key. This key only appears if the default configuration is changed, i.e. as I have done by granting Authenticated Users write access.
Important Info: Only applies cos the application user is not the NetworkService, but a domain user. The NetworkService user is already part of the approved list, so you won’t need to apply these steps.
Further Reading:
If you would like to prevent authenticated users from writing to the event log, and would prefer to lock down security further, you can follow the process above, but rather than giving access to all authenticated users, you can grant access to specific users by adding their SID (security ID) to the channel access list instead. VBScript to find SID for a user.

I hope this helps. You may need the help of a domain admin for some of this, depending on how your network is configured and your own rights.

Incidentally, where you see the unhappy smiley, that is supposed to be a colon : followed by an opening parentheses ( but I can't figure out how to stop the damn smiley appearing. You will see it without the smiley in the links I've provided.

Steve Pettifer SSC Eights! Points: 915 More actions · Answer 2

Word of warning:

I've just done this as I typed it out on a Win 2K3 server and it didn't work at first. I checked the reg key very carefully and it appeared that the 0x3 looked different, font-wise, from existing parts of the key. I copied the 0x3 from another section, pasted it in and hey presto it worked. SDDL is not user friendly!!

Steve