CLR程序集将不会在64位SQL Server 2005中加载 [英] CLR assembly will not load in 64 bit SQL Server 2005

问题描述

我们使用了一些用户自定义函数的组件，我们的安装的SQL Server 2005（32位）的。我们部署此生产用这样的脚本：

  CREATE ASSEMBLY [Ourfunctions]
授权[DBO]
从0x4D5A9000 ... 000
的PERMISSION_SET =安全
走
CREATE FUNCTION [DBO]。[GLOBAL_FormatString]（@输入[为nvarchar（4000））
返回[为nvarchar（4000）利用EXECUTE AS CALLER
如
外部名称[Ourfunctions]。[UserDefinedFunctions]。[GLOBAL_FormatString]
走
 

我们从来没有经历过这些功能的任何问题。现在，当我们试图升级我们的服务器到x64，我们调用任何函数时得到的错误。示例堆栈跟踪：

  

System.Data.SqlClient.SqlException：在一个   在Microsoft .NET时出错   在尝试加载框架   组装ID 65549.服务器可能   资源耗尽，或   组件可以不被信任同   PERMISSION_SET = EXTERNAL_ACCESS或   不安全的。再次运行该查询，或检查   文档，看如何解决   集信任问题。欲了解更多   有关此错误信息：   System.IO.FileLoadException：无法   加载文件或程序集ourfunctions，   版本= 0.0.0.0，文化=中立，   公钥= null或它的一个   依赖性。给定的程序集名称   或codeBase的是无效的。 （例外   来自HRESULT：0x80131047）   System.IO.FileLoadException：在   System.Reflection.Assembly.nLoad（的AssemblyName   文件名，字符串codeBase类，证据   assemblySecurity，大会   locationHint，StackCrawlMark和放大器;   stackMark，布尔   throwOnFileNotFound，布尔-snip -

在错误中提到的权限集 EXTERNAL_ACCESS 和不安全，而我们使用的水平 SAFE 。

该.dll文件是建立与目标平台设置为任何CPU，我们得到了相同的结果，当我们试图从文件而不是VARBINARY语法加载的dll。我们已经尝试过 http://support.microsoft.com/kb/918040

我们已经尝试了32位计算机上完全相同的程序，一切都只是工作。它必须是x86和x64之间的差异。任何想法？

解决方法：我们终于找到了解决办法。事实证明，我们的组件确实是32位编译之一。在Visual Studio中，我们所使用的目标任何CPU，但在检查底层的.csproj，我发现下面的代码片段：

 ＆LT;的PropertyGroup条件='$（配置）| $（平台）==调试|值为anycpu'＆GT;
    ...等元素......
    ＆LT; PlatformTarget＆GT; 86＆LT; / PlatformTarget＆GT;
  ＆LT; /的PropertyGroup＆GT;
 

因此​​，我们的任何CPU的目标实际上是建立一个x86汇编！ Aaargh。我已经追溯到这一行的颠覆，但它已经在那里先签于2006年也许这是在数据库项目的一些早期的模板中的错误？

无论如何，感谢您的帮助。我将接受拉斯的回答，因为我怀疑很多谁遇到同样的问题，将最受他的回答有所帮助。

解决方案

它没有做的事实，这是64位，您需要更改数据库允许它。试试这个：

  ALTER DATABASE YOURDATABASEHERE
SET TRUSTWORTHY ON;
走
 

如果单独不起作用，你可以尝试以下几种方法以及

 使用YOURDATABASEHERE
走
sp_configure显示高级选项，1;
走
RECONFIGURE;
走
sp_configure的OLE自动化过程'，1;
走
RECONFIGURE;
走
 

We use an assembly with some user defined functions in our installation of SQL Server 2005 (32 bit). We deploy this to production with a script like this:

CREATE ASSEMBLY [Ourfunctions]
AUTHORIZATION [dbo]
FROM 0x4D5A9000...000
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION [dbo].[GLOBAL_FormatString](@input [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Ourfunctions].[UserDefinedFunctions].[GLOBAL_FormatString]
GO

We have never experienced any problems with these functions. Now, when we tried to upgrade one of our servers to x64, we got errors when calling any of the functions. Sample stack trace:

System.Data.SqlClient.SqlException: An error occurred in the Microsoft .NET Framework while trying to load assembly id 65549. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly 'ourfunctions, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047) System.IO.FileLoadException: at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean -snip-

The error mentions the permission sets EXTERNAL_ACCESS AND UNSAFE whereas we use the level SAFE.

The .dll file is build with target platform set to 'Any CPU' and we get the same results when we try to load the dll from file instead of the varbinary syntax. We already tried the suggestions in http://support.microsoft.com/kb/918040

We have tried the exact same procedure on a 32 bit machine and everything just worked. It must be a difference between x86 and x64. Any ideas?

SOLUTION: We finally found the solution. It turns out that our assembly was indeed a 32 bit compiled one. In Visual Studio, we used the target "Any CPU", but on inspecting the underlying .csproj, I found the following snippet:

  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    ...other elements...
    <PlatformTarget>x86</PlatformTarget>
  </PropertyGroup>

So our "Any CPU" target was actually building an x86 assembly! Aaargh. I have traced back this line in subversion, but it was already there at first checkin in 2006. Maybe this was a bug in some early template of the database project?

Anyway, thanks for your help. I will accept Russ's answer, as I suspect that many who experience the same problems will be helped most by his answer.

解决方案

It doesn't have to do with the fact that it is 64 bit, you need to alter the DB to allow it. Try this:

ALTER DATABASE YOURDATABASEHERE
SET TRUSTWORTHY ON;
GO

if that alone doesn't work, you can try these options as well

USE YOURDATABASEHERE
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO 

这篇关于CLR程序集将不会在64位SQL Server 2005中加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！