SQL 如何授予数据库所有者 (DBO) EXTERNAL ACCESS ASSEMBLY 权限？

How to grant the database owner (DBO) the EXTERNAL ACCESS ASSEMBLY permission?

提问by Dev

When I try to create assembly in SQL 2008 from .Net assembly (.Net 3.5) I am getting the below error, error says that I have to set either of the below properties as true, how can I do that?

当我尝试从 .Net 程序集 (.Net 3.5) 在 SQL 2008 中创建程序集时，出现以下错误，错误提示我必须将以下任一属性设置为 true，我该怎么做？

The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission as TRUE

The database has the TRUSTWORTHY database property on

The assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

数据库所有者 (DBO) 的 EXTERNAL ACCESS ASSEMBLY 权限为 TRUE

该数据库具有 TRUSTWORTHY 数据库属性

该程序集使用证书或非对称密钥进行签名，该密钥具有具有 EXTERNAL ACCESS ASSEMBLY 权限的相应登录名。

The complete error is below,

完整的错误如下，

CREATE ASSEMBLY for assembly 'SQLLogger' failed because assembly 'SQLLogger' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

为程序集“SQLLogger”创建程序集失败，因为程序集“SQLLogger”未被授权用于 PERMISSION_SET = EXTERNAL_ACCESS。当以下任一情况为真时，程序集被授权：数据库所有者 (DBO) 具有 EXTERNAL ACCESS ASSEMBLY 权限并且数据库具有 TRUSTWORTHY 数据库属性；或者程序集使用证书或非对称密钥签名，该密钥具有具有 EXTERNAL ACCESS ASSEMBLY 权限的相应登录名。

Thanks in advance!

提前致谢！

采纳答案by bleepzter

You must set these settings in the project file! When you right click on your project, click the Database Settings from the project configuration and select the miscellaneous tab. You should see something similar to what I have here:

您必须在项目文件中设置这些设置！当您右键单击您的项目时，从项目配置中单击数据库设置并选择杂项选项卡。您应该会看到类似于我在这里看到的内容：

This is the same question as: Error Running CLR Stored Proc

这与以下问题相同：Error Running CLR Stored Proc

回答by hoggar

This worked for me:

这对我有用：

EXEC sp_changedbowner 'sa'
ALTER DATABASE [dbase] SET trustworthy ON

and I also did this:

我也这样做了：

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

回答by Solomon Rutzky

⚠ ‼ Please do notset TRUSTWORTHY ON... ⚠

⚠！请不要不设置TRUSTWORTHY ON...⚠

...unless absolutely necessary‼ And, it should pretty much never be "necessary", even when loading an Assembly that you did not build (you can always add another certificate, or worst-case: sign afterloading into SQL Server), or when loading .NET Framework libraries that aren't "supported" and hence aren't already in SQL Server's CLR host (you can use the certificate they are signed with, or worst-case: sign afterloading into SQL Server). Setting the database to TRUSTWORTHY ONopens up a security hole, and for more info on that, please see:

...除非绝对必要‼而且，它几乎永远不会是“必要的”，即使在加载您没有构建的程序集时（您总是可以添加另一个证书，或者最坏的情况：加载到 SQL Server后签名），或者在加载不受“支持”并因此不在 SQL Server 的 CLR 主机中的 .NET Framework 库时（您可以使用它们签名的证书，或者最坏的情况：加载到 SQL Server后签名）。将数据库设置为TRUSTWORTHY ON打开一个安全漏洞，有关更多信息，请参阅：

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining

请，请，请停止使用假冒、可信和跨数据库所有权链接

Instead,

反而，

it is muchbetter to do the following:

这是很多更好地做到以下几点：

USE [master];

CREATE ASYMMETRIC KEY [SomeKey]
  AUTHORIZATION [dbo]
  FROM EXECUTABLE FILE = 'C:\path\to\Some.dll';

CREATE LOGIN [SomeLogin]
  FROM ASYMMETRIC KEY [SomeKey];

GRANT EXTERNAL ACCESS ASSEMBLY TO [SomeLogin]; -- or "UNSAFE" instead of "EXTERNAL ACCESS"

The above only needs to be done once per Instance, per key. So if you use the same snk/ pfxfile for all of your assemblies, then the steps shown above only need to be done once per SQL Server Instance; the number of Assemblies and databases containing those Assemblies does not matter. Or, if signing with a Certificate, then just replace ASYMMETRIC KEYwith CERTIFICATEin the example code shown above.

以上只需要每个实例，每个键执行一次。因此，如果您对所有程序集使用相同的snk/pfx文件，那么每个 SQL Server 实例只需执行上述步骤一次；程序集和包含这些程序集的数据库的数量无关紧要。或者，如果使用证书签名，则只需替换上面显示的示例代码中的ASYMMETRIC KEYwith CERTIFICATE。

This approach allows you to keep better security on the database (by keeping TRUSTWORTHYset to OFF) and allows for more granular control of which assemblies are even allowed to be set to EXTERNAL_ACCESSand/or UNSAFE(since you can separate by using different keys for signing and Logins based on those different keys).

这种方法允许您在数据库上保持更好的安全性（通过保持TRUSTWORTHY设置为OFF），并允许更精细地控制甚至允许将哪些程序集设置为EXTERNAL_ACCESS和/或UNSAFE（因为您可以使用不同的密钥进行签名和基于登录在那些不同的键上）。

However, if you mustuse the TRUSTWORTHY ONmethod, then the database owner does not need to be sa. The requirement is merely that the Login registered as the database owner has been granted either EXTERNAL ACCESS ASSEMBLYor UNSAFE ASSEMBLY(same two permissions shown above for the Asymmetric Key-based Login). Meaning:

但是，如果必须使用该TRUSTWORTHY ON方法，则数据库所有者不必是sa。要求仅仅是注册为数据库所有者的登录名已被授予EXTERNAL ACCESS ASSEMBLY或UNSAFE ASSEMBLY（与上述基于非对称密钥的登录名相同的两个权限）。意义：

USE [master];
GRANT UNSAFE ASSEMBLY TO [{Login-that-is-dbo-for-DB-containing-Assembly}];

For a more detailed walk-through of the security options, please see the following article that I wrote on SQL Server Central: Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies).

有关安全选项的更详细演练，请参阅我在 SQL Server Central 上撰写的以下文章：通往 SQLCLR 级别 4：安全性（外部和不安全程序集）的阶梯。

---

---

For a detailed walk-through of how to automate this via Visual Studio / SSDT, please see the following 3 articles (a 3-part series), also on SQL Server Central:

有关如何通过 Visual Studio / SSDT 自动执行此操作的详细演练，请参阅以下 3 篇文章（由 3 部分组成的系列），同样位于 SQL Server Central：

• Stairway to SQLCLR Level 6: Development Tools Intro
• Stairway to SQLCLR Level 7: Development and Security
• Stairway to SQLCLR Level 8: Using Visual Studio to work around SSDT

• SQLCLR 级别 6 的阶梯：开发工具介绍
• 通往 SQLCLR 级别 7 的阶梯：开发和安全
• 通往 SQLCLR 级别 8 的阶梯：使用 Visual Studio 解决 SSDT

Also, since writing those 3 articles, I have come up with an easier method using T4 templates but have not had time to write that up yet. When I do, I will update this answer with a link to that article.

另外，自从写了那 3 篇文章后，我想出了一个使用 T4 模板的更简单的方法，但还没有时间写出来。当我这样做时，我将使用指向该文章的链接更新此答案。

UPDATE

更新

SQL Server 2017 introduced a new complication in the form of a server-level configuration option named "CLR strict security". It is enabled by default and requires that ALL Assemblies, even those marked as SAFE, be signed with a Certificate or Asymmetric Key, have the associated Login, andthat the Login has the UNSAFE ASSEMBLYpermission granted (not good enough to grant EXTERNAL ACCESS ASSEMBLY). Please see my answer to the following S.O. question for more details on this new "feature":

SQL Server 2017 以名为“CLR 严格安全性”的服务器级配置选项的形式引入了新的复杂功能。它默认启用并要求所有程序集，即使是标记为 的程序集SAFE，都使用证书或非对称密钥签名，具有关联的登录名，并且登录名已被UNSAFE ASSEMBLY授予权限（不足以授予EXTERNAL ACCESS ASSEMBLY）。有关此新“功能”的更多详细信息，请参阅我对以下 SO 问题的回答：

CLR Strict Security on SQL Server 2017

SQL Server 2017 上的 CLR 严格安全性

回答by Stefan Michev

Following code worked for me for integrated security:

以下代码适用于我的集成安全性：

ALTER DATABASE dtabasename SET TRUSTWORTHY ON;
GO

ALTER AUTHORIZATION ON DATABASE::dtabasename TO [DOMAIN\UserName]
GO

回答by Contango

This works for:

这适用于：

• Visual Studio 2015 Update 2.
• Visual Studio 2017.
• Visual Studio 2017 and SQL Server 2019 (thanks @Ramkumar Sambandam).

• Visual Studio 2015 更新 2。
• 视觉工作室 2017。
• Visual Studio 2017 和 SQL Server 2019（感谢 @Ramkumar Sambandam）。

In your project settings, select "External Access":

在您的项目设置中，选择“外部访问”：

On publish, the error message says that it cannot accept "EXTERNAL_ACCESS" unless the assembly is set to "Trustworthy".

在发布时，错误消息说它不能接受“EXTERNAL_ACCESS”，除非程序集设置为“可信”。

So, in the project settings, set the assembly to "Trustworthy":

因此，在项目设置中，将程序集设置为“可信”：

This meant that I was able to run a sample user defined function that listed files on the local hard drive.

这意味着我能够运行一个示例用户定义函数来列出本地硬盘驱动器上的文件。

If the security is still too restrictive, add the attribute DataAccess = DataAccessKind.Readto your UDF, e.g.:

如果安全性仍然过于严格，请将属性添加DataAccess = DataAccessKind.Read到您的 UDF，例如：

[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FindFiles", DataAccess = DataAccessKind.Read, TableDefinition = "FileName nvarchar(500), FileSize bigint, CreationTime datetime")]

Update 2017-07-02

更新 2017-07-02

On SQL Server 2016+ Visual Studio 2015, you might also have to do the following:

在SQL Server 2016+ 上Visual Studio 2015，您可能还必须执行以下操作：

• use master;grant unsafe assembly to [Domain\Username];
• Run any programs (such as Visual Studio or any C# utilities) in Administratormode to give them sufficient permissions to publish UNSAFEassemblies.

• use master;grant unsafe assembly to [Domain\Username];
• 在Administrator模式下运行任何程序（例如 Visual Studio 或任何 C# 实用程序），以授予它们足够的权限来发布UNSAFE程序集。

If nothing works, try connecting using username saand your administrator password. This will always work, regardless of whether Visual Studio is run in Administratormode or not.

如果没有任何效果，请尝试使用用户名sa和管理员密码进行连接。这将始终有效，无论 Visual Studio 是否在Administrator模式下运行。

Update 2020-01-17

更新 2020-01-17

Updated list of compatible VS + SQL Server combinations.

更新了兼容 VS + SQL Server 组合的列表。

回答by Michael Nielo

This is how I managed to make it work:

这就是我设法让它工作的方式：

ALTER DATABASE databasename SET trustworthy ON

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

/
DROP ASSEMBLY assemblyname

GO


CREATE ASSEMBLY assemblyname
FROM 0x4D5A9000.....
WITH PERMISSION_SET = EXTERNAL_ACCESS

回答by Iman

this single line solves the problem for me

这条线为我解决了问题

use master;
grant external access assembly to [domain\username]