tags:

views:

98

answers:

2

Exact duplicate of: http://stackoverflow.com/posts/2035107

Trying file delete and save operation on a remote location. When run as a console App, it works fine but fails when called from XP_CMDSHELL (SQL server) Here is the exception when run from XP_CMDShell

[4804] System.UnauthorizedAccessException: Attempted to perform an unauthorized operation. 

[4804] at System.Security.AccessControl.Win32.GetSecurityInfo(ResourceType resourceType, String name, SafeHandle handle, AccessControlSections accessControlSections, RawSecurityDescriptor& resultSd) [4804] at System.Security.AccessControl.NativeObjectSecurity.CreateInternal(ResourceType resourceType, Boolean isContainer, String name, SafeHandle handle, AccessControlSections includeSections, Boolean createByName, ExceptionFromErrorCode exceptionFromErrorCode, Object exceptionContext) [4804] at System.Security.AccessControl.FileSystemSecurity..ctor(Boolean isContainer, String name, AccessControlSections includeSections, Boolean isDirectory) [4804] at System.Security.AccessControl.DirectorySecurity..ctor(String name, AccessControlSections includeSections) [4804] at System.IO.DirectoryInfo.GetAccessControl(AccessControlSections includeSections) [4804] at Excel.SetAcl(String filename, String account) in D:\SAABZX01D\dev\libraries\EXCEL\Class1.cs:line 228 [4804] at Excel.doKEStats(String baanId, String fromDate, String toDate) in D:\SAABZX01D\dev\libraries\EXCEL\Class1.cs:line 87

Here is the code

 public static  bool SetAcl(string filename,string account)
    {
        FileSystemAccessRule rule = new FileSystemAccessRule(account, FileSystemRights.FullControl, AccessControlType.Allow);
         string path= System.IO.Directory.GetDirectoryRoot(filename);
      System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(filename);


      bool what = false;
      DirectorySecurity security = di.GetAccessControl(AccessControlSections.Access);
      security.ModifyAccessRule(AccessControlModification.Add, rule, out what);




      di.SetAccessControl(security);
      return what;

    }
A: 

Make sure the account SQL Server runs as has the permissions to do that file operation.

ZippyV
A: 

The problem was DirectoryInfo presented with full path (including file name).. Here is the modified code that works..

 public static bool SetAcl(string filename, string account)
{
    FileSystemAccessRule rule = new FileSystemAccessRule(account, FileSystemRights.Write, AccessControlType.Allow);

    PermissionSet fp = new PermissionSet(PermissionState.Unrestricted);
    fp.AddPermission(new FileIOPermission(FileIOPermissionAccess.Read, new string[] { filename }));
    fp.AddPermission(new FileIOPermission(FileIOPermissionAccess.Write | FileIOPermissionAccess.PathDiscovery, new string[] { filename }));
    fp.Assert();

    System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(System.IO.Path.GetDirectoryName(filename));


    bool what = false;
    DirectorySecurity security = di.GetAccessControl();

    security.ModifyAccessRule(AccessControlModification.Add, rule, out what);
    di.SetAccessControl(security);
    return what;

}
TonyP