Access denied when copying file from File Table unc path to a file server

Question

I have a file table with FULL non-transactional access. It can thus be browsed like any file server as well as inserted directly via T-SQL. I wrote a simple SQL CLR C# to copy files from the file table to a file server. The user doing the copy is the sql service account which has admin rights to sql server (therefore file table) and the file share. There are no real permission errors.

```try
    {
        using (new ImpersonationNamespace.Impersonation(domain, username, password))
        {

            if (!Directory.Exists(sourceDirectory))
            {
                throw (new Exception("Source Directory: '" + sourceDirectory.ToString() + "' does not exist or user: "+ WindowsIdentity.GetCurrent().Name+" does not not have rights."));
            }
            if (!Directory.Exists(targetDirectory))
            {
                throw (new Exception("Target Directory: '" + targetDirectory.ToString() + "' does not exist or user: " + WindowsIdentity.GetCurrent().Name + " does not not have rights."));
            }
            if (!File.Exists(sourcePath))
            {
                throw (new Exception("Source File: '" + sourceFileName.ToString() + "' does not exist or user: " + WindowsIdentity.GetCurrent().Name + " does not not have rights."));
            }
            if (File.Exists(targetPath) && overwrite == false)
            {
                throw (new Exception("Target File: '" + targetFileName.ToString() + "' already exists in targeted directory: '" + targetDirectory.ToString() + "'"));
            }

            File.Copy(sourcePath, targetPath, overwrite);
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
```

Msg 6522, Level 16, State 1, Procedure dbo.FileCopy_CLR, Line 0 [Batch Start Line 30] A .NET Framework error occurred during execution of user-defined routine or aggregate "FileCopy_CLR": System.Exception: Source Directory: '\sqlserver\filestreamname\FilestreamDirectoryName\Filetable\FlowInput\13143\' does not exist or user: domain\sqlserverserviceaccount does not not have rights. System.Exception: at StoredProcedures.FileCopy(String sourceDirectory, String targetDirectory, String sourceFileName, String targetFileName, Boolean overwrite, String domain, String username, String password) .

Answer 1

Not sure if this a good answer but... I renamed the Filetable_Directory to match the filetable name itself. I changed Read_Only bit on all directories. I bounced the sql service though all File Stream settings were correct. I also used path.combine in C# to ensure properly formed strings etc.

Then I tested the procedure again via management studio and it worked, so I thought all that mattered. But it did not. Turns out that the servername case I am working with to test the proc was the problem.

Directory.Exists() and File.Copy() ran fine on my workstation because my test was using \Servername.domain.com\Filetablerootdirectories....

The application calling the same proc was restating a production path with a REPLACE string (to avoid impacting production) and hardcoded the name to \SERVERNAME\FileTablerootdirectories...

So the filetableness of the problem was irrelevant. I didnt know why System.IO library was behaving so with respect to case and fully qualified etc. But if you run into this problem, its not related to Filestream or SQL Server engine intercepting permissions etc. That was all fine all along.

Luckily, I am copying files from the filetable and safely hardcode the servername to something that works. Also, the target directories are similarly fixed, so I can safely use this as-is. But if I wanted a toolbelt of clr procs to do File System utilities within the SQL Server, this would have to be beefed up a bit to be more robust.