title: SQLCLR function to get file info with impersonation
body: <p>So, I have a SQLCLR function that I have written to get file system information and it works just fine if I am getting information on the local SQL server, but when I try to get file info on a remote machine via a UNC path (\\server\c$\directory\) I get permissions denied issues. I think this is a double hop permissions issue, but I'm having trouble figuring out how to get around this. Here is a simplified version of the code that only returns file name ... to reduce the complexity and lines.</p>
  
  <pre><code>using System;
  using System.Collections;
  using System.Data.SqlClient;
  using System.Data.SqlTypes;
  using System.IO;
  using System.Security.Principal;
  using Microsoft.SqlServer.Server;
  
  namespace CLRFunctions
  {
      public class SQLFileSystem
      {
          [SqlFunction(FillRowMethodName = "FillRow", TableDefinition = "FileName nvarchar(500)", DataAccess = DataAccessKind.Read)]
          public static IEnumerable fnGetFiles(SqlString Path, SqlString FilePattern, SqlBoolean Recursive)
          {
              String[] files = null;
              WindowsIdentity clientId = null;
              WindowsImpersonationContext impersonatedUser = null;
  
              clientId = SqlContext.WindowsIdentity;
  
              try
              {
                  try
                  {
                      impersonatedUser = clientId.Impersonate();
                      if (impersonatedUser != null)
                      {
                          files = Recursive ? Directory.GetFiles(Path.ToString(), FilePattern.ToString(), SearchOption.AllDirectories) : Directory.GetFiles(Path.ToString(), FilePattern.ToString(), SearchOption.TopDirectoryOnly);
                      }
                  }
                  catch (Exception ex)
                  {
                      files = null;
                  }
                  finally
                  {
                      if (impersonatedUser != null)
                      {
                          impersonatedUser.Undo();
                      }
                  }
              }
              catch
              {
                  throw;
              }
  
              return files;
          }
  
          public static void FillRow(Object obj, out SqlString FileName)
          {
              String file = (String)obj;
              FileInfo fi = new FileInfo(file);
              FileName = fi.Name;
          }
      }
  }
  </code></pre>
  
  <p>This is the error I get.</p>
  
  <pre><code>Msg 6522, Level 16, State 1, Line 1
  A .NET Framework error occurred during execution of user-defined routine or aggregate "fnGetFiles": 
  System.UnauthorizedAccessException: Access to the path '\\server\c$\temp' is denied.
  System.UnauthorizedAccessException: 
      at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
      at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)
      at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)
      at VanClinic.Libraries.SQLFileSystem.SQLFileSystem.fnGetFiles(SqlString Path, SqlString FilePattern, SqlBoolean Recursive)
  </code></pre>