CODEWITHSUNDEEP
c#.netsql-serverimpersonationsqlclr
Davin Studer
Davin Studer

Reputation: 1611

SQLCLR function to get file info with impersonation

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.

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;
        }
    }
}

This is the error I get.

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)

Upvotes: 1

Views: 1750

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48864

By default, Impersonation only allows the credentials to work on the local system. In order to apply those credentials outside of the local system, you need to enable your account for Delegation. The following definitions are taken from: http://msdn.microsoft.com/en-us/library/system.security.principal.tokenimpersonationlevel.aspx

Impersonation: The server process can impersonate the client's security context on its local system. The server cannot impersonate the client on remote systems.

Delegation: The server process can impersonate the client's security context on remote systems.

This setting is configurable in Active Directory and also requires SPNs to be set up first. The steps required to get this working are detailed in the following article (free registration is required, I believe):

Removing the Linked Server 2 hop Limitation

Upvotes: 1

Related Questions