SFTP from a Sql Server Context gives error message

Question

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

Comments

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

---

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

---

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.

---

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

---

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

---

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.

---

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.

---

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.

Answer 1

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.

Comments

OK, well at least I know for sure now.