0 votes
by (150 points)
edited

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
by (18.0k points)
edited

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

by (18.0k points)
edited

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).

by (150 points)
edited

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.

by (18.0k points)
edited

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

by (150 points)
edited

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

by (150 points)
edited

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.

by (18.0k points)
edited

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.

by (150 points)
edited

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
by (18.0k points)
edited
 
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.
by (150 points)
edited

OK, well at least I know for sure now.

...