MS SQL Server integration service (SSIS) is very widely used in data acquisition that subsequently goes through the ETL (Extract, Transform, and Load) process which makes the data available for immediate use or can be used by other sources as input. Many organizations get data from outside sources in common formats such as CSV or MS EXCEL and those files are dropped into a folder at regular intervals. Since it is humanely not possible to monitor the folder 24×7, it would be ideal to have a monitoring application that kicks into picture as soon as files are dropped into the folder and parses it, and logs any failures occurred during the process.
SSIS comes to the rescue to meet the above requirement and provides Event Watcher Task component which can be configured to monitor a folder at pre-defined intervals. Most of the information available on Internet is about how this component can be used in various ways, this article is not going to repeat the same but will highlight the security issues that arise after the SSIS package is deployed on the target system and provides information on how to fix them.
STEPS TO REPRODUCE THE ISSUE
Download the sample SSIS solution which has a single WMI Event Watcher task that is configured to monitor the folder C:\ WMIFolder (this has to be changed to the correct folder path on your system), at 10 seconds interval.
This solution is built against MS SQL Server 2008 R2, and if you’re using lower MS SQL Server version, please follow the below steps to recreate the project.
Create a new Integration Services Project and drag and drop a WMI Event Watcher Task to the Control Flow canvas.
Set the below as the WqlQuerySource (remember to replace the folder path highlighted in red color with a valid folder path on your system):
SELECT * FROM __instancecreationevent WITHIN 10 WHERE TargetInstance ISA 'Cim_DirectoryContainsFile' AND TargetInstance.GroupComponent='Win32_Directory.Name="c:\\\\WMIFolder"'
Build the SSIS package and copy the Deployment folder from the”bin” directory to the target system.
Double click on the file having its extension as”SSISDeploymentManifest”, which is similar to a setup executable and contains Integration Services Deployment manifest information.
Follow the onscreen instructions/screens to complete the deployment.
Create an SQL job to invoke this SSIS package and execute the job manually. Start Job window will be displayed, and after a while it will report that job execution failed and asks us to check the job history log for details.
Checking the cryptic log reveals that”Access is denied”, but doesn’t give any other clue.
Executed as user: Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.Started: 2:01:00 PMΓ Error: 2012-09-03 14:01:00.38 Code: 0xC0029261 Source: Watch for Incoming Excel File WMI Event Watcher Task Description: Watching for the Wql query caused the following system exception: “Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))”. Check the query for errors or WMI connection for access rights/permissions. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:01:00 PM Finished: 2:01:00 PM Elapsed: 0.265 seconds The package execution failed. The step failed.
Above error occurs where the user account for the SQL Agent service has limited privileges. Using an account that has elevated privileges should solve the issue, but which may not be Okay with the company security policy. If you can use an account with sufficient privileges, then you can stop reading, otherwise keep going.
Below given MSDN link, has good information on provisioning security for DCOM and WMI at the Access Denied section.
http://msdn.microsoft.com/en-us/library/aa394603(VS.85).aspx
When the information provided in the above link has enough to solve the issue, what is the need for this article?
1) Configuring DCOM for the un-initiated could be very daunting, particularly using text based instructions.
2) Even when all the steps are followed, still the Access Denied error could exist in which case additional settings have to be tried.
Having laid the foundation for this article, let’s proceed with the DCOM/WMI configuration.
SETTING DCOM SECURITY
1) Click Start -> Run -> Dcomcnfg or Start -> Control Panel -> Administrative Services ->
2) Expand the Component Services node and right click on My Computer and chose Properties.
3) Click COM Security tab in the My Computer properties and click”Edit Limits” under the Launch and Activation Permissions area.
4) Check whether the user name or group that is used to invoke the WMI task exist in the”Group or user names:” list. If not, click on Add button.
In the Select Users, Computers, or Groups dialog box, add a user name or the group in the Enter the object names to select box, and then click OK. If this package is being executed under SQL Server Agent then the user name must correspond to the account used by SQL Server Agent service. To know the user name,”Start -> Run -> Services.msc” and navigate to SQL Server Agent. Right click and choose Properties.
Go to”Log On” tab.
Click”OK” to close this window and to return back to the DCOM configuration window.
5) In the Launch and Activation Permission dialog, select the user/group added in the above step and give all permissions.
6) Repeat the same with the”Access Permissions“, but here give the user/group Remote Access permission. This is in contrast with what MSDN says:
Instead of the”Anonymous Login”, we’re giving the permission to a specific user/group.
7) Go back to the SQL Agent job and execute it, which will still fail. We’ve left one step in the MSDN article under section Allowing Users Access to a Specific WMI Namespace, which is to grant permission to the user/group on the WMI namespace. We’ll complete this step as well.
Right click on My Computer in Windows Explorer and choose Manage.
Expand Serviced and Applications node and right click on the WMI Control node and select Properties.
Select Security tab in the WMI Control Properties window. Expand the Root node and select CIMV2 node. Click Security button.
If the above added user/group doesn’t exist in the Security section, then click on Add and complete the additional steps. Give the user/group Remote Enable permission.
Click OK twice to close the WMI configuration windows.
Above modifications will only be effective after the WMI service is restarted. Open a command Prompt and issue the below statements:
Net stop winmgmt (if you don’t have enough privileges, then you may get Access is Denied message, in which case open the command prompt with elevated previleges).
Net start winmgmt
8) Again go back to SQL job and execute it. If it concludes successfully congratulations, otherwise keep on reading. Here’s where we’ve to play with some DCOM settings and see whether the SQL job executes.
Go back to DCOM configuration screen, right click on My Computer and choose Properties. Select COM Security tab in the pop up window. Click Edit Defaults in the Access Permissions area.
If the user/group doesn’t exist under the Default Security section then add them and give them Remote Access permission.
Repeat the same with the Edit Defaults under the Launch and Activation Permissions and assign Remote Launch and Remote Activation permissions.
9) Verify whether the SQL job executes successfully this time. If it fails again go back to DCOM configuration and assign Local Activation Permission also.
10) This time the SQL Job should execute without any errors. If the SSIS package is configured to monitor a folder for files, then the job may fail because the user/group doesn’t have permission on the folder being watched. In such case, please follow the below steps to assign the necessary rights.
Navigate to the folder and open its security window. Add the user/group if they are not already in the list and assign appropriate permissions. You may have to give Full Control if the SSIS package creates/deletes files.
Leave a Reply