从SQL Server 2008调用非托管C / C ++ DLL函数 [英] Calling unmanaged C/C++ DLL functions from SQL Server 2008
问题描述
我已经编写了一个C＃适配器类，它从Win32 DLL加载这些函数，使用
DllImport 并将其公开给.Net代码。这在大多数.Net应用程序中工作得很好。现在，我试图使用与SQL Server CLR相同的技术。我创建一组CLR函数和存储过程，调用适配器类。这不适用于在
System.BadImageFormatException 中加载非托管DLL结果的尝试。我可以使用扩展存储过程来执行此操作，但该方法是不推荐使用，可能会在任何新版本的SQL Server中停止。
从CLR存储过程调用非托管函数的正确方式是什么？我想这应该是进程外的。
我试图让我的存储过程调用一个Web公开这些功能的服务。这听起来好像是一个好主意，但到目前为止，我遇到了一个部署SQLCLR程序集的问题，这使得Web服务调用。我无法加载 System.ServiceModel.dll 程序集 version = 3.0.0.0 ，它依赖于 System.Web.dll 程序集版本 2.0.0.0 。
加载 System.Web 程序集给我以下错误：
Assembly'System.Web'汇编'system.web，version = 2.0.0.0，culture = neutral，publickeytoken = b03f5f7f11d50a3a。'，它不存在于当前数据库中。 SQL Server尝试定位并自动从引用程序集来自相同位置加载引用的程序集，但该操作失败（原因：版本，文化或公钥不匹配）。请将引用的程序集加载到当前数据库并重试您的请求。
我已经找到解决部署 System.Web 程序集。而不是从 C：\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll 部署它，它应该从 C：\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll 。然后所有其他所需的程序集也被部署。
按部署顺序的程序集列表：
- C：\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll
- C：\Windows \Microsoft.NET\Framework64\v2.0.50727\System.Web.dll
- C：\Windows\Microsoft.NET\Framework\v2.0.50727\\ \\ System.Messaging.dll
- C：\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll
- C：\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll
- C：\\ \\ Windows \Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll
在这里进行讨论： MSDN - SQL CLR中的非托管代码 。我怀疑这是由引擎的DLL加载引起的。它们提供了一系列选项，包括在另一个服务中的sql server外部托管代码，并使用WCF或COM访问代码。最终的选择是将您的代码重新编译为纯托管C ++，但这可能不是旧代码的选项。
了解SQL Server 2005中的CLR集成详细介绍了该过程的工作原理。
要进一步限制允许存在的代码并在SQL Server中执行
，每个程序集必须使用一组
权限。可以使用三个预定义的集合; SAFE，
EXTERNAL_ACCESS和UNSAFE
...
您还应该查看 CLR集成安全性，并确定您正在执行的代码所需的信任级别，以及是否将能够使用CLR进程内的代码。
I have a vast library of C/C++ functions, which needs to be called from SQL Server 2008.
I have written a C# adapter class which loads these functions from Win32 DLL with DllImport and exposes them to .Net code. This works perfectly fine in most .Net applications.
Now, I was trying to use the same technique with SQL Server CLR. I create a set of CLR functions and stored procedures, which call the adapter class. This does not work as an attempts to load unmanaged DLL results in System.BadImageFormatException.
I can do this with extended stored procedures, but that method is deprecated and may be discontinued in any new release of SQL Server.
What would be the proper way of calling unmanaged functions from CLR stored procedure? My guess that this should be done out-of-process.
I am trying to make my stored proc call a Web service which exposes these functions. This sounds like a good idea, but so far I am having a problem deploying the SQLCLR assembly which makes Web service call. I cannot load System.ServiceModel.dll assembly version=3.0.0.0, which has dependency on System.Web.dll assembly version 2.0.0.0.
Loading System.Web assembly gives me the following error:
Assembly 'System.Web' references assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: version, culture or public key mismatch). Please load the referenced assembly into the current database and retry your request.
I have found the solution for the problem of deploying System.Web assembly. Instead of deploying it from C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll, it should be deployed from C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll. Then all other required assemblies get deployed too.
The list of assemblies in the order of deployment:
- C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll
- C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll
- C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll
- C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll
- C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll
- C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll
Interesing discussion here : MSDN - Unmanaged code in SQL CLR. I suspect it's due to how the DLL's are loaded by the engine. They present a series of options including hosting the code outside the sql server in another service and accessing the code using WCF or perhaps COM. The Final option is to perhaps recompile your code to pure managed C++ but this may not be an option for legacy code.
Understanding CLR Integration in SQL Server 2005 presents more information on how the process works.
To further restrict the code that is allowed to exist and execute inside SQL Server each assembly must be registered with a set of permissions. Three pre-defined sets are available to use; SAFE, EXTERNAL_ACCESS and UNSAFE ...
You should also review CLR Integration Security, and detemrine the trust levels need for the code you're executing and whether you will be able to access use the code within the CLR process anyway.
这篇关于从SQL Server 2008调用非托管C / C ++ DLL函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
