WinSCP Session.PutFiles with Remove=true gets "Error deleting file System Error. Code: 5" when called from SQLCLR but works in SSIS script task

user_3132672 0
Viewed 951

I am creating an assembly file so that get files and put files can be called as a Stored Procedure from SQL Server. Below is the method for put files. When I pass SqlBoolean DeleteLocalFolder=true, WinSCP errors out with the below error message:

Error: WinSCP.SessionRemoteException: Error deleting file System Error. Code: 5

[SqlProcedure]
public static void PutFiles(SqlString HostName, SqlString UserName, SqlString Password,
                            SqlString SshHostKeyFingerprint, SqlString SshPrivateKeyPath, 
                            SqlString SshPrivateKeyPassphrase, SqlString LocalFolderPath,
                            SqlString RemoteFolderPath,SqlBoolean DeleteLocalFolder,
                            SqlString FileMask, out SqlString strMessage)
{
    // Declare Variables to hold the input parameter values
    string hostName = HostName.Value;
    string userName = UserName.Value;
    string password = Password.Value;
    string sshFingerPrint = SshHostKeyFingerprint.Value;
    string sshPrivateKey = SshPrivateKeyPath.Value;
    string sshPassPhrase = SshPrivateKeyPassphrase.Value;

    bool delateLocalFolder = DeleteLocalFolder.Value;
    //Local Directory 
    DirectoryInfo dir = new DirectoryInfo(LocalFolderPath.Value);
    string folderName = dir.Name;

    //Begin connection to SFTP **Always uses default SFTP port 
    try
    {
        string FtpFolderPath = RemoteFolderPath.Value;

        SessionOptions options = new SessionOptions()
        {
            Protocol = Protocol.Sftp,
            HostName = hostName,
            UserName = userName,
            Password = password,
            SshHostKeyFingerprint = sshFingerPrint,
            SshPrivateKeyPath = sshPrivateKey,
            SshPrivateKeyPassphrase = sshPassPhrase                          
        };

        //Open the Remote session
        using (Session session = new Session())
        {
            session.ExecutablePath = ExecutablePath+"WinSCP.exe";
            session.SessionLogPath = ExecutablePath+"WinscpLog.log";
            session.DisableVersionCheck = true;
            session.Open(options);

            session.ExecutableProcessUserName = "username to log into the sql server instance";

            SecureString _Password = new SecureString();
            foreach (char _PasswordChar in "password to log in sql instance")
            {
                _Password.AppendChar(_PasswordChar);
            }
            session.ExecutableProcessPassword = _Password;
            // Upload files
            TransferOptions transferOptions = new TransferOptions();
            transferOptions.TransferMode = TransferMode.Binary;
            transferOptions.FileMask = FileMask.Value;

            TransferOperationResult transferoperationalResult;

            transferoperationalResult = session.PutFiles(LocalFolderPath.Value, FtpFolderPath, false, transferOptions);
            transferoperationalResult.Check();

            if (transferoperationalResult.IsSuccess)
            {
                if (dir.Exists == true)
                {
                    string sourcePath = FtpFolderPath + folderName + "//";
                    session.MoveFile(sourcePath + "*.*", FtpFolderPath); 
                    session.RemoveFiles(sourcePath);
                }
            }
        }

        strMessage = "Upload of files successful";
    }
    catch (Exception e)
    {
        //Console.WriteLine("Error: {0}", e);
        strMessage = "Error: "+ e;
    }
}

I have written custom code in SSIS script task and have called the Session.PutFiles method by passing the argument bool remove = true. It works with out any problem. Can anyone please explain why it errors only in the custom assembly?

sqlclr winscp-net winscp sql-server c#
1 Answers

By default, processes that reach outside of SQL Server use the security context of the service account of the MSSQLSERVER (or MSSQL.InstanceName) service. When using this default setup, this service account would need to have write / modify access to the folder that the file is in (and possibly to the file itself if the file permissions are using inheritance).

Alternatively, you can use impersonation to change the security context of the external process to that of the Login (within SQL Server) executing the stored procedure, but only if the Login is a Windows Login. SQL Server logins do not have an SID known to the OS, so their security context would use the default, which again is that of the service account for the main SQL Server Database Engine process.

using System.Security.Principal;

public class stuff
{
   [SqlProcedure]
   public static void PutFiles()
   {

      using (WindowsImpersonationContext __ImpersonationIdentity =
                   SqlContext.WindowsIdentity.Impersonate())
      {
         ... {do stuff} ...

         __ImpersonationIdentity.Undo();
      }
   }
}

Also, Martin Prikryl commented on this answer to mention that:

WinSCP .NET assembly supports impersonation on its own. See Session.ExecutableProcessUserName and Session.ExecutableProcessPassword

The idea being ( I assume, though I have not tested):

using System.Security;

  ...

   using (Session session = new Session())
   {
     session.ExecutableProcessUserName = "some_user_name";

     SecureString _Password = new SecureString();
     foreach (char _PasswordChar in "some_password")
     {
        _Password.AppendChar(_PasswordChar);
     }
     session.ExecutableProcessPassword = _Password;
     ... other session stuff...
     session.Open(options);

       ...more stuff...

     _Password.Dispose();
   } /* using() */

In the example above, the username and password are string literals, but in practice those values can come from input parameters to the stored procedure or even from the sqlservr.exe.Config file. Or since the Assembly has a PERMISSION_SET of UNSAFE in order to reference the COM object, you can really get the values from anywhere, including the registry, etc.

So perhaps that is something to try. I am not sure of the timing of when that takes effect, so try one option and if it doesn't work, then try the other.

Please note that, at the very least, the difference between the options is:

  1. Using impersonation via SQLCLR as I provided code for above only allows for the calling Login to be impersonated (and only if that Login is a Windows Login), not any random Login. But, this doesn't require a password either.
  2. Using WinSCP for the impersonation allows for flexibility in what Login / account is being impersonated, BUT it requires passing in a password.
  3. The WinSCP impersonation will allow impersonation to be used by a SQL Server Login, so that is an interesting option there
user_5777650
Related