SFTP from a Sql Server Context gives error message

0 votes
asked Oct 8, 2013 by gregoryagu (150 points)
edited Oct 15, 2013

I need to do SFTP from within a Sql Server Context. I can do FTP with MS libraries, but they do not support SFTP.

So I downloaded Rebex, and put together a sample project, and tried to install in Sql Server as a CLR stored procedure.

When doing this, Sql Server gives the following message:

Msg 6218, Level 16, State 2, Line 3

CREATE ASSEMBLY for assembly 'RebexTest' failed because assembly 'Rebex.Common' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message

[ : Rebex.Security.Certificates.CertificateStore::Exists][mdToken=0x60003b0][offset 0x000000C7] Method is not visible.

Is there a way to fix this so I can install in Sql Server?

Greg

Applies to: Rebex SFTP
commented Oct 9, 2013 by Jan Sotola (17,310 points)
edited Oct 9, 2013

Did you add a "WITH PERMISSION_SET = UNSAFE" option to the CREATE ASSEMBLY command? If you did and the problem is still occurring, please send us a version of your SQL Server and version of Rebex components you are using.

In general, the Rebex SFTP should work well within the SQL-CLR environment, if configured according the following article: http://blog.rebex.net/news/archive/2013/06/10/how-to-upload-data-to-ftp-or-sftp-server-from-a-sql-clr-procedure-sql-server-2012-and-vs-2012.aspx

commented Oct 9, 2013 by Jan Sotola (17,310 points)
edited Oct 9, 2013

Oh, and if you are using a TRIAL version of Rebex components, please write us to support@rebex.net for a trial key. The trial key is not usually needed, unless the trial version is used in some hosted environment (like SQL CLR).

commented Oct 9, 2013 by gregoryagu (150 points)
edited Oct 9, 2013

Thanks for the link. I will try this again and see if I can get this to work. And I emailed support for a trial key.

commented Oct 11, 2013 by Jan Sotola (17,310 points)
edited Oct 11, 2013

Is the CLR procedure already working? If not, could you post some details about the failure?

commented Oct 11, 2013 by gregoryagu (150 points)
edited Oct 11, 2013

Hi Jan, Still testing this, will get back with details shortly.

commented Oct 11, 2013 by gregoryagu (150 points)
edited Oct 11, 2013

Here is the deal: Yes, it will work with Unsafe permission set. The problem is the DBA will not allow Unsafe. If the code is %100 managed code, then it would not need the Unsafe context. But the method: Rebex.Security.Certificates.CertificateStore::Exists seems to call unmanged code. If there was a way to modify that so it does not, then it would work well for us.

commented Oct 11, 2013 by Jan Sotola (17,310 points)
edited Oct 11, 2013

Unfortunately, there are more requirements for the UNSAFE mode apart from 100% managed code (e.g. not using static fields). We would try to create a reduced version of the Sftp component overcoming these limits, but I'm afraid there will be some limit we cannot rid of. I'll write you back next week.

commented Oct 11, 2013 by gregoryagu (150 points)
edited Oct 11, 2013

Thanks, but don't worry about it for our sake. We are going to try to do this outside of Sql Server instead in a process.

1 Answer

0 votes
answered Oct 14, 2013 by Jan Sotola (17,310 points)
edited Oct 15, 2013
 
Best answer

I've done some research and this is our final answer:

Unfortunately, Rebex components cannot be used in SQL-CLR environment with the SAFE or EXTERNAL-ACCESS permission sets. The required permission set is UNSAFE.

There are several conditions preventing usage of the SAFE mode:

  • P/Invoking is used in some situations.
    These could be removed without significant reduction in functionality. For example, iOS and Android versions don't use any P/Invokes at all.
  • Finalizers are used in some classes.
    The only ones really needed are those used to free unmanaged resources that are accessed using the P/Invokes.
  • Static fields are used a lot, for example for synchronization locks.
    It would be very difficult to get rid of all static fields in our components, unfortunately.
commented Oct 15, 2013 by gregoryagu (150 points)
edited Oct 15, 2013

OK, well at least I know for sure now.

...